Doctrine pagination (Using SQL_CALC_FOUND_ROWS)

Starting from version 4.0 in MySQL DBMS, there is a rather convenient possibility of counting the number of all suitable records for a query, when the number of records is limited by LIMIT. When working with a search in the database, as well as when sampling from tables with a large number of records, such functionality is simply necessary. In this article, I will explain how you can use this feature in Doctrine ORM.





Immediately I would like to say that this method of pagination was chosen because it was necessary to implement compatibility with the current project where it is used.

Syntax

In the SELECT query, you must specify the SQL_CALC_FOUND_ROWS option in front of the column list. Here is the beginning of the syntax description of the SELECT clause

SELECT

[ALL | DISTINCT | DISTINCTROW]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ... ...

Thus, executing a SELECT SQL_CALC_FOUND_ROWS query, the DBMS will calculate the total number of rows matching the query condition, and store that number in memory. Naturally, the SELECT SQL_CALC_FOUND_ROWS query makes sense only when using the constraint (LIMIT). Immediately after completing the query for retrieving the number of records, you need to execute another SELECT query: SELECT FOUND_ROWS () ;. As a result, MySQL returns one row with one field, in which the number of rows will be stored.

')

An example of the queries themselves:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE number> 100 LIMIT 10;

SELECT FOUND_ROWS ();

The first query returns (displays) 10 rows of the tbl_name table for which the number> 100 condition is met. The second call to the SELECT command will return the number of rows that the first SELECT command would return if it were written without the LIMIT statement. Although when using the SELECT SQL_CALC_FOUND_ROWS command, MySQL should recalculate all the rows in the result set, this method is still faster than without LIMIT, since you do not need to send the result to the client.



More information about using this design can be found in the post of kurtkrut and Valery Leontiev .

Task

So for paging the Doctrine sample, you can use the following methods:

1. Select all records from the database, and then determine by condition the ones we need. I hope you will not do this :)
2. Use two queries. The first without a LIMIT part with count (id) to determine the number of records, and the second with LIMIT directly for sampling. Many people do pagination this way. He advised me to use omez and not to bother. But in my project I already used p.4
3. Use Doctrine_Pager, which in principle does the same as in p.2
4. Use SQL_CALC_FOUND_ROWS expression in SELECT query

With Doctrine_Pager, everything is clear in principle. Here is an example:

// Determine the parameters of the pagination

$ currentPage = 1; // Current page

$ resultsPerPage = 50; // Number of results per page



// Create the paginator object based on the DQL query

$ pager = new Doctrine_Pager (

Doctrine_Query :: create ()

-> from ('User u'),

$ currentPage,

$ resultsPerPage

);

Next we get in the object exactly those records that we need for the current page. Read more here .

Problem

But using SQL_CALC_FOUND_ROWS is not so simple. Despite the criticism of this method of pagination ( here and here ), or rather, for reasons of compatibility with the current project, it became necessary to use SQL_CALC_FOUND_ROWS in Doctrine. But then continuous whistles began. When used in select () for DQL (Doctrine Query Language), the following request was generated.



From dql

$ q = Doctrine_Query :: create ()

-> select ('SQL_CALC_FOUND_ROWS *')

-> from ('User u');

The result was the following SQL

SELECT SQL_CALC_FOUND_ROWS AS o__0 FROM User ...

this “AS o__0” spoiled everything :) Doctrine interpreted SQL_CALC_FOUND_ROWS as the name of the field.

Decision

I will not talk about the many hours of dancing with a tambourine and tinkering in the depths of Doctrine, I'd rather write about how I solved this problem. First, we had to somehow throw SQL_CALC_FOUND_ROWS through DQL and DBAL into a SQL query. This is solved by using your own query class, and changing the parseSelect ($ dql) and _buildSqlQueryBase () functions

<? php

class MyDoctrine_Query extends Doctrine_Query

{

public function parseSelect ($ dql)

{



...

// check for DISTINCT keyword

if ($ first === 'DISTINCT') {

$ this -> _ sqlParts ['distinct'] = true;



$ refs [0] = substr ($ refs [0], ++ $ pos);

}

/ * Here we add a check on the existence of the SQL_CALC_FOUND_ROWS query.

and if it is, set to true the value of the query parameter of the same name

* /



if ($ first === 'SQL_CALC_FOUND_ROWS') {

$ this -> _ sqlParts ['sql_calc_found_rows'] = true;

$ refs [0] = substr ($ refs [0], ++ $ pos);

}

...

}



protected function _buildSqlQueryBase ()

{

switch ($ this -> _ type) {

case self :: DELETE:

$ q = 'DELETE FROM';

break;

case self :: UPDATE:

$ q = 'UPDATE';

break;

case self :: SELECT:

$ distinct = ($ this -> _ sqlParts ['distinct'])? 'DISTINCT': '';

/ * And here we actually add the expression to the query * /

$ sql_calc_found_rows = ($ this -> _ sqlParts ['sql_calc_found_rows'])? 'SQL_CALC_FOUND_ROWS': '';

$ q = 'SELECT'. $ sql_calc_found_rows. ' '. $ distinct. implode (',', $ this -> _ sqlParts ['select']). 'FROM';

break;

}

return $ q;

}

}

?>

Next you need to tell Doctrine to use our query class.

$ manager = Doctrine_Manager :: getInstance ();

require_once (dirname (__ FILE__). '/lib/doctrine_extra/MyDoctrine/Query.php');

$ manager-> setAttribute (Doctrine :: ATTR_QUERY_CLASS, 'MyDoctrine_Query');

After that, to execute DQL using SQL_CALC_FOUND_ROWS it is enough to execute it in the select () part.

$ q = Doctrine_Query :: create ()

-> select ('SQL_CALC_FOUND_ROWS *')

-> from ('User u')

-> limit (10);

Now the attentive reader will ask how to get the same number of records that we would receive without the LIMIT part.

To do this, write your EventListener :

<? php

class MyDoctrine_EventListener_SqlCalcFoundRows extends Doctrine_EventListener {



private static $ foundRows = null;



/ * Called immediately after the request is completed, which is what we need. * /

public function postQuery (Doctrine_Event $ event) {

$ pdo = Doctrine_Manager :: connection () -> getDbh ();

$ sql = "SELECT FOUND_ROWS ()";

$ stmt = $ pdo-> query ($ sql);

$ result = $ stmt-> fetch ();

$ count = $ result ['FOUND_ROWS ()'];

self :: $ foundRows = (int) $ count;

}



/ * Returns the number of found entries * /

public static function getFoundRowsCount () {

return self :: $ foundRows;

}

}

?>

We connect it to the system:

require_once (dirname (__ FILE__). '/lib/doctrine_extra/MyDoctrine/EventListener/SqlCalcFoundRows.php');

$ conn-> addListener (new MyDoctrine_EventListener_SqlCalcFoundRows ());

And now the number of entries can be obtained with the following command:

MyDoctrine_EventListener_SqlCalcFoundRows :: getFoundRowsCount ();

It is very convenient to integrate this function into the D octrine collection.

$ q = Doctrine_Query :: create ()

-> select ('SQL_CALC_FOUND_ROWS *')

-> from ('User u')

-> where ('u.status = "active")

-> limit (10)

-> offset (5);

$ allCount = $ users-> getFoundRowsCount ();

To do this, you also need to inherit Doctrine_Collection.

<? php

class MyDoctrine_Collection extends Doctrine_Collection

{

/ **

* Returns the count of results from a previous query with SQL_CALC_FOUND_ROWS

* /

public function getFoundRowsCount () {

if (in_array ('MyDoctrine_EventListener_SqlCalcFoundRows', get_declared_classes ())) {

return MyDoctrine_EventListener_SqlCalcFoundRows :: getFoundRowsCount ();

} else {

return NULL;

}

}

}

?>

and connect it to bootstrap

require_once (dirname (__ FILE__). '/lib/doctrine_extra/MyDoctrine/Collection.php');

$ manager-> setAttribute (Doctrine :: ATTR_COLLECTION_CLASS, 'MyDoctrine_Collection');

I hope that if someone needs to use SQL_CALC_FOUND_ROWS, he will find this article, and he will not step on the same rake as I :)

If interested, I will write more on how I finish Doctrine.



PS omez helped in the development.