where clause in magento2

In general, MySQL utilizes the WHERE clause to extract records that meet specific criteria. Similarly, in Magento2, we can employ the WHERE clause to filter data based on certain criteria for data collection. Magento uses the methods addAttributeToFilter and addFieldToFilter for collecting data with specific criteria, such as greater than, equal, not equal, etc. Below are the descriptions of the two methods for collecting data:

You may also the Magento1 article: where clause in magento

addAttributeToFilter():

addAttributeToFilter is a function applicable to a EAV attribue collection in Magento, adding conditions to the WHERE part of the MySQL query to extract a EAV attribue. For instance:

$collection = $this->productCollectionFactory->create();
$collection->addFieldToSelect('*')
           ->addAttributeToFilter('entity_id', array('eq' => $productId));

To see the custom SQL query for the above code, you can print or log the $collection object. Use:

print_r((string)$collection->getSelect());

The generated SQL query will look similar to:

SELECT `e`.* FROM `catalog_product_entity` AS `e` WHERE (`e`.`entity_id` = '1')

addFieldToFilter():

addFieldToFilter() is generally used when working with a custom module and wanting to retrieve data with a WHERE clause in Magento. It’s similar to the addAttributeToFilter method but is specifically suitable for modules with simple columns, not attributes. For example:

$employeCollection = $this->employeeCollectionFactory->create();
$employeCollection->addFieldToSelect('*')
                 ->addFieldToFilter('entity_id', array('eq' => $id));

To see the custom SQL query, use:

print_r((string)$employeCollection->getSelect());

The resulting SQL query might look like:

SELECT `main_table`.* FROM `employee_details` AS `main_table` WHERE (`entity_id` = 1)

<?php
/**
 * @Module         : Jrb Demomodule
 * @Package        : Jrb_Demomodule
 * @Description    : Testing custom script output
 * @Developer      : Jyotiranjan Biswal<biswal@jyotiranjan.in>
 * @Copyright      : https://www.jyotiranjan.in/
 */

namespace Jrb\DemoModule\Model;

use Magento\Framework\Model\AbstractModel;

class ScriptWhereClause extends AbstractModel
{

    protected $logger;

    /**
     * CollectionFactory
     *
     * @var CollectionFactory
     */
    protected $productCollectionFactory;
    
    /**
     * employeeCollection
     *
     * @var \Jrb\CrudModel\Model\ResourceModel\Employee\Collection
     */
    protected $employeeCollectionFactory;

    /**
     * Constructor
     *
     * @param \Psr\Log\LoggerInterface $logger
     * @param \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
     * @param \Jrb\CrudModel\Model\ResourceModel\Employee\Collection $employeeCollectionFactory
     */
    public function __construct(
        \Psr\Log\LoggerInterface $logger,
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
        \Jrb\CrudModel\Model\ResourceModel\Employee\CollectionFactory $employeeCollectionFactory
    )
    {
        $this->logger = $logger;
        $this->productCollectionFactory = $productCollectionFactory;
        $this->employeeCollectionFactory = $employeeCollectionFactory;
    }

    /**
     * Execute the custom script
     *
     * @return void
     */
    public function execute()
    {
        $id = 1;
        $this->getAllRecords($id);
        $this->getAllRecordsFromCustomModule($id);
    }

    /**
     * @param int $id
     * 
     * @return array
     */
    public function getAllRecords($productId){
        $collection = $this->productCollectionFactory->create();
        $collection->addFieldToSelect('*')
        ->addAttributeToFilter('sku', array('like' => 'Nok%'));
        
        $this->logger->info((string)$collection->getSelect());
        print_r((string)$collection->getSelect());
        echo PHP_EOL;
    }

    public function getAllRecordsFromCustomModule($id){
        $employeCollection = $this->employeeCollectionFactory->create();
        $employeCollection->addFieldToSelect('*')
                    ->addFieldToFilter('entity_id', array('eq' => $id));
        print_r((string)$employeCollection->getSelect());
        echo PHP_EOL;
    }
}

These examples illustrate how addAttributeToFilter and addFieldToFilter are akin to WHERE clauses in Magento. Additionally, here are some operators commonly used in the WHERE clause for Magento.

Like – like

$collection->addAttributeToFilter('sku', array('like' => '24-MB%'));

Not Like – nlike

$collection->addAttributeToFilter('sku', array('nlike' => 'err-prod%'));

Equals: eq

$collection->addAttributeToFilter('status', array('eq' => 1));

Not Equals – neq

$collection->addAttributeToFilter('sku', array('neq' => '24-MB01'));

In – in

$collection->addAttributeToFilter('id', array('in' => array(116,114,112)));

Not In – nin

$collection->addAttributeToFilter('id', array('nin' => array(116,114,112)));

NULL – null

$collection->addAttributeToFilter('description', 'null');

Not NULL – notnull

$collection->addAttributeToFilter('description', 'notnull');

Greater Than – gt

$collection->addAttributeToFilter('id', array('gt' => 50));

Greater Than or Equals To- gteq

$collection->addAttributeToFilter('id', array('gteq' => 50));

Less Than – lt

$collection->addAttributeToFilter('id', array('lt' => 100));

Less Than or Equals To – lteq

$collection->addAttributeToFilter('id', array('lteq' => 100));

Conclusion

This tutorial provides a comprehensive understanding on how to use multiple operator in where clause of Mysql query in magento 2 that is where clause in magento2. If you have any questions, suggestions, or if something was inadvertently overlooked. I’ll promptly address your queries.

Keep sharing and liking for more insightful tutorials!