2

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.

5

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.

  • Thank you! This worked a charm. I can see how I was overcomplicating things in my logic! – Sarah Jun 10 '14 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.