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.

I know this is not entirely revolutionary, but I saw a whole plugin dedicated to it, which I found to be quite overkill (when a simple blog post will most likely do!).

For symfony 1.3 and 1.4, it should go something like this:

   // In sfActions
    chdir(sfConfig::get('sf_root_dir')); // Trick plugin into thinking you are in a project directory
    $task = new sfMyVerySpecialTask($this->dispatcher, new sfFormatter());
    $task->run(array('argument_name' => 'argument'), array('option_name' => 'option'));

If you don’t have access to the dispatcher object in your current context, you can call $context->getEventDispatcher() on an sfContext instance. If you don’t have access to an instance of this class, you can always call the singleton sfContext::getInstance() to retrieve one. Be forewarned, however. Calling the singleton will throw an exception in the event that no context exists! I would advise wrapping the task in a if(sfContext::hasInstance()) statement. This will gracefully degrade running this task, rather than throwing an exception.

Fantasy: Your client has an incredibly simple, intuitive, and cohesive ACL schema in mind. Permission and group names make sense, never change, and current users perpetually encounter properly restricted behavior. While we’re at it, you’re also able to code one-handed while scuba-diving the Caymans.

The Cold Hard Truth: Permission names are inconsistent, Groups are changed and reassigned, and your poor users are left dangling somewhere between “Why can I see the administrator’s Social Security Number?” and “The ‘Donate Large Sums of Money’ page is giving me permission denied!”

I created a simple solution to this problem with a few new symfony tasks now available via csSecurityTaskExtraPlugin. In a nutshell, the plugin allows you to more easily visualize the security coverage of your application. Here are some examples below:

$ ./symfony app:security frontend

The app:route-security task compares your security.ymls to all the routes in your application

$ ./symfony app:route-security frontend

You can also list who has access to which actions specified in security.yml with the group-security task.

 $ ./symfony app:group-security frontend

Pass the name of an sfGuardGroup object as the second argument to narrow down your output

 $ ./symfony app:group-security frontend author

List users who has access with the user-security task.

 $ ./symfony app:user-security frontend

Pass the username or id of an sfGuardUser object as the second argument to narrow down your output

$ ./symfony app:group-security frontend andyadministrator
OR
$ ./symfony app:group-security frontend 3

It’s fairly basic right now. The product of a few hours’ work and a desire to get something new out into the community. What other enhancements would you like to see to give you more/better control of your site’s security coverage?

What a great week! So many things have been going on. If you live in Nashville especially, you have a lot to be excited about. The official releases of symfony 1.3 and 1.4 provide a lot of exciting new functionality in the framework, which you can read about here. But even more exciting is this year’s advent calendar, a large part of with being contributed by Nashville’s own Ryan Weaver! Be sure to check it out, and consider purchasing the book from amazon to support the community.

Also, my personal project Symplist is now launched, and in Alpha. Please check it out and provide feedback. Symplist is a plugin site that exists for the community. Its greatest asset will come with individuals like you rating and commenting on plugins. Another section of the site, which I’ve dubbed “Community Lists”, is something I hope will be a great help to the community. This section will function as a repository for dense information. An example of this is a list I’m putting together of Symfony Best Practices. The highest-rated items in each list sort to the top, as do the lists themselves. Check them out, rate and add items, and leave some feedback!

On a separate note, Jon Wage (Nashville native) has recently been pushing PHP Interoperability Standards with PHP 5.3′s namespacing support. I would recommend checking it out! If you personally have any PHP projects, or are currently developing one, consider incorporating these standards.

If you want to get involved, consider joining the Nashville Symfony User’s Group, which meets at Centre{source} the first Tuesday of every month! Symfony is taking off, and Nashville is right on board!