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.
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…
By François Zaninotto on Jul 14, 2007
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.
By smeves on Jul 22, 2007
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?
By Josh on Aug 8, 2007
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.
By ernie on May 12, 2008
woops.. hehe.. in reference to previous comment, “if you do this” means if you do your trick to add multiple conditions.
By ernie on May 12, 2008