SUBSCRIBE VIA RSS


Subscribe to our feed

Symfony Experts

Symfony Experts
If you have an urgent question for a symfony-related issue, this is the place to ask.

Topics

Stack Overflow


The old fashioned way

RECENT TUNES

May 12, 2007 – 5:29pm Left Joins with multiple conditions using Propel Criteria

Symfony by default uses Propel for its ORM. Although Propel supports supports custom SQL I have learned how to take advantage of the built-in Criteria class to help construct queries. As described in the Propel trac,

…its database neutrality and logical simplicity make it a good choice for expressing many common queries; however, for a very complex query, it may prove more effective (and less painful) to simply use a custom SQL query to hydrate your Propel objects. (propel trac)

In building a complex search system for a client, I benefited greatly from being able to add and remove columns and conditions to a criteria object on the fly without having to worry about the actual SQL syntax. However, eventually I hit a wall when I needed to add a left join using multiple columns. Adding left joins to a criteria object is typically done like this:

$c = new Criteria();
$c->addJoin(TABLE_A::ID, TABLE_B::ID, Criteria::LEFT_JOIN);
/* equivalent to: "...left join TABLE_B ON (TABLE_A.ID=TABLE_B.ID)" */

However, this syntax does not support a join with multiple conditions, so you can’t produce something like this:

...left join TABLE_B ON (TABLE_A.ID=TABLE_B.ID AND TABLE_B.VALUE > 100)

In playing around quite a bit and examining the source code, I realized you could hack the addJoin method call and trick it into adding the extra column for you. The addJoin method actually is creating a new Join object that has a left and right column definition as defined in the method call. These are used to build the join condition:

$condition = $join->getLeftColumn() . '=' . $join->getRightColumn();

(see the source)

So, if you add the extra columns to the RightColumn like so:

$c->addJoin(TABLE_A::ID, TABLE_B::ID.' AND '.TABLE_B::VALUE.' > 0', Criteria::LEFT_JOIN);

the generated SQL will yield something like this:

...LEFT JOIN TABLE_B ON (TABLE_A.ID = TABLE_B.ID AND TABLE_B.VALUE > 0)

Although it’s a hack and it may not work under all conditions, so far it has been a suitable workaround.

Posted by in  Web Development   |  

14 Responses to Left Joins with multiple conditions using Propel Criteria

  1. This is a good trick, thanks for sharing it.

    The sad thing is, it is completely not secure if the second condition of the JOIN relies on a request parameter (or any other tainted value). In your example, it is 0, so this is not a problem.

    To properly escape a value in the second condition of a join, you need much more code (about 30 lines) and that’s where Propel really gets heavy…

  2. smeves says:

    This is a good point. In these cases, perhaps it would be best to just write the query out rather than use Criteria objects. The escaping methods found in the “PreparedStatement” classes of the necessary database driver are all protected, so there is really no way to access them from outside those classes. Bummer. The MySQL driver uses the following escape function, so fortunately so it wouldn’t be too hard to run your extra value in your join statement through a similar process. This of course would break if you ever took your app to another DB system.

    class MySQLPreparedStatement extends PreparedStatementCommon implements PreparedStatement {        
        
        /**
         * Quotes string using native mysql function (mysql_real_escape_string()).
         * @param string $str
         * @return string
         */
        protected function escape($str)
        {
            return mysql_real_escape_string($str, $this->conn->getResource());
        }    
        
    }  
    
  3. Josh says:

    Thank you, I was having the exact same problem.

    I get curious when I run into issues like this… it’s not like we’re the first two people to ever need to use this kind of join. Why didn’t the Propel developers build this functionality in already? Are they planning to?

  4. ernie says:

    Love your site. I’ve been using this particular method in several places. I actually check my request parameters to ensure they’re exactly what i’m looking for.
    i.e. I may only pass integers so I make sure by putting something like:
    $foo = (int)$this->getRequestParameter(‘foo’);

    Also, I have found that if you do this and use:
    $c->setIgnoreCase(true);

    A column map error occurs. Nothing major as long as you know to watch for it. Took me a while to track down what the issue was.

  5. ernie says:

    woops.. hehe.. in reference to previous comment, “if you do this” means if you do your trick to add multiple conditions.

  6. Eric Bartels says:

    Thanks for the post. Found it after “reverse engineering” the creole-api. So next time I will google first because it would have been a bit faster 🙂

    fyi: http://propel.phpdb.org/trac/ticket/167#comment:13

  7. Paweł says:

    Doesn’t work with Propel 1.3 any more.

  8. Stephen Neander says:

    For Propel 1.3 try the following:
    $c->addJoin(TABLE_B::VALUE.’ > 0 AND ‘. TABLE_A::ID, TABLE_B::ID, Criteria::LEFT_JOIN);
    This worked for me.

  9. Thanks for this post!

  10. @Stephen Neander you’ve saved my live dude :))) thanks… and thanks to the author of this post.

  11. Jan Fabry says:

    If you use Propel 1.4 or 1.5, you can use the new addMultipleJoin method of your Criteria object [ http://www.propelorm.org/ticket/606 ]. However, it is not injection-safe, and will probably be rewritten for Propel 1.6 [ http://www.propelorm.org/ticket/878 ].

  12. Jasper says:

    U just saved my day! Thank you.

  13. Umang Shah says:

    Life Saver. Thanks for sharing it.

  14. Umang Shah says:

    This should work for propel 1.3
    $c->addJoin(
    array(TableAPeer::COL_A1, TableAPeer::COL_A2), //left columns
    array(TableBPeer::COL_B1, TableBPeer::COL_B2), // right columns
    CRITERIA::LEFT_JOIN
    );
    ===================
    will generate a query like
    table_a LEFT JOIN table_b ON (col_a1 = col_b1 AND col_a2 = col_b2)