Selecting date range when date is not zero using Propel
Using Propel I would like to find records that have a date field that is not null and also between a specific range.
NB Unfortunately, since this is part of a larger query, I cannot use a custom SQL query here.
For example: I might have entries like this:
---------------------
| ID | DUE_DATE |
---------------------
| 1 | NULL |
| 2 | 01/01/2010 |
| 3 | 02/01/2010 |
| 4 | NULL |
| 5 | 05/01/2010 |
---------------------
I can return all rows with due_date between 01/01/2010 and 02/01/2010, but I don’t want to return those entries where due_date is NULL.
In this example, I only want to return rows 2 and 3.
However, Propel seems to overwrite my NOTNULL criteria.
Can I do this with Propel?
Thanks!
a source to share
Why are you creating separate objects Criterion
?
$start_date = mktime(0, 0, 0, date("m") , date("d")+$start, date("Y"));
$end_date = mktime(0, 0, 0, date("m") , date("d")+$end, date("Y"));
$c = new Criteria();
$c->add(TaskPeer::DUE_DATE, $end_date, Criteria::LESS_EQUAL);
$c->addAnd(TaskPeer::DUE_DATE, $start_date, Criteria::GREATER_EQUAL);
$c->addAnd(TaskPeer::DUE_DATE, null, Criteria::ISNOTNULL);
When I try this in Propel 1.2, 1.3, or 1.4, I get the following SQL statement:
SELECT task.TASK_ID, task.DUE_DATE FROM task WHERE ((task.DUE_DATE<=:p1 AND task.DUE_DATE>=:p2) AND task.DUE_DATE IS NOT NULL )
The method $c->add()
replaces the current criterion for the given field. You create your criteria for TaskPeer::DUE_DATE
, so they will always replace the previous ones.
a source to share
I did not remove the section of zero entries, I think he will make: tasks.due_date IS NULL AND tasks.due_date IS NULL
.
Anyway, maybe you can use the Criteria::CUSTOM
WHERE-SQL-SQL clause to write? An example from the Propel documentation :
$con = Propel::getConnection(ReviewPeer::DATABASE_NAME);
$c = new Criteria();
$c->add(ReviewPeer::REVIEW_DATE, 'to_date('.ReviewPeer::REVIEW_DATE.', \'YYYY-MM-DD\') = '.$con->quote($date->format('Y-m-d'), Criteria::CUSTOM);
a source to share