Using pagination with CakePHP’s finderQuery SQL

Cakephp 1.2

Update February 2009: this article is now about a year old and deals with an early beta versions of CakePHP 1.2. In the mean time, CakePHP 1.2 has been released with many improvements and great documentation. Please don’t use this patch, use this instead. I’ll leave the article below for reference.

Cakephp lets you define various kinds of associations between models. The principle is really simple, just define arrays like $hasMany in your main model, where associations with other models are defined.

For most purposes this does the trick, most associations are made by convention, but you can override these as well for e.g. database (foreign-) keys.

From there on, you can use all cake built-in functionality to retrieve model data. One such feature is auto pagination, where the page size is set through the value of the limit field in the model’s definition.

If you’re crafting something really complex, you can define your own SQL query in the finderQuery field. This is very nice, but the trade-of is that the other fields are ignored in the query. And if fields limit and offset are ignored, you no longer get auto pagination.

The patch below fixes this for the current development tree of CakePHP 1.2 (revision 6461).

This patch inspects the finderQuery SQL and applies the values of the relevant model fields if the SQL does not contain a value for LIMIT and ORDER BY, respectively.

So now you have:

  • limit value from model applied, unless LIMIT is defined in finderQuery
  • order value from model or application query applied, unless ORDER BY is defined in finderQuery
  • offset value from model or application query applied, unless LIMIT is defined in finderQuery

Patch for /cake/libs/model/datasources/dbo_source.php

  1. Index: dbo_source.php
  2. ===================================================================
  3. — dbo_source.php  (revision 6461)
  4. +++ dbo_source.php  (working copy)
  5. @@ -1046,7 +1046,19 @@
  7. if ($external && isset($assocData[‘finderQuery’])) {
  8. if (!empty($assocData[‘finderQuery’])) {
  9. –            return $assocData[‘finderQuery’];
  10. +            $qry = $assocData[‘finderQuery’];
  11. +            // if no ORDER is specified in finderquery, use model definition
  12. +            if (!preg_match(‘/\sORDER\s+BY\s/i’, $qry)) {
  13. +               $orderData = (empty($queryData[‘order’])) ? $assocData[‘order’] : $queryData[‘order’];
  14. +               $qry .= ‘ ‘ . $this->order($orderData);
  15. +            }
  16. +            // if no LIMIT/OFFSET is specified in finderquery, use model definition
  17. +            if (!preg_match(‘/\sLIMIT\s/i’, $qry)) {
  18. +               $limitData = (empty($queryData[‘limit’])) ? @$assocData[‘limit’] : $queryData[‘limit’];
  19. +               $offsetData = (empty($queryData[‘offset’])) ? @$assocData[‘offset’] : $queryData[‘offset’];
  20. +               $qry .= ‘ ‘ . $this->limit($limitData, $offsetData);
  21. +            }
  22. +            return $qry;
  23. }
  24. }

[ratings]

Reblog this post [with Zemanta]

Post to Twitter Tweet This Post to Delicious Delicious Post to Reddit Reddit This Post Post to StumbleUpon Stumble This Post