paknahad/querifier

A library to get requested query from the client and generate a query for Doctrine, Cake-php ORM or Laravel Eloquent

v0.2.1 2019-05-15 05:34 UTC

README

Latest Stable Version Build Status License: MIT

Querifier

Installing

composer require paknahad/querifier

Usage

Symfony & Doctrine

<?php
use Symfony\Bridge\PsrHttpMessage\Factory\DiactorosFactory;
use Paknahad\Querifier\Filter;
...
    $psrFactory = new DiactorosFactory();
    $psrRequest = $psrFactory->createRequest($request);

    $filter = new Filter($psrRequest);
    $filter->applyFilter($repository->createQueryBuilder('alias'));

Examples:

Expression:

http://example.com/books?q=title:php^author.name:hamid

Criteria:

http://example.com/books?filter[title]=php&filter[author.name]=hamid

SQL:

SELECT * FROM books AS b INNER JOIN authors AS a ...
WHERE
    b.title = 'php'
    AND
    a.name = 'hamid'

Advanced query:

Expression:

http://example.com/books?q=title:php^(author.name:%hamid%|(publish_date>2017-1-1^publish_date<2017-6-1))

Criteria:

books?filter[title]=php&filter[_c1][author.name][_like]=%hamid%&filter[_c2][publish_date][_gt]=2017-1-1&filter[_c3][publish_date][_lt]=2017-6-1]&filter[_c4][_cmb_and]=_c2,_c3&filter[_cmb_or]=_c4,_c1

SQL:

SELECT * FROM books AS b INNER JOIN authors  AS a …
WHERE
    b.title = 'php' 
    AND
    (
        (
            b.publish_date > '2017-1-1'
            AND
            B.publish_date < '2017-6-1'
        )
        OR
        a.name LIKE '%hamid%'
     )

Expression Parser

Simple query

simple query makes by this structure:

example.com?q=FIELD_NAME:VALUE

Advanced query:

Logical Operators:

  • ^ -> AND
  • | -> OR

Comparision Operators:

  • : -> Equal or LIKE in case there is a % in value.
  • :null => IS NULL
  • <> -> Not Equal or NOT LIKE in case there is a % in value.
  • <>null => IS NOT NULL
  • > -> Greather than
  • < -> Less than

Criteria Parser

Simple query

simple query makes by this structure:

url?filter[FIELD_NAME]=VALUE

Advanced query:

1- Define conditions.

url?filter[CONDITION NAME][FIELD NAME][OPERATOR]=VALUE

2- Combine these conditions together.

url?filter[CONDITION NAME][COMBINE]=CONDITIONS SEPARATED BY “,”
  • Condition name(optional) : An identifier for using in combinations, must be started by “_” and followed by AlphaNumeric characters
  • Operator name(optional , Default: _eq) : Name of an operator such as _eq, _not_eq, _in, _gt, _lt, _like.
  • Combine : use to combine two or more conditions : _cmb_or , _cmb_and

Sorting

  • Ascending on name field: http://example.com/books?sort=name
  • Decending on name field: http://example.com/books?sort=-name
  • Multiple fields: http://example.com/books?sort=city,-name
  • Field on a relation: http://example.com/books?sort=author.name