0

I am trying to to achieve some thing like

SELCT * FROM xyz WHERE ( open_date >= $fromDate AND open_date<= $toDate = ) OR (closed_to_date >= $fromDate AND closed_to_date<= $toDate =)

What I am doing is,

$fromDate = Mage::getModel('core/date')->date("Y-m-d");
$toDate = date("Y-m-d", strtotime("+2 week"));

        $addressCollection->addAttributeToSelect('*')->addAttributeToFilter(
                array(
                    array('attribute' => 'open_date', 'gteq' => $fromDate),
                    array('attribute' => 'closed_to_date', 'gteq' => $fromDate),
                )
        );

        $addressCollection->addAttributeToSelect('*')->addAttributeToFilter(
                array(
                    array('attribute' => 'open_date', 'lteq' => $toDate),
                    array('attribute' => 'closed_to_date', 'lteq' => $toDate),
                )
        );

sql query for above magento query is

(string) SELECT `e`.*, `at_open_date`.`value` AS `open_date`, `at_closed_to_date`.`value` AS `closed_to_date` FROM `customer_address_entity` AS `e`
 INNER JOIN `customer_address_entity_datetime` AS `at_open_date` ON (`at_open_date`.`entity_id` = `e`.`entity_id`) AND (`at_open_date`.`attribute_id` = '553')
 INNER JOIN `customer_address_entity_datetime` AS `at_closed_to_date` ON (`at_closed_to_date`.`entity_id` = `e`.`entity_id`) AND (`at_closed_to_date`.`attribute_id` = '384') WHERE (`e`.`entity_type_id` = '2') AND ((at_open_date.value >= '2017-01-19 00:00:00') OR (at_closed_to_date.value >= '2017-01-19 00:00:00')) AND ((at_open_date.value <= '2017-02-02 00:00:00') OR (at_closed_to_date.value <= '2017-02-02 00:00:00'))

But what I want is

SELECT `e`.*, `at_open_date`.`value` AS `open_date`, `at_closed_to_date`.`value` AS `closed_to_date` FROM `customer_address_entity` AS `e`
 INNER JOIN `customer_address_entity_datetime` AS `at_open_date` ON (`at_open_date`.`entity_id` = `e`.`entity_id`) AND (`at_open_date`.`attribute_id` = '553')
 INNER JOIN `customer_address_entity_datetime` AS `at_closed_to_date` ON (`at_closed_to_date`.`entity_id` = `e`.`entity_id`) AND (`at_closed_to_date`.`attribute_id` = '384') WHERE (`e`.`entity_type_id` = '2') AND ((at_open_date.value >= '2017-01-19 00:00:00') AND (at_open_date.value <= '2017-02-02 00:00:00')) OR ((at_closed_to_date.value >= '2017-01-19 00:00:00') AND (at_closed_to_date.value <= '2017-02-02 00:00:00'))

For more clarification, want to change condition

From

AND ((at_open_date.value >= '2017-01-19 00:00:00') OR (at_closed_to_date.value >= '2017-01-19 00:00:00')) AND ((at_open_date.value <= '2017-02-02 00:00:00') OR (at_closed_to_date.value <= '2017-02-02 00:00:00'))

To

AND ((at_open_date.value >= '2017-01-19 00:00:00') AND (at_open_date.value <= '2017-02-02 00:00:00')) OR ((at_closed_to_date.value >= '2017-01-19 00:00:00') AND (at_closed_to_date.value <= '2017-02-02 00:00:00'))

Update:-1 Came with a solution, but not a magento way.

$addressCollection = Mage::getResourceModel('customer/address_collection');
$addressCollection1 = Mage::getResourceModel('customer/address_collection');

$addressCollection->addAttributeToFilter('open_date', array('gteq' => $fromDate));
$ids1 = $addressCollection->addAttributeToFilter('open_date', array('lteq' => $toDate))->getAllIds();

$addressCollection1->addAttributeToFilter('closed_to_date', array('gteq' => $fromDate));
$ids2 = $addressCollection1->addAttributeToFilter('closed_to_date', array('lteq' => $toDate))->getAllIds();

$result = array_merge($ids1, $ids2);
$final_result = array_unique($result);

Any suggestion to improve this?

1

Use it like this:

$addressCollection->addAttributeToSelect('*')
        ->addAttributeToFilter(
                    array('open_date', array('lteq' => $toDate))
        )
        ->addAttributeToFilter(
                    array('closed_to_date', array('gteq' => $fromDate))
        );
  • is it a valid query?. Because it is not working. – Abdul Ghaffar Jan 19 '17 at 7:55
  • this is an example. For AND condition, you need to write addfieldToFilter like above in my answer. – BornCoder Jan 19 '17 at 7:57
  • I know this, here query will be "where open_date <=$toDate AND closed_to_date <=$fromDate", but it is not working any way. – Abdul Ghaffar Jan 19 '17 at 8:00
  • fromDate also you want less than or greater than. What query this is returning because this should work for your condition – BornCoder Jan 19 '17 at 8:03
2
$addressCollection->addAttributeToSelect('*')
        ->addAttributeToFilter(
                    array('open_date', array('gteq' => $fromDate))
        )
        ->addAttributeToFilter(
                    array('closed_to_date', array('lteq' => $toDate))
        );
  • is it a valid query?. Because it is not working. – Abdul Ghaffar Jan 19 '17 at 7:55
  • Yes, if it is an attribute, you could use addAttributeToFilter else you can use addFieldToFilter – Chandresh P. Jan 19 '17 at 8:41

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.