1

I have a predefined list of SKUs with prices in an array, and I would like to return the products within Magento that matches those SKUs but not the price. As a quick solution, I have constructed an SQL query that does this (attribute 77 is the product price):

SELECT product.sku FROM catalog_product_entity product
INNER JOIN catalog_product_entity_decimal price
ON product.entity_id = price.entity_id AND price.attribute_id = 77
WHERE (product.sku = 'FR050866' AND price.value != 132.2)
OR (product.sku = 'FR050869' AND price.value != 55.06)
OR (product.sku = 'FR230116' AND price.value != 210.93)
OR (product.sku = 'FS010048' AND price.value != 37.63)
OR (product.sku = 'FS010049' AND price.value != 13.44)

I then retrieved the matching SKUs as an array, and plugged it into the SearchCriteria:

// Fetch the SKUs as an array
$product_skus_to_amend = $connection->fetchCol($sql);

// Find the products by those SKUs
$this->_filterGroup->setFilters([
    $this->_filterBuilder
     ->setField('sku')
     ->setConditionType('in')
     ->setValue($product_skus_to_amend)
     ->create()
]);

$this->_searchCriteria->setFilterGroups([$this->_filterGroup]);
$products = $this->_productRepositiry->getList($this->_searchCriteria);

Is there a better method of doing this that doesn't rely on creating my own SQL query, thus potentially only requiring one database call, instead of the two using the method above? I've been trying to figure this out using the Magento\Framework\Api\SearchCriteriaInterface or Magento\Framework\Api\SearchCriteriaBuilder classes, but to no avail. Every guide I have found, which included Magento's Documentation, constructs queries like the below:

WHERE (x OR y) AND (a OR b)

However I can't find anything that constructs the below:

WHERE (x AND y) OR (a AND b)

Any help will be greatly appreciated!

1 Answer 1

0

You can use addAttributeToFilter for this and use array('nlike' =>

    $collection->addAttributeToSelect('sku');  
    $collection->addAttributeToSelect('price'); 
   // Get all SKUS
                $allowedSku = array(
                                        array("finset" => array('FR050866')),
                                        array("finset" => array('FR050869')),
                                        array("finset" => array('FR230116')),
                                        array("finset" => array('FS010048')),
                                        array("finset" => array('FS010049')),
                                      );    

                $notallowedPrice = array(
                                            array("finset" => array('77')),
                                            array("finset" => array('132.2)),
                                            array("finset" => array('55.06')),
                                            array("finset" => array('210.93')),
                                            array("finset" => array('37.63')),
                                            array("finset" => array('13.44')),
                                          ); 
    $collection->addAttributeToFilter(array(
                            array('attribute'=>'sku', 'finset'=>$allowedSku),
                            array('attribute'=>'price', array('nlike' => notallowedPrice),

    ));

Useful link: https://fishpig.co.uk/magento/tutorials/addattributetofilter/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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