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: Criteria, Criteria::CUSTOM, custom, date, date function, mysql, php, propel, symfony