Code snippets for symfony 1.x


Refine Tags

Snippets tagged "pager propel sql raw"

How to get a propel pager working with a custom SQL query

The problem

Let us take the example of snipeet! :-) The function that selects snippets by tags is a custom SQL query. When filtering by tags there is no pager anymore.

Why is that? The problem is that sfPropelPaginate only works with a Criteria object, not with a raw query. As a result you cannot have pagination with raw SQL query which is a rather severe limitation.

The solution

The solution is to first encode the parameter of the query in the criteria (that's the tricky bit) and then to set up a custom peer method.

As i said earlier we will take a simplified version of snipeet as an example. We assume that we have a function that creates a sql statement from a list of tags.

Here we go about setting the dummy criteria that encodes an array of tags:

  $c = new Criteria();
  for ($i = 1; $i <= count($tags); ++$i)
    $prefix = 't'.$i;
    // note that that SnippetTagPeer::TABLE_NAME  SnippetTagPeer::NAME are in fact arbitrary
    // any other table and table.column combination would work as well
    $c->addAlias($prefix.SnippetTagPeer::TABLE_NAME, SnippetTagPeer::TABLE_NAME);
    $c->add($prefix.SnippetTagPeer::NAME, $tags[$i-1]);
  return $c;

Now you can initialise a sfPropelPaginate object as usual with that Criteria object. When that is done you tell it to use your custom peer method:


Next you code the doSelectByTags method that performs the actual selection from a criteria object:

function doSelectByTags($c)
  // first you fetch the tags from the query
  $tags = array();
  foreach($c->keys() as $key)
    $tags[] = $c->get($key);
  // here comes your custom sql query 
  // it basically creates a $statement variable from the $tags array
  // .....
  // now you have to add the limit and offset:
 // and the rest of the code is as usual
by Olivier Verdier on 2006-05-25, tagged custom  pager  pagination  propel  query  raw  sql