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:

getCriteriaFromTags($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:

$pager->setPeerMethod('doSelectByTags');

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:
$statement->setLimit($c->getLimit());
$statement->setOffset($c->getOffset());
// and the rest of the code is as usual
}