Magento Collection addFieldToFilter – Complex AND/OR Query

collection;query

We have an events module on a Magento site we are developing which features event records which have start_date and end_date attributes.

On the events listing view you can view events by month. Each month view should show events which 1) start in that month, 2) end in that month or 3) start before AND end after that month (ie. last for the duration of the whole month).

My current Magento filter to get all events for the month is as follows (thanks to this answer https://stackoverflow.com/a/7851884/2071728). The variable $first_dayis the first day of the given month eg. 2014-06-01 and the variable $last_dayis the last day of the given month eg. 2014-06-30 or 2014-07-31.

    $collection = Mage::getModel('mymodule_events/details')->getCollection();
    $collection->addFieldToFilter( 'status', '1' );
    $collection->addFieldToFilter(
        array('start_date', 'end_date'),
        array(
            array('from'=>$first_day,'to'=>$last_day), 
            array('from'=>$first_day,'to'=>$last_day)
        )
    );

The query which results is as follows:

SELECT `main_table`.* FROM `mymodule_events_details` AS `main_table` 
WHERE (status = '1') 
   AND ((start_date >= '2014-06-01' AND start_date <= '2014-06-30') 
         OR (end_date >= '2014-06-01' AND end_date <= '2014-06-30'))

Points 1 and 2 above are working fine and most events are being retrieved, but the events which last the whole duration of the month are not being retrieved, except if you are viewing the month where they start or end.

If anyone could help with how I can alter my code to get the following query I would really appreciate it. I believe this is the resulting query I need (note the last OR):

SELECT `main_table`.* FROM `mymodule_events_details` AS `main_table` 
WHERE (status = '1') 
    AND ((start_date >= '2014-06-01' AND start_date <= '2014-06-30') 
          OR (end_date >= '2014-06-01' AND end_date <= '2014-06-30') 
          OR (start_date < '2014-06-01' AND end_date > '2014-06-30'))

Thanks in advance! I am using Magento CE 1.8.

Best Answer

You should not limit your start and end dates. Try this:

$collection = Mage::getModel('mymodule_events/details')->getCollection();
$collection->addFieldToFilter( 'status', '1' );
$collection->addFieldToFilter(
    'start_date',
    array(
        'lteq'=>$last_day
    )
);    
$collection->addFieldToFilter(
    'end_date',
    array(
        'gteq'=>$first_day
    )
);

So start_date should be lighter/equal $last_day and end_date should be greater/equal $first_day.

Related Topic