Posts Tagged ‘Criteria’

Criteria::CUSTOM – the bane of my existence, until now…

Wednesday, April 22nd, 2009

A couple weeks ago I came across a query that I needed to write that would only retrieve the records that had the same date  as another table’s record.  Since I am using propel, I looked for a simple way to format the datetime field to just match the dates in the query so that only the appropriate records would be returned.  I googled for a long time, not reaching any solution.

I ended up with a stopgap measure to get me by until I could focus more time on the problem.  I iterated through the results and used php to compare the dates and drop the nonequal date records.  Today the problem came to a head where I could not move forward with my work until I solved the issue.  Since I’m using mysql and propel, I ended up creating a custom criteria using the mysql ‘DATE’ function.  I know this is suboptimal in that my query is now tied to mysql, but I really don’t expect to be changing databases soon, or ever.

Most of the solutions that I found online would not work with the DATE function for one reason or another (another = I couldn’t figure it out).  I finally found my solution in the symfony forums (can you believe it!?!?!) here.  For some reason google failed me on this one.  So out of the billions of pages online I had to find the one (1) that had my solution.  Luckily time_to_find_solution < losing_my_mind.  So the very simple solution is this:

$c->add(ActivityInfoPeer::ACTIVITY_EVENT_TIME, 'DATE('.ActivityInfoPeer::ACTIVITY_EVENT_TIME.')= DATE('.ActivitySeismoInfoPeer::ASI_EVENT_TIME.')', Criteria::CUSTOM);

Till Next Time

Tags: , , , , , , , ,
Posted in programming | No Comments »

adding OR conditional to a symfony query

Thursday, April 9th, 2009

I had to do some looking to find how to do an ‘or’ statement in symfony with propel.  I found out at a google’s group here and the api documentation from propel is here.  Once I found the information it was surprisingly easy.  Below is the code I ended up using to select records either updated in the last two weeks OR activities that are delayed AND activities that the posting times are NULL (implying they are not posted yet).

$c = new Criteria();
$c->AddDescendingOrderByColumn(ActivityInfoPeer::ACTIVITY_EVENT_TIME);
 
$criterion = $c->getNewCriterion(
ActivityInfoPeer::UPDATED_AT, date("Y-m-d H:i:s", time() - 1209600), Criteria::GREATER_THAN)->addOr(
     $c->getNewCriterion(ActivityInfoPeer::ACTIVITY_DELAYED, 1, Criteria::EQUAL)
     )->addAnd($c->getNewCriterion(ActivityInfoPeer::ACTIVITY_POST_TIME, null, Criteria::ISNULL)
     );
$c->add($criterion);
 
$this->pending_activities = ActivityInfoPeer::doSelect($c);

Till Next Time

Tags: , , , , ,
Posted in programming | No Comments »