Pagination in Doctrine (Using SQL_CALC_FOUND_ROWS)

Starting from version 4.0, the MySQL DBMS has a rather convenient ability to count the number of all records matching the query, when the number of records is limited by LIMIT. When working with a search in the database, as well as when fetching from tables with a large number of records, such functionality is simply necessary. In this article I will tell you how to use this feature in Doctrine ORM


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

Syntax

In the SELECT query, the SQL_CALC_FOUND_ROWS option must be specified before the column list. Here is the beginning of the description of the syntax of the SELECT construct.

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 this number in memory. Naturally, it makes sense to query SELECT SQL_CALC_FOUND_ROWS only when using a constraint (LIMIT). Immediately after executing the query for sampling, to obtain the number of records, you need to execute another SELECT query: SELECT FOUND_ROWS () ;. As a result, MySQL will return 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 will return (output) 10 rows of the tbl_name table for which the condition number> 100 is fulfilled. 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 a LIMIT expression. Although when using the SELECT SQL_CALC_FOUND_ROWS command, MySQL must recalculate all the rows in the result set, this method is still faster than without LIMIT, since it is not necessary to send the result to the client.

You can read more about the use of this design in kurtkrut's post and by Valery Leontyev .

Task

So, to paginate a Doctrine sample, you can use the following methods:

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

With Doctrine_Pager, everything is basically clear. Here is an example:

// Define the parameters of pagination
$ currentPage = 1; // Current page
$ resultsPerPage = 50; // Number of results per page

// Create a paginator object based on a DQL query
$ pager = new Doctrine_Pager (
Doctrine_Query :: create ()
-> from ('User u'),
$ currentPage,
$ resultsPerPage
);

Then we get in the object exactly the records that we need for the current page. More details can be read here .

Problem

But using SQL_CALC_FOUND_ROWS is not so simple. Despite 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 query was generated.

From DQL

$ q = Doctrine_Query :: create ()
-> select ('SQL_CALC_FOUND_ROWS *')
-> from ('User u');

It turned out the following SQL

SELECT SQL_CALC_FOUND_ROWS AS o__0 FROM User ...

this “AS o__0” spoiled everything :) Doctrine took 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 picking in the bowels of Doctrine, I’ll write better about how I solved this problem. First, we needed to somehow throw SQL_CALC_FOUND_ROWS through DQL and DBAL into an SQL query. This was solved by using our own query class, and changing the functions parseSelect ($ dql) and _buildSqlQueryBase ()

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 the existence check in the SQL_CALC_FOUND_ROWS query
and if it is, set 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 must 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 specify 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 very number of records that we would get without the LIMIT part.
To do this, write your EventListener :

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 value of the number of records found * /
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 records can be obtained with the following command:

MyDoctrine_EventListener_SqlCalcFoundRows :: getFoundRowsCount ();

It is very convenient to integrate this function into the Dostrine 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 must also inherit the Doctrine_Collection

class MyDoctrine_Collection extends Doctrine_Collection
{
/**
* Возвращает кол-во резщультатов предыдущего запроса с выражением SQL_CALC_FOUND_ROWS
*/
public function getFoundRowsCount() {
if(in_array('MyDoctrine_EventListener_SqlCalcFoundRows', get_declared_classes())) {
return MyDoctrine_EventListener_SqlCalcFoundRows::getFoundRowsCount();
} else {
return NULL;
}
}
}
?>

и подключить её в 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 will not step on the same rake as me :)
If interested, I will write more on the topic of how I finish Doctrine.

PS omez helped in the development .