Brentertainment

How many of you encounter the need for complex where statements in Doctrine? What application DOESN’T require a complex where statement on occasion? Well, what I’m getting at is most of them do!.

If and when you encounter such a situation, you can choose one of three paths (but choose wisely):

1. - Break your query into smaller, more manageable queries and execute them separately
2. - Drop down to DQL, or even straight SQL (essentially bypassing the ORM layer) for more granular control and to utilize options not available in the Doctrine_Query object (subqueries, etc).
3. - EXTEND THE DOCTRINE QUERY OBJECT!

One and Two are great options, and should always be considered. However, there are occasions where a query object is just more convenient. For example, if you are using the default symfony admin generator, you should be aware its core functionality requires a Query object. And hey, what’s the use of writing all this object oriented code if we can’t subclass every now and then? Plus, it’s a lot of fun.

Predicament

I want to filter a query (any query) by a date range. I want to take whatever timestamp fields are tied to this object, and make sure at least ONE of them falls in that date range. I call the filter “Occurs In Range”. Here’s an example:

• - I have a list of assigned tasks
• - Each Task object has an assignment date, a due date, and an action date.
• - I want to filter my tasks by a date range, and find any tasks where any of those dates fall between my range

Task 1:

• title: Launch that website for that friend
• description: Jack wants a website professing his love for his cat. He wants you to make it for him.
• start date: April 1st, 2010
• end date: April 30th, 2010
• action date: null

Task 2:

• title: Purchase Food for St. Patrick’s Day Party
• description: You are having a wild party for St. Patrick’s Day. Don’t forget cabbage!
• start date: March 1st, 2010
• end date: March 16th, 2010
• action date: March 17th

How are we going to write a filter that adds this query? Sure, we could iterate over the timestamp fields, adding ->orWhere’s. But if we have any where clause already attached to this query, it’s going to screw with the logic. Why would you let Doctrine screw with the logic? I tell you the truth brother, this is a thing of evil.

Solution

By subclassing the Doctrine_Query object, we can create our own query. I called mine Doctrine_Query_Extra, although other good suggestions were Doctrine_Pimp_Query, Doctrine_Bling_Query, and Bling_Pimp_Doctrine. I then added a few methods to allow us better handling of our where clauses. These functions include andClause for beginning a parenthetical where clause with AND, orClause for beginning a parenthetical clause with OR, and endClause for ending either clause. Here is an example of these functions in action.

  public function addOccursInRangeColumnQuery(Doctrine_Query $query, $field, $value)
  {
    $query->andClause();

    foreach ($this->getOption('occurs_in_range.filter_fields') as $i => $f)
    {
      $this->orClause()
          ->addDateQuery($query, $f, $value)
          ->endClause();
    }

    $query->endClause();

    return $query;
  }
 

How cool is that? We in effect just wrapped our entire filter in an AND statement, and made sure our individual date query statements were OR’d (i.e. only ONE of the object’s dates need to be in the range). Shazam.

Some of you are confused with the above function. “What is addOccursInRangeColumnQuery”? Quite simply, if you set an ‘occurs_in_range’ filter field to the “sfWidgetFormFilterDate” widget in your Form Filter class (something like *Model*FormFilter.class.php) and also a “occurs_in_range.filter_fields” option to your array of date fields:

  $this->setOption('occurs_in_range.filter_fields', array('start_date', 'end_date', 'active_date'));

…Doctrine and symfony will automatically look to call that method when that filter is used. Put the above code in your BaseFormFilterDoctrine class and you’ll be ship shape. Take a peak at my Code Snippets link at the bottom to see this in context.

In Doctrine 1.2+ you can tell Doctrine to use your subclass by calling

$manager->setAttribute(Doctrine_Core::ATTR_QUERY_CLASS, 'Doctrine_Query_Extra');

in your project configuration. If you are using less than Doctrine 1.2, you can create the Doctrine_Query_Extra class by calling Doctrine_Query_Extra::create(), or by overloading the Doctrine::getTable('TableName')->createQuery() method on a table by table basis, which will look something like this:

    public function createQuery($alias = '')
    {
        if ( ! empty($alias)) {
            $alias = ' ' . trim($alias);
        }
        return Doctrine_Query_Extra::create($this->_conn)->from($this->getComponentName() . $alias);
    }

You can download this class using the link below. If you use it, let me know if it works well for you, and please post here if you can think of ways to improve it!

Links

• Doctrine_Query_Extra class
• Code Snippets

**Update**
Fixed an issue in Doctrine_Query_Extra where an error was thrown for empty clauses (i.e. $query->andClause()->endClause()). This is now fixed.