This website requires JavaScript.

    Json Query — Easy SQL building tool

    The idea of ​​converting Json to DQL (Doctrine Query Language) and then executing a database query served as the basis for this project. This conversion allows you to use most of Doctrine's features, including filtering by related entities with ordering and sorting. Of course, it does not eliminate the need to create complex queries. For example, queries with grouping and aggregation are not supported (not implemented intentionally). But it gets rid of the routine work.

    Frontend

    Entity and field names conversion

    To perform automatically conversion Json Query to SQL, ORM Backend uses common pattern for field names: <full-namespace>-<class_name>.<fieldName> This ensures that entity names and their field names are unique. Also, this agreement opens up the prospect of editing the fields of different objects in one form and automatically saving them by one post-request. Please note that ORM Backend does not use table and column names.

    "app-model-team" // entity App\Model\Team
    "app-model-team_trainer"  // entity App\Model\TeamTrainer
    "app-model-team.trainer" // association App\Model\Team::trainer
    "app-model-team.players" // collection App\Model\Team::players
    "app-model-team.trainer.createdBy.role.name" // The depth is not limited

    Aliases

    The converted names are very long and inconvenient to use in your code. ORM Backend offers to send requests by url containing the full class name, and use aliases in the code. For examle, all requests to App\Model\Team entity can be passed by url containing the transformed class name as a parameter. These requests can be handled by a single controller that performs actions for all models.

    // routes.php
    // uri /api/entities/app-model-team
    Route::get('/api/entities/{entity}', 'ApiController@search')

    By default, the alias is calculated as a short class name with the first lowercase character. You can change this behavior, but it is important that the alias matches the alias on the frontend.

    Structure of Json Query

    {
      select: [],
      join: [],
      filter: [],
      order: []
    }

    Select

    We assume that the smallest unit of the transmitted information is an object. Therefore the select part can only contains association and collection names.

    select: [
      "team",
      "team.trainer",
      "team.players"
    ]

    Join

    You don't really need to write anything here. All necessary joins are performed automatically. This part of Json Query can be omitted.

    Filter

    All values ​​must be strongly typed. An exception will be thrown if the value cannot be cast to the type that specified in the mapping.

    Conjunctions: or, and.

    filter: [
      "or",
      [...comparison1],
      [...comparison2],
      [...comparison3]
    ]
    filter: [
      "and",
      [...comparison1],
      [...comparison2],
      [...comparison3]
    ]
    // "and" can be omitted
    filter: [
      [...comparison1],
      [...comparison2],
      [...comparison3]
    ]

    Operators: eq, neq, gt, gte, lt, lte, like, notLike.

    filter: [
      ["team.trainer.id", "eq", "3286b84b-314e-424b-b23e-e586cdf2cca7"],
      ["team.inFinal", "neq", false],
      ["team.player.age", "gt", 18],
      ["team.player.age", "gte", 18],
      ["team.player.age", "lt", 18],
      ["team.player.age", "lte", 18],
      ["team.name", "like", "amigos"],
      ["team.name", "notLike", "%amigos%"]
    ]

    Operators: isNull, isNotNull.

    filter: [
      ["team.trainer.id", "isNull"],
      ["team.name", "isNotNull"]
    ]

    Operators: in, notIn.

    filter: [
      ["team.createdBy.role.code", "in", ["admin", "guest"]],
      ["team.id", "notIn", ["c32e99ec-6a2b-473d-82de-ff177b1fcae6", "77d6e9dd-c38f-40be-9c39-fc4daa46d086"]],
    ]

    Operator between.

    filter: [
      ["team.createdAt", "between", "2020-01-01", "2020-01-31"]
    ]

    Nested criteria are fully supported. The depth is not limited.

    filter: [
      [...comparison]
      [
        "or",
        [...comparison],
        [ [...comparison], [...comparison] ]
      ]
    ]

    Order

    By default, sorting is in ascending order. To sort in descending order, the field must be prefixed with a minus. The number of fields is not limited.

    order: [
      "team.name", "-team.trainer.name"
    ]

    Pagination

    The pagination is not a part of Json Query. It can be done by passing page and perpage parameters to the query string. Pagination is supported in two modes: normal and cursor. In normal mode, the total number of elements is counted, while in cursor mode, this calculation is not performed. Cursor pagination is useful with infinite scrolling.

    Sending request

    The supported request methods are GET, POST, PUT and DELETE. The supported media-types are application/json, application/x-www-form-urlencoded and multipart/form-data. JQuery deparam plugin can be useful for converting Json Query to query-string parameters. Read RESTfull Services about requests.

    Json to PHP array

    Let's say we want to fetch all teams with the name like Amigos that Gary trains. The Json representation for this query looks very simple and intuitive. It can be obtained as a request payload or converted from a URL query string. Please note that the conversion from the payload is performed using PHP native json_decode function, and from the query string we will retrieve a ready-made array provided by the PHP itself. Nothing new here, everything is very simple.

    // json query example
    {
        select: [
            "team",
            "team.players",
            "team.trainer"
        ],
        filter: [
            [
                "or",
                ["team.name", "like", "amigos"],
                ["team.trainer.name", "like", "gary%"],
            ],
            ["team.trainer.id", "isNotNull"]
        ],
        order: ["team.name", "-team.players.birthdate"]
    };
    // php array
    [
        'select' => [
            "team",
            "team.players",
            "team.trainer"
        ],
        'filter' => [
            [
                "or",
                ["team.name", "like", "amigos"],
                ["team.trainer.name", "like", "gary%"],
            ],
            ["team.trainer.id", "isNotNull"]
        ],
        'order' => ["team.name", "-team.players.birthdate"]
    ];

    PHP array to DQL

    ORM Backend provides code that automatically converts a Json Query to DQL. Automatic LEFT OUTER JOIN building based on parts of select, filter and order. First of all, the query parameters are cleared using filter_var. They are then casted to strong type according to the Doctrine mapping during Doctrine query building.

    Usually, to build the query, we write something like following

    // filter only
    $qb->where(
        $qb->expr()->andX()->addMultiple([
            $qb->expr()->orX()->addMultiple([
                $qb->expr()->like('team.name', ':name1'),
                $qb->expr()->like('team_trainer.name', ':name2'),
            ]),
            $qb->expr()->notNull('team_trainer.id')
        ])
    );

    The result

    // DQL
    FROM Team team
    LEFT JOIN team.players team_players
    LEFT JOIN team.trainer team_trainer
    WHERE (
        team_players.name LIKE :name1
        OR team_trainer.name LIKE :name2
    ) AND team_trainer.id NOT NULL

    It would be more efficient to use the trainer's inner join in this example. But currently only left joins are used in this project. Let's say you want to filter teams by trainer name, but you do not need to display the trainer's data.

    You don't have to write

    {
      select: [
        "team",
        "team.trainer"
      ],
      filter: [
        ["team.trainer.name", "like", "gary"]
      ] 
    }

    Just enough

    {
      filter: [
        ["team.trainer.name", "like", "gary"]
      ] 
    }

    ORM backend will determine that a join is needed and build the correct DQL. Sorting is similar. You can always select only those entities that you need, as well as filter and sort by fields of other related entities. Also, there is no need to specify the alias of the root entity in the select part. It is always added by ORM Backend.

    Using in PHP code

    Json-like query can also be used in your own PHP code. The supported operators are eq neq gt gte lt lte isNull isNotNull like notLike in notIn between. DQL expression and aliases are also available. Simple and intuitive Json Query is tempting to use it everywhere. Below are a couple of examples of actual code.

    /**
      *
      * @param  \Illuminate\Http\Request  $request
      * @return  \Illuminate\Http\Response
      */
    public function index(Request $request)
    {
        $parameters = [
            'select' => [
                'event.previewImage',
                'event.place'
            ],
            'filter' => [
                ['event.status', 'eq', StatusEnum::PUBLISHED]
            ],
            'order' => ['event.sort', '-event.createdAt']
        ];
        
        $paginator = $this->cursor($this->repository->createQuery(Event::class, $parameters, 'event'))->appends($request->all());
    
        return view('app.index', ['paginator' => $paginator]);
    }
    /**
      *
      * @param \Illuminate\Http\Request $request
      * @param string $eventCode
      * @param string $placeCode
      * @param string $adCode
      * @return \Illuminate\View\View
      */
    public function details(Request $request, string $eventCode, string $placeCode, string $adCode)
    {
        $parameters = [
            'select' => [
                'ad.event',
                'ad.place',
                'ad.images'
            ],
            'filter' => [
                ['ad.event.code', 'eq', $eventCode],
                ['ad.place.code', 'eq', $placeCode],
                ['ad.code', 'eq', $adCode],
                ['ad.event.status', 'eq', StatusEnum::PUBLISHED],
                ['ad.place.status', 'eq', StatusEnum::PUBLISHED],
                ['ad.status', 'eq', StatusEnum::PUBLISHED]
            ]
        ];
        
        try {
            /**
             * 
             * @var \App\Entities\Ad $ad
             */
            $ad = $this->repository->createQuery(Ad::class, $parameters, 'ad')->getSingleResult();
        } catch (NoResultException $e) {
            abort(404);
        }
        
        return view('app.details', [
            'event' => $ad->getEvent(),
            'place' => $ad->getPlace(),
            'ad' => $ad
        ]);
    }