Finding “Augmented” Propel Objects
Retrieving objects with Propel is easy with sfPropelFinderPlugin. And, since the addition of the with() method, you can hydrate related objects in a single query with no pain.
But there is still a use case where the sfPropelFinder can't help you, and this is when you need to add one or two columns from a related objects but not the whole object.
For instance, imagine that you have an Article and a Comment object, the Comment being related to the Article in the schema by a article_id column.
Until now, if you wanted to retrieve some comments and the related article title with no additiona query, you had to either get a resultset and parse it by hand, or hydrate both the Article and the Comment object:
findOne();
echo $comment->getArticle()->getTitle(); // requires an additional db query
$comment = sfPropelFinder::from('Comment')->
with('Article')->
findOne();
echo $comment->getArticle()->getTitle(); // no additional db query, but a whole object is hydrated just for one column
Today, the sfPropelFinder just made it simpler. You can add a single column from a related object with the withColumn() finder method, and retrieve it afterwards on any Propel object with the getColumn() method:
withColumn('Article_Title')->
findOne();
echo $comment->getColumn('Article_Title'); // no additional db query, and only one more column fetched
Internally, the finder will look for a relation between the Comment and the Article classes in the TableMap, and add the related join in its internal Criteria. It will call the Criteria's addAsColumn() method and deal with the augmented resultset so that the additional column is available in the Comment objects.
This new method accepts calculated columns through raw SQL code, and alias names. That means that you can add a column and reuse it in an orderBy() clause:
join('Comment')->
withColumn('COUNT(comment.ID)', 'NbComments')->
orderBy('NbComments')->
find();
Not only will the resulting Article objects be ordered by number of comment, but each individual object will have access to its number of comments through getColumn(). All that with a single SQL query and a very short syntax.
So the number of situations where you need to get a resultset instead of an array of Propel objects has reduced again. the sfPropelFinder takes care of all the dirty job for you, and lets you focus on the design of your business model rather than the details. If you didn't test it already, it's time to get the HEAD SVN revision of sfPropelFinderPlugin and give it a try.
Possibly related posts (automatically generated):
This is great stuff!! Thank you so much.
I have a question : does sfPropelFinderPlugin require a mysql database to be powered by InnoDB ? (because when I use the with() method it warns me that the Article table is not related with the Author table)
@ghis: I don't think so. Did you define your relation properly in the schema?
I think it is, because the Propel doSelectJoinXXX work fine.
Tanks a lot for this awesome work
You must have read my mind... I started using sfPropelFinder and was thinking it's such a shame i need to load in two extra objects just for one column value. I checked back on your site and saw this post. This is brilliant, thank you so much!!
P.S. I don't think think you mention the cool ability of getting the column value from a third table, all you need to do is tell the finder to join the linking table and it works! Bad example follows...
[sourcecode language='php'] $items = sfPropelFinder::from('OrderDetail') ->join('ProductHeader') ->withColumn('ProductDetail_LongDescription') ->find(); [sourcecode]
A note to other users, you need to enable behaviours in propel.ini for the getColumn() method to work. Took me a little while to figure this out...
nice to see the
sfPropelFinder::findFirst()andsfPropelFinder::findLast()methods in the svn..would be nice to have a 3rd parameter with the column name to choose for sorting. not everybody uses 'UpdatedAt', 'UpdatedOn', 'CreatedAt', 'CreatedOn', 'Id' for column names.. maybe this list of columns should also be configurable via yml file.
@Markus: You got it.
http://trac.symfony-project.com/changeset/9928
Hi Francois,
this is regarding the example where you use "COUNT('comment.ID', ...)."
I think this needs a group by clause added in order to work, otherwise you'd be mixing grouping clauses with non-grouping...
After adding a group by column, it worked.
Hope this helps, Daniel
@Daniel: You're right. A
groupBy()method is on its way to solve this.