Tutorials

DevExpress Grid to Symfony Controller (Doctrine) Part #2

April 11, 2021
Good Stuff

Welcome back to part #2 of this tutorial. Let’s resume our tutorial. In the last tutorial, we cover pretty much on setting up the yaml stuff from symfony and getting the packages. Now we are going to dive deep into writing the controllers and repository for the entity game_type

Let’s take a good look at our schema here again.

As for my devExpress/devExtreme php Symfony 5 custom bundle

Visit https://github.com/mingyuen78/devextremeBundle

And here is how my controller looks like GameTypeController with the route of /game_type/dg_select
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

<?php
namespace App\Controller;
 
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
 
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\JsonResponse;
 
use App\Repository\GameTypeRepository;
use AppBundle\Util\EnumError;
use AppBundle\Util\FilterConstructHelper;
 
class GameTypeController extends AbstractController
{
    private $gametypeRepository;
    public function __construct(GameTypeRepository $gametypeRepository)
    {
        $this->gametypeRepository = $gametypeRepository;
    }    
 
    /**
     * @Route("/game_type", name="game_type")
     */
    public function index(): Response
    {
        return $this->render('game_type/index.html.twig', [
            'controller_name' => 'GameTypeController',
        ]);
    }
 
    /**
    * @Route("/game_type/dg_select", name="game_type", methods={"POST"})
    */ 
    public function dg_select(Request $request):JsonResponse {
        /* 
        * handle incoming json post
        * sample body raw will be {"filter":null,"sort":null,"defaultSort":[{"selector":"Trx_Id","desc":true}],"skip":0,"take":20} 
        * in my frontend files, I created an extra key called defaultSort to store the default sort of the grid.
        * filter will be an array of selector objects just like defaultSort. FilterConstructHelper creates an 
        * GridArguments Object.
        */ 
        $loadOptions = json_decode($request->getContent());
        // FilterConstructHelper is my own AppBundle 
        $arg = FilterConstructHelper::process($loadOptions);
        $recordSets = $this->gametypeRepository->getDGRecords($arg);
        $total_summary = [0]; //This grid has no summary.
 
        return new JsonResponse([
            'code' => EnumError::ALLGOOD, 
            'result' => $recordSets['results'],
            'totalCount' => $recordSets['total_rows'],
            'summary' => $total_summary
        ], Response::HTTP_OK);
    }
}
Next is my Repository for GameType – GameTypeRepository
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

<?php
 
namespace App\Repository;
 
use App\Entity\GameType;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\ORM\Query;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\ORM\Tools\Pagination\Paginator;
 
/* My own custom bundle copied to src\AppBundle */
use AppBundle\Generic\GridArguments;
use AppBundle\Util\DevExFilterHelper;
 
/**
 * @method GameType|null find($id, $lockMode = null, $lockVersion = null)
 * @method GameType|null findOneBy(array $criteria, array $orderBy = null)
 * @method GameType[]    findAll()
 * @method GameType[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class GameTypeRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, GameType::class);
    }
 
    public function getDGRecords(GridArguments $gridArg): array
    {        
        // Need to tell filter which of these need replacing and which alias table it is from.
        $map = [
            'id' => 'gt.id',
            'game_type_name' => 'gt.game_type_name',
            'game_type_description' => 'gt.game_type_description',
            'created_by' => 'gt.created_by',
            'updated_by' => 'gt.updated_by',
            'created_date' => 'gt.created_date',
            'updated_date' => 'gt.updated_date',
        ];
 
        $qb = $this->createQueryBuilder('gt');
        if ($gridArg->group) {
            $qbCol = DevExFilterHelper::QueryBuilderHandler($map, $gridArg->group);
            $qb->distinct();
            if (strpos($gridArg->group, '_date') !== false) {
                // If its related to date. updated_date and created_date are my date time field.
                // I wanted to show only the date and return into header filter.
                $qb->select('CAST('.$qbCol.' as DATE) as text, CAST('.$qbCol.' as DATE) as key');
            } else {
                $qb->select($qbCol.' as text,'.$qbCol.' as key');
            }
        }
        if ($gridArg->filter) {
            // A static method from my bundle to process filters into where statements */
            $whereRaw = DevExFilterHelper::GetSqlExprByArray($gridArg->filter);
            $where = DevExFilterHelper::QueryBuilderHandler($map, $whereRaw);
            $qb->where($where);
        }
 
        /* cloning query for count */
        $cloneQuery = clone $qb->getQuery();
        $totalRows = $this->getCount($cloneQuery, $gridArg->group);                
 
        if ($gridArg->sort) {
            /* A static method from my bundle to process multiple sort into orderby statements */
            $sortByRaw = DevExFilterHelper::GetSqlSortByArray($gridArg->sort);
            $sortBy = DevExFilterHelper::QueryBuilderHandler($map, $sortByRaw);
            $qb->add('orderBy', $sortBy);
        }
        /* for pagination */
        $qb->setFirstResult( $gridArg->skip );
        $qb->setMaxResults( $gridArg->take );
        $results = $qb->getQuery()->getResult();
 
        $data = [];
        if ($gridArg->group) {
            foreach ($results as $result) {
                // This is the json schema that header filters need to display the data.
                $data[] = [
                    'key' => $result["key"],
                    'text' => $result["text"],
                ];
            } 
        } else {
            foreach ($results as $result) {
                // packing from Entities Objects into associative arrays. 
                // note we can format our dates here to return nicely to controller.
                $data[] = [
                    'id' => $result->getId(),
                    'game_type_name' => $result->getGameTypeName(),
                    'game_type_description' => $result->getGameTypeDescription(),
                    'created_by' => $result->getCreatedBy(),
                    'updated_by' => $result->getUpdatedBy(),
                    'updated_date' => $result->getUpdatedDate()->format('Y-m-d H:i:s'),
                    'created_date' => $result->getCreatedDate()->format('Y-m-d H:i:s'),
                ];
            }
        }
 
        return array(
            "results" => $data,
            "total_rows" => $totalRows
        );
    }
 
    private function getCount($query, $group = null) {
        if ($group) {
            // For individual select only specific or selected few fields. 
            $totalRows = count($query->execute());
        } else {
            // Symfony have their own class to count total count of the query used in pagination 
            // but, is only for standard query
            $paginator = new Paginator($query);
            $totalRows = count($paginator);
        }
 
        return $totalRows;
    }
}

With these in place, and some sample data inserted into the table Game Type we can use Postman to test our webservice. Fire up the server using symfony server:start. Send a post with headers Content-Type : application/json and loadOption parameters in. loadOptions parameters are what DevExpress Grid will typically send when you set to true. 

http://localhost:8000/game_type/dg_select
Below are some of the loadOptions you can paste into your postman under Body > Raw

For loadOptions with Pagination and multiple sorts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

{
    "sort": [
        {
            "selector": "created_date",
            "desc": false
        },
        {
            "selector": "game_type_description",
            "desc": false
        },
        {
            "selector": "updated_by",
            "desc": false
        }
    ],
    "group": null,
    "requireTotalCount": true,
    "searchOperation": "contains",
    "searchValue": null,
    "skip": 0,
    "take": 20,
    "userData": {},
    "defaultSort": [
        {
            "selector": "id",
            "desc": 1
        }
    ]
}

For loadOptions with Pagination and multiple search filters. To test pagination, modify the take to higher number. take (line 19) It represent max results shown per page and skip is the current page which starts with index 0.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

{
    "sort": [
        {
            "selector": "created_date",
            "desc": false
        },
        {
            "selector": "game_type_description",
            "desc": false
        },
        {
            "selector": "updated_by",
            "desc": false
        }
    ],
    "group": null,
    "requireTotalCount": true,
    "searchOperation": "contains",
    "searchValue": null,
    "skip": 0,
    "take": 1,
    "userData": {},
    "filter": [
        [
            "game_type_description",
            "contains",
            "free runner"
        ],
        "and",
        [
            "updated_by",
            "contains",
            "super"
        ]
    ],
    "defaultSort": [
        {
            "selector": "id",
            "desc": 1
        }
    ]
}
Next, we move on to front-end VueJS with devExtreme-vue as dependencies.

Below are the dependencies I used to contruct this sample.

You can clone the git project of my sample devExpress / devExtreme grid here.
https://github.com/mingyuen78/crud_manager
Make sure you have node.js , and vue cli installed. Just either use yarn or npm install on it’s crud_manager folder to install its dependencies.

I hope this tutorial helps in accelerating your efforts to get things done in Symfony 5 and DevExpress. Have fun and happy coding!

Comments

comments