Category: Propel

Every now and then, we use Sphinx to provide full text searching in MySQL InnoDB tables. Sphinx is pretty solid. It’s easy to set up, pretty fast, and easy to deploy.

My one big issue with Sphinx has always been making it play nice with Symfony, specifically Propel. The way Sphinx returns a result set is as an ordered list of [id, weight] for each document it matched. As outlined here the idea is to then hit your MySQL server to return the actual documents and use “ORDER BY FIELD(id, [id list])” to keep them in the right order that you received the list.

The problem is, Propel Criteria objects provide no mechanism to set an ORDER BY FIELD. This is an issue because if you drop Criterias you loose Propel Pagers which generally adds to a lot of duplicated code and is honestly just not very elegant.

Anyway, after some thought I came up with this solution.

If you read through the definition of “Criteria::addDescendingOrderByColumn()”:

	<?php
	/**
	* Add order by column name, explicitly specifying descending.
	*
	* @param string $name The name of the column to order by.
	* @return Criteria Modified Criteria object (for fluent API)
	*/
	public function addDescendingOrderByColumn($name)
	{
	$this->orderByColumns[] = $name . ' ' . self::DESC;
	return $this;
	}

view raw gistfile1.php hosted with by GitHub

All it really does is add the second part of the ORDER BY clause to an array which then gets joined up to build the final SQL. Because of this, you can actually just add an element onto the orderByColumns array which will cause Propel to execute an ORDER BY FIELD SQL statement.

To make the magic happen, I sub-classed Criteria and then added a addOrderByField() function to let me add a field to order by as well as a list to order by.

	<?php
	class sfCriteria extends Criteria {
	private $myOrderByColumns = array();
	/**
	* Add an ORDER BY FIELD clause.
	*
	* @param String $name The field to order by.
	* @param Array $elements A list to order the elements by.
	* @return unknown
	*/
	public function addOrderByField($name, $elements)
	{
	$this->myOrderByColumns[] = ' FIELD(' . $name . ', ' . join(", ", $elements) . ')';
	return $this;
	}
	public function getOrderByColumns(){
	return array_merge( $this->myOrderByColumns, parent::getOrderByColumns() );
	}
	}

view raw gistfile1.php hosted with by GitHub

8/8/12: Update per Simon’s comment below

Also add this function to make sure your ORDER BY FIELD columns get cleared:

	<?php
	public function clearOrderByColumns() {
	$this->myOrderByColumns=array();
	return parent::clearOrderByColumns();
	}

view raw gistfile1.php hosted with by GitHub

To use it, do something like this:

	<?php
	$ids = array(1, 3, 7);
	$c = new sfCriteria();
	$c->add( SomeModelPeer::ID, $ids, Criteria::IN);
	$c->addOrderByField( SomeModelPeer::ID, $ids);
	$results = SomeModelPeer::doSelect( $c );

view raw gistfile1.php hosted with by GitHub

And thats about it. Since sfCriteria is a sub-class of Criteria the code works seamlessly with existing PropelPagers and anything else that expects a Propel Criteria.