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

July 21, 2009 – 5:06pm Propel criteria on custom columns (with addAsColumn)

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.

Posted by in  Uncategorized   |  

4 Responses to Propel criteria on custom columns (with addAsColumn)

  1. James Skidmore says:

    You posted this five hours ago, just in time for my question about this. Thanks for the great post!

  2. Ronny Siegel says:

    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

  3. Scott Meves says:

    @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.

  4. Ronny Siegel says:

    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