Magento addFieldToFilter Two fields match as OR not AND

Question

I ve been stuck on this for the last few hours  I got it working by hacking a few lines in  lib Varien Data Collection Db php  but I d rather use the proper solution and leave my core untouched   All I need to do is get a collection and filter it by two or more fields  Say  customer firstname and remote ip  Here s my  disfunctional without hacking Db php  code    collection   Mage  getModel  sales order  - gt getCollection  - gt  addAttributeToSelect     - gt  addFieldToFilter array array  remote ip   array  eq   gt  127 0 0 1     array  customer firstname   array  eq   gt  gabe      array  eq   gt array 1 2 3       With a stock Db php  I tried this   sample taken from http   magentoexpert blogspot com 2009 12 retrieve-products-with-specific html    collection- gt addFieldToFilter array      array  name   gt  orig price   eq   gt  Widget A        array  name   gt  orig price   eq   gt  Widget B                     But that gives me this error   Warning  Illegal offset type in isset or empty  in magento lib Varien Data Collection Db php on line 369   If I wrap that with a try catch  then it moves into  getConditionSql   and gives this error   Warning  Invalid argument supplied for foreach    in magento lib Varien Data Collection Db php on line 412   Does anyone have any working  functional code for doing this  I m running Magento 1 9  Enterprise   Thanks

User · Answer

To filter by multiple attributes use something like:

//for AND
    $collection = Mage::getModel('sales/order')->getCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter('my_field1', 'my_value1')
    ->addFieldToFilter('my_field2', 'my_value2');

    echo $collection->getSelect()->__toString();

//for OR - please note 'attribute' is the key name and must remain the same, only replace //the value (my_field1, my_field2) with your attribute name


    $collection = Mage::getModel('sales/order')->getCollection()
        ->addAttributeToSelect('*')
        ->addFieldToFilter(
            array(
                array('attribute'=>'my_field1','eq'=>'my_value1'),
                array('attribute'=>'my_field2', 'eq'=>'my_value2')
            )
        );

For more information check: http://docs.magentocommerce.com/Varien/Varien_Data/Varien_Data_Collection_Db.html#_getConditionSql

User · Answer

I also tried to get the field1    a  OR field2    b   Your code didn t work for me   Here is my solution   results   Mage  getModel  xyz abc  - gt getCollection     results- gt addFieldToSelect  name     results- gt addFieldToSelect  keywords     results- gt addOrder  name   ASC     results- gt setPageSize 5     results- gt getSelect  - gt where  keywords like   foo   or additional keywords  like   bar        results- gt load     echo json encode  results- gt toArray       It gives me   SELECT name  keywords FROM abc WHERE keywords like   foo   OR additional keywords like   bar     It is maybe not the  magento s way  but I was stuck 5 hours on that   Hope it will help

User · Answer

public function testAction              filter a   array  like   gt  a              filter b   array  like   gt  b             echo           string           Mage  getModel  catalog product           - gt getCollection           - gt addFieldToFilter  sku  array  filter a  filter b           - gt getSelect                  Result   WHERE    e sku like  a    or  e sku like  b        Source  http   alanstorm com magento collections

User · Answer

Here is my solution in Enterprise 1 11  should work in CE 1 6         collection- gt addFieldToFilter  max item count                       array                          array  gteq    gt  10                           array  null    gt  true                                                   - gt addFieldToFilter  max item price                       array                          array  gteq    gt  9 99                           array  null    gt  true                                                   - gt addFieldToFilter  max item weight                       array                          array  gteq    gt  1 5                           array  null    gt  true                                          Which results in this SQL       SELECT  main table        FROM  shipping method entity  AS  main table      WHERE    max item count  gt   10  OR  max item count IS NULL          AND    max item price  gt   9 99  OR  max item price IS NULL          AND    max item weight  gt   1 5  OR  max item weight IS NULL

User · Answer

To create simple OR condition for collection  use format below        orders   Mage  getModel  sales order  - gt getResourceCollection         orders- gt addFieldToFilter         status         array           processing            pending                    This will produce SQL like this   WHERE     status     processing   OR   status     pending

User · Answer

This is the real magento way        collection Mage  getModel  sales order                   - gt getCollection                   - gt addFieldToFilter                          array                               customer firstname    attribute 1 with key 0                              remote ip    attribute 2 with key 1                                                    array                              array  eq   gt  gabe     condition for attribute 1 with key 0                             array  eq   gt  127 0 0 1     condition for attribute 2

User · Answer

There is a bit of confusion going on here  but let me try to clarify things   Lets say you wanted sql that looked something like   SELECT       main table           main table   email  AS  invitation email         main table   group id  AS  invitee group id   FROM       enterprise invitation  AS  main table   WHERE        status    new        OR  customer id    1234       In order to achieve this  your collection needs to be formatted like this    collection   Mage  getModel  enterprise invitation invitation  - gt getCollection      collection- gt addFieldToFilter array  status    customer id    array  array  status   eq   gt  new    array  customer id    eq   gt  1234         Now to see what this looks like you can always echo the query that this creates by using   echo  collection- gt getSelect  - gt   toString

User · Answer

I ve got another way to add an or condition in the field   - gt addFieldToFilter      array  title    content        array          array  like   gt    titlesearchtext              array  like   gt    contentsearchtext

User · Answer

Thanks Anda  your post has been a great help   However the OR sentence didnt  quite work for me and I was getting an error   getCollection    invalid argument supplied for foreach    So this is what I ended with  notice the attribute being specified 3 times instead of 2 in this case        collection- gt addFieldToFilter  attribute   array        array  attribute   gt  my field1   eq   gt  my value1                    array  attribute   gt  my field2   eq   gt  my value2         addFieldToFilter first requires a field and then condition  -  link

User · Answer

OR conditions can be generated like this    collection- gt addFieldToFilter      array  field 1    field 2    field 3       columns     array     conditions         array     conditions for field 1             array  in    gt  array  text 1    text 2    text 3                 array  like    gt    text                      array  eq    gt   exact       condition for field 2         array  in    gt  array  val 1    val 2       condition for field 3            This will generate an SQL WHERE condition something like       WHERE             field 1 IN   text 1    text 2    text 3   OR field 1 LIKE   text         OR  field 2    exact         OR  field 3 IN   val 1    val 2            Each nested array  lt condition gt   generates another set of parentheses for an OR condition

[php] Magento addFieldToFilter: Two fields, match as OR, not AND

Examples related to php

Examples related to magento

Examples related to magento-1.9