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()”:

	/**
	 * 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;
	}

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.

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() );
  }
}

To use it, do something like this:

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

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.

Tags: open source, php, Propel, symfony

This entry was posted on Tuesday, October 13th, 2009 at 6:22 pm and is filed under General, open source. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.