1

I want to filter a product collection with pairs of attributes match. For example get product collection which has (brand="Toyota" AND color="Red") OR (brand="Ford" AND color="Blue") OR (brand="BMW" AND color="White").

How can I do that with addAttributeToFilter or AddFieldToFilter?

2

Try this,

example :

$collection->addAttributeToFilter([['attribute'=>'brand','like'=>'%Toyota%'],['color'=>'status', 'eq'=>'Red']]);

Another thing to look at to achieve 'OR' is:

->addFieldToFilter(
     'brand',
     ['in' => ['simple', 'configurable']]
)
1
  • Thanks, but this does not work for my case
    – Hieu Duong
    Apr 19 at 1:33
0

Please study it deeply from this link.
https://meetanshi.com/blog/apply-or-conditions-to-collection-in-magento-2/

I hope the above link will definitely help you in this issue.
Adding another link that is similar and can help you in this topic.
https://stackoverflow.com/questions/3826474/magento-addfieldtofilter-two-fields-match-as-or-not-and
Vibhore

0

Look like addAttributeToFilter or addFieldToFilter will always insert the where clause as AND condition. I ended up building sql on my own:

use Magento\Catalog\Api\Data\ProductAttributeInterface;
use Magento\Catalog\Model\Product;
use Magento\Catalog\Model\ProductRepository;
use Magento\Eav\Model\Attribute;
use Magento\Eav\Model\AttributeRepository;
use Magento\Framework\Api\FilterBuilder;
use Magento\Framework\Api\SearchCriteriaBuilder;
use Magento\Framework\App\ResourceConnection;

class Helper {
    public function __construct(
        ProductRepository $productRepository,
        AttributeRepository $attributeRepository,
        SearchCriteriaBuilder $searchCriteriaBuilder,
        FilterBuilder $filterBuilder,
        ResourceConnection $resourceConnection
    ) {
        $this->productRepository = $productRepository;
        $this->attributeRepository = $attributeRepository;
        $this->searchCriteriaBuilder = $searchCriteriaBuilder;
        $this->filterBuilder = $filterBuilder;
        $this->resourceConnection = $resourceConnection;
    }

    /**
     * Get product array from set of multiple attribute values
     * Param structure:
     *      $attributes: array(
     *          $attributeCode1,
     *          $attributeCode2,
     *          ...
     *      )
     *      $valueSets: array(
     *          array(
     *              $attributeCode1 => $value1,
     *              $attributeCode2 => $value2,
     *              ...
     *          ),
     *          array(
     *              $attributeCode1 => $value3,
     *              $attributeCode2 => $value4,
     *              ...
     *          ),
     *      )
     *
     * @param array $attributes
     * @param array $valueSets
     *
     * @return Product[]
     */
    public function getProductsFromAttributeValues($attributes, $valueSets)
    {
        $attributeSearchResult = $this->attributeRepository->getList(
            ProductAttributeInterface::ENTITY_TYPE_CODE,
            $this->searchCriteriaBuilder->addFilters(
                [
                    $this->filterBuilder
                        ->setField('attribute_code')
                        ->setConditionType('in')
                        ->setValue($attributes)
                        ->create(),
                ]
            )->create()
        );


        $connection = $this->resourceConnection->getConnection();
        $select = $connection->select()
                             ->from(['e' => $connection->getTableName('catalog_product_entity')], 'entity_id');

        /** @var Attribute $item */
        foreach ($attributeSearchResult->getItems() as $item) {
            $attributeCode = $item->getAttributeCode();
            $attributeId = $item->getAttributeId();
            $select->joinInner(
                ["at_$attributeCode" => $connection->getTableName('catalog_product_entity_varchar')],
                "`at_$attributeCode`.`entity_id` = `e`.`entity_id` AND `at_$attributeCode`.`attribute_id` = '$attributeId'",
                null
            );
        }

        foreach ($valueSets as $valueSet) {
            $index = 0;
            foreach ($valueSet as $attributeCode => $value) {
                if ($index === 0) {
                    $select->orWhere("`at_$attributeCode`.`value` = ?", $value);
                } else {
                    $select->where("`at_$attributeCode`.`value` = ?", $value);
                }
                $index++;
            }
        }
        $products = [];
        foreach ($connection->fetchAll($select) as $data) {
            $products[] = $this->productRepository->getById($data['entity_id']);
        }

        return $products;
    }
}

Usage:

$helper->getProductsFromAttributeValues(
    ['brand', 'color'],
    [
        ['brand' => 'Toyota', 'color' => 'Red'],
        ['brand' => 'Ford', 'color' => 'Blue'],
        ['brand' => 'BMW', 'color' => 'White'],
    ]
)
0

Try this

$collection->addAttributeToSelect('*');
$collection->addAttributeToFilter([['attribute'=>'brand','eq'=>'Toyota'],['attribute'=>'color', 'eq'=>'Red']]);

$collection->addAttributeToFilter([['attribute'=>'brand','eq'=>'Ford'],['attribute'=>'color', 'eq'=>'Blue']]);

$collection->addAttributeToFilter([['attribute'=>'brand','eq'=>'BMW'],['attribute'=>'color', 'eq'=>'White']]);

Please print this collection also to string. Use this command for printing collections
echo $collection->getSelect()->__toString();

Edit: Adding another solution

$collection->getSelect()->where(
    "(brand='Toyota' AND color='Red') OR (brand='Ford' AND color='Blue') OR (brand='BMW' AND color='White')"
);

You can do more research here .. a person asked very similar question as you asked please have a look

https://magento.stackexchange.com/questions/139488/filter-category-collection-with-nested-or-and-statements

Thanks
Vibhore
5
  • Thanks, but unfortunately the result is opposite of what I want: (brand = 'Toyota' OR color = 'Red') AND (brand = 'Ford' OR color = 'Blue')
    – Hieu Duong
    Apr 19 at 1:33
  • May you please let me know the exact full output of this command : echo $collection->getSelect()->__toString(); it wll be helpful for me to drill deeper. Apr 19 at 4:17
  • Here it is: pastebin.com/raw/Wf0VwgXr
    – Hieu Duong
    Apr 19 at 4:24
  • I edited this answer please try after edit is written that solution. Apr 19 at 6:31
  • I'm sorry this will not work because color and brand are in different table and joined to product table during query time. Look at the raw SQL and you will see.
    – Hieu Duong
    Apr 19 at 6:45
0

Try this :

$collection->getSelect()->where(
    "(brand='Toyota' AND color='Red') OR (brand='Ford' AND color='Blue') OR (brand='BMW' AND color='White')"
);
0

In this sample function we are running sql queries directly those having joins please have a look, it may be helpful, this function is written in our block.

    public function getCatData()
     {
     $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
     $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
     $connection = $resource->getConnection();
     $category_id = $this->getRequest()->getParams();
     $category_id = $category_id['id'];
         $parent="l1_";
         $parentAttr="l2_";


         $l1Query="SELECT VALUE AS NAME FROM rpt_catalog_category_entity_varchar  WHERE attribute_id=45 AND entity_id=".$category_id;

         $l2Query="SELECT DISTINCT c.entity_id AS l2_entity_id,e.value AS l2_name,g.value AS l2_url FROM rpt_catalog_category_entity AS a INNER JOIN rpt_catalog_category_entity AS c ON a.entity_id=c.parent_id INNER JOIN rpt_catalog_category_entity_int AS d ON c.entity_id=d.entity_id LEFT JOIN rpt_catalog_category_entity_varchar AS e ON d.entity_id=e.entity_id AND e.attribute_id=45 INNER JOIN rpt_catalog_category_entity_varchar AS f ON d.entity_id=f.entity_id AND f.attribute_id=48 INNER JOIN rpt_catalog_category_entity_varchar AS g ON d.entity_id=g.entity_id AND g.attribute_id=119 WHERE a.entity_id=".$category_id." AND d.attribute_id=46 AND d.value=1 ORDER BY e.value";

         $l3Query="SELECT DISTINCT c.entity_id AS l2_entity_id,g.value AS l3_name,i.value AS l3_url FROM rpt_catalog_category_entity AS a INNER JOIN rpt_catalog_category_entity AS c ON a.entity_id=c.parent_id INNER JOIN rpt_catalog_category_entity_int AS d ON c.entity_id=d.entity_id INNER JOIN rpt_catalog_category_entity AS e ON c.entity_id=e.parent_id INNER JOIN rpt_catalog_category_entity_int AS f ON e.entity_id=f.entity_id INNER JOIN rpt_catalog_category_entity_varchar AS g ON f.entity_id=g.entity_id AND g.attribute_id=45 INNER JOIN rpt_catalog_category_entity_varchar AS i ON f.entity_id=i.entity_id AND i.attribute_id=119 WHERE a.entity_id=".$category_id." AND d.attribute_id=46 AND d.value=1 AND f.attribute_id=46 AND f.value=1 ORDER BY g.value";
         $l4Query="SELECT value AS description FROM rpt_catalog_category_entity_text WHERE attribute_id=47 AND entity_id=".$category_id;

        $l1Data=$connection->fetchAll($l1Query);
        $l2Data=$connection->fetchAll($l2Query);
        $l3Data=$connection->fetchAll($l3Query);
        $l4Data=$connection->fetchAll($l4Query);
        return array($l1Data,$l2Data,$l3Data,$l4Data);
     }

Please modify the above code as per your need.
Thanks
Vibhore

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.