This still trips me up. If you are using propel criteria objects and want to add a condition on a custom column, you have to do a bit of trickery to get it working just right.
Let’s say you want to add a custom column to your query, let’s say “points remaining” for the sake of example.
$c->addAsColumn('points_remaining', '('.UserPointsPeer::TOTAL_POINTS.' - '.UserPointsPeer::POINTS_EARNED.')');
And now you want to limit your query to just users who have a certain number of points remaining. Since this is on a custom column, you have to use the addHaving() method. This gets a bit more tricky because when adding criteria based on custom columns, Propel wants to generate criterion objects using column names which include the column’s table name and dot separator. So, we have to rely on the Criteria::CUSTOM functionality. No doubt this is a hack and it completely circumvents any built-in query escaping that is essential, but if you are careful about sanitizing your input, you can do the following. Note this code sample assumes that $pointsFilter['from'] and $pointsFilter['to'] contain the GET or POST input parameters for use in your query:
// first version where we want to fetch a custom column if (!empty($pointsFilter['from'])) { $pointsFilter['from'] = (int) $pointsFilter['from']; $criterion = $c->getNewCriterion(UserPointsPeer::ID, 'points_remaining >= '.$pointsFilter['from'], Criteria::CUSTOM); } if (!empty($pointsFilter['to'])) { $pointsFilter['to'] = (int) $pointsFilter['to']; if (isset($criterion)) { $criterion->addAnd($c->getNewCriterion(UserPointsPeer::ID, 'points_remaining <= '.$pointsFilter['to'], Criteria::CUSTOM)); } else { $criterion = $c->getNewCriterion(UserPointsPeer::ID, 'points_remaining <= '.$pointsFilter['to'], Criteria::CUSTOM); } } if (isset($criterion)) { $c->addHaving($criterion); }
Note that we have to use addHaving() to add our condition to our custom column.
If you don’t need to retrieve the custom column in your query but still want to limit your results on that criteria, you can use the add() method, but you have to change it up a bit:
// second version without adding a custom column if (!empty($pointsFilter['from'])) { $pointsFilter['from'] = (int) $pointsFilter['from']; $criterion = $c->getNewCriterion( UserPointsPeer::ID, // can be any column in your table, really '('.CurriculumPeer::TOTAL_POINTS.' - '.UserPointsPeer::POINTS_EARNED.') >= '.$pointsFilter['from'], Criteria::CUSTOM ); } if (!empty($pointsFilter['to'])) { $pointsFilter['to'] = (int) $pointsFilter['to']; $pointsFilterToCriterion = $c->getNewCriterion( UserPointsPeer::ID, '('.CurriculumPeer::TOTAL_POINTS.' - '.UserPointsPeer::POINTS_EARNED.') <= '.$pointsFilter['to'], Criteria::CUSTOM ); if (isset($criterion)) { $criterion->addAnd($pointsFilterToCriterion); } else { $criterion = $pointsFilterToCriterion; } } if (isset($criterion)) { $c->add($criterion); }
Note that in this example I’m typecasting our input parameters are integers, but you could mysql_real_escape_string() or any other sanitizing method you like.
You posted this five hours ago, just in time for my question about this. Thanks for the great post!
By James Skidmore on Jul 21, 2009
Thank you for the good explanation. But I have a problem with the addAsColumn functionality. I do not see this column in the result. Do I something wrong or it is not possible to make these column visible.
Maybe do you know an other way to run a custom db function in a way that the result will be visible.
Thanks
By Ronny Siegel on Mar 31, 2010
@Ronny, I am not sure what you mean by “visible”. You will still have to fetch your custom columns from your result object if you want to use them in your view. Maybe this post on custom queries with propel will help you.
By Scott Meves on Mar 31, 2010
Thank you for the link and sorry for the imprecise description. Now I know how I have to use the addAsColumn function. But the statement I add with the addAsColumn is not part of the Criteria result. I can only use it in the Criteria e.g. to add a condition. Is there a possibility to provide the values that are added via addAsColumn in the Criteria result object, e.g. with the addSelectColumn function? Thanks
By Ronny Siegel on Apr 8, 2010