aelfannir/doctrine-query-paginator

Simple, powerful package for search, sort, filter & paginate database

0.3.0 2021-08-23 09:20 UTC

Requires

• php: >=8.0
• doctrine/doctrine-bundle: ^2.4
• doctrine/orm: ^2.7
• guzzlehttp/guzzle: ^7.3
• symfony/console: 5.3.*
• symfony/flex: ^1.3.1
• symfony/yaml: 5.3.*

Requires (Dev)

• phpunit/phpunit: ^9.5
• symfony/browser-kit: 5.3.*
• symfony/css-selector: 5.3.*
• symfony/maker-bundle: ^1.20
• symfony/phpunit-bridge: 5.3.*

MIT

• Elfannir

README

Make sure Composer is installed globally, as explained in the installation chapter of the Composer documentation.

Applications that use Symfony Flex

Open a command console, enter your project directory and execute:

$ composer require aelfannir/doctrine-query-paginator

Applications that don't use Symfony Flex

Step 1: Download the Bundle

Open a command console, enter your project directory and execute the following command to download the latest stable version of this bundle:

$ composer require aelfannir/doctrine-query-paginator

Step 2: Enable the Bundle

Then, enable the bundle by adding it to the list of registered bundles in the config/bundles.php file of your project:

// config/bundles.php

return [
    // ...
    AElfannir\DoctrineQueryPaginator\DoctrineQueryPaginatorBundle::class => ['all' => true],
];

Docs

Filter

Filters describe conditions on table property values to include in the results from a database query.

A filter can be a property filter or it can be a compound filter formed by joining mtuliple property filters using a logical "AND" or "OR" operation.

Compound filters can be nested.

Property filters

property

Type string, The name or ID of the property to filter on.

operator

Type string, The operator ID used to generate comparision expression. Possible values include;

value

Can be one of the following types array, string or number

Examples

{
  "filter": {
    "operator": "AND",
    "filters": [
      {
        "property": "createdAt",
        "operator": "DATETIME.GTE",
        "value": "2021-01-01 00:00"
      },
			{
        "property": "createdAt",
        "operator": "DATETIME.LT",
        "value": "2022-01-01 00:00"
      }
    ]
  }
}

Compound filters

A compound filter object combines several database property filters together. A compound filter can even be combined within a compound filter.

The compound filter object contains one of the following keys:

operator

The condition used to combine filters, can be "AND" or "OR"

filters

array of property filters

Examples

{
  "filter": {
    "operator": "OR",
    "filters": [
      {
        "property": "firstName",
        "operator": "STRING.EQ",
        "value": "Mohammed"
      },
      {
        "operator": "AND",
        "filters": [
          {
            "property": "createdAt",
            "operator": "DATETIME.GTE",
            "value": "2021-01-01 00:00"
          },
          {
            "property": "createdAt",
            "operator": "DATETIME.LT",
            "value": "2022-01-01 00:00"
          }
        ]
      }
    ]
  }
}

Search

Type string, search in the target table root's properties

Example

{
  "search": "foo"
}

Pagination

Type object should contain the metadata that required for the datatable pagination to work.

pagination.page

Type number, The current page number.

pagination.pages

Type number, Total number of pages available in the server.

pagination.perPage

Type number, The current page number.

pagination.total

Type number, Total all records number available in the server

Example

• Request

{
  "pagination": {
    "page": 1,
    "perPage": 10
  }
}

• Response

{
  "pagination": {
    "page": 1,
    "perPage": 10,
    "total": 100,
    "pages": 10
  }
}

Sorts

Sort objects describe the order of database query results. The Query a database endpoint accepts an array of sort objects in the sorts body parameter. In the array, the lower index object takes precedence. Each sort object contains the following keys

property

Type string, The name of the property to sort against.

direction

The direction to sort. Possible values include "ASC" and "DESC".

Example

{
  "sorts": [
    {
      "property": "firstName",
      "direction": "ASC"
    },
    {
      "property": "lastName",
      "direction": "DESC"
    }
  ]
}

Join

table

Type string, join table name.

alias

optional, type string, note that when alias is undifined table's value is used as ajoin table's alias

join

optional, array of join conditions,

Example

{
  "join": [
    {
      "table": "category",
      "join": [
        {
          "table": "parent",
          "alias": "subCategory",
          "join": [
            {
              "table": "parent",
              "alias": "subCategory2"
            }
          ]
        }
      ]
    }
  ]
}