3

I am trying to determine how to filter a Magento collection from a flat table that would produce a WHERE clause with an AND that is followed by expression with a nested OR, which has nested ANDs, such as:

SELECT * FROM `my_model_flat_table`
WHERE (`customer_id` = 123456) 
AND (
    ((`colA` = CONSTANT_A) AND (`colB` = $variableA))
    OR
    ((`colA` = CONSTANT_B) AND (`colB` = $variableB))
)

Short of doing this as two separate collections, loading and then merging them, I'm at a loss.

I'm trying to do this in a “Magento-friendly" way without resorting to:

$myCollection = Mage::getModel('mymodule/mymodel')
    ->getCollection()
    ->addFieldToFilter('customer_id',123456)
    ->getSelect()
    ->where(...put the nested logic in here...);

I've looked over the information at the following links, with no luck:

What I have done at this juncture is to go with a SQL SELECT, rather than using Magento's Collections, to retrieve the IDs of resources I need. From there I am loading the individual objects as needed:

$resource = Mage::getSingleton('core/resource');
$dbReader = $resource->getConnection('core_read');
$tableName = $resource->getTableName('mymodule/mymodel');
$query = "SELECT mymodel_id FROM {$tableName} WHERE customer_id = :cId AND 
    ((colA = :eiType AND colB = :eiId) OR (colA = :mcType AND colB = :mcId)) 
    ORDER BY mymodel_id DESC";
$bindings = array(
    'cId'    => $customerId,
    'eiType' => $foreignKeyCodeA,
    'eiId'   => $foreignKeyA,
    'mcType' => $foreignKeyCodeB,
    'mcId'   => $foreignKeyB,
);
$myModelIds = $dbReader->fetchCol($query,$bindings);

This is not what I wanted to do, but it does cut down on making multiple Collection loads to get to the data I need.

I also want to note that in the database colA and colB form a unique index, so the 'OR' in the SELECT statement will not result in data collisions.

migrated from stackoverflow.com Sep 9 '16 at 0:57

This question came from our site for professional and enthusiast programmers.

1

Use this

$ids = array(1001,1002,1003,1004,1005);

Mage::getModel('catalog/product')->getCollection()->getSelect()
        ->where('main_table.entitry_id IN (?)', $ids);

it will filter collection according to product ids which are coming in $ids array

  • This is the correct approach - use your direct SQL to fetch the ids as necessary and then use this to populate a product collection. – philwinkle Sep 9 '16 at 2:25
  • @karan-adhikari: You didn't answer the question I asked. You provided a way to get a product collection based on known IDs. For your example, there is even a much easier way: Mage::getModel('catalog/product')->getCollection()->addFieldToFilter('entity_id',['in' => [1001,1002,1003,1004,1005]]); – robenecker Sep 29 '16 at 14:26
1

Referencing how the core does this in Mage_Eav_Model_Resource_Entity_Attribute_Collection::addHasOptionsFilter(), I come up with the following:

public function addFilters($foreignKeyCodeA, $foreignKeyA, $foreignKeyCodeB, $foreignKeyB)
{
    $adapter = $this->getConnection();
    $andWhereA = implode(' AND ', array(
        $adapter->quoteInto('(main_table.colA = ?)', $foreignKeyCodeA),
        $adapter->quoteInto('(main_table.colB = ?)', $foreignKeyA)
    ));
    $andWhereB = implode(' AND ', array(
        $adapter->quoteInto('(main_table.colA = ?)', $foreignKeyCodeB),
        $adapter->quoteInto('(main_table.colB = ?)', $foreignKeyB)
    ));
    $orWhere = implode(' OR ', array($andWhereA, $andWhereB));

    $this->getSelect()
        ->where($orWhere);

    return $this;
}

If you add the above in the collection class, then you can

$myCollection = Mage::getModel('mymodule/mymodel')
        ->getCollection()
        ->addFieldToFilter('customer_id',123456)
        ->addFilters($foreignKeyCodeA, $foreignKeyA, $foreignKeyCodeB, $foreignKeyB)
;
  • 1
    Beware: this approach eats memory. To get around it, retrieve only the IDs with ->getAllIds(), which you can insert after ->addFilters(). – kiatng Sep 9 '16 at 3:40
  • Thanks @kiatng, I will modify a test copy of what I am working on to see if the nested arrays can work. I was thinking something like that would be the case, but could not puzzle it out. I also thing that what you would all need to be nested down in another AND. That is, your $orWhere would have to have an AND against $customerId. Thanks again for taking the to reply! – robenecker Sep 29 '16 at 14:06

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.