How to filter a database collection with the addFieldToFilter() method?

dmitrii_fediuk · April 14, 2016, 9:46am

Context: How to filter a database collection?

A base abstract method

magento/magento2/blob/072a1ae/lib/internal/Magento/Framework/Data/Collection.php#L171-L174


public function addFieldToFilter($field, $condition)
{
    throw new \Magento\Framework\Exception\LocalizedException(new \Magento\Framework\Phrase('Not implemented'));
}

Specification

Add field filter to collection

If $condition integer or string - exact value will be filtered (‘eq’ condition)

If $condition is array - one of the following structures is expected:

["from" => $fromValue, "to" => $toValue]
["eq" => $equalValue]
["neq" => $notEqualValue]
["like" => $likeValue]
["in" => [$inValues]]
["nin" => [$notInValues]]
["notnull" => $valueIsNotNull]
["null" => $valueIsNull]
["moreq" => $moreOrEqualValue]
["gt" => $greaterValue]
["lt" => $lessValue]
["gteq" => $greaterOrEqualValue]
["lteq" => $lessOrEqualValue]
["finset" => $valueInSet]

If non matched - sequential parallel arrays are expected and OR conditions
will be built using above mentioned structure.

Example:

$field = ['age', 'name'];
$condition = [42, ['like' => 'Mage']];

The above would find where age equal to 42 OR name like %Mage%.

\Magento\Framework\Data\Collection\AbstractDb::addFieldToFilter()

github.com

magento/magento2/blob/072a1ae/lib/internal/Magento/Framework/Data/Collection/AbstractDb.php#L380-L396


public function addFieldToFilter($field, $condition = null)
{
    if (is_array($field)) {
        $conditions = [];
        foreach ($field as $key => $value) {
            $conditions[] = $this->_translateCondition($value, isset($condition[$key]) ? $condition[$key] : null);
        }


        $resultCondition = '(' . implode(') ' . \Magento\Framework\DB\Select::SQL_OR . ' (', $conditions) . ')';
    } else {
        $resultCondition = $this->_translateCondition($field, $condition);
    }


    $this->_select->where($resultCondition, null, Select::TYPE_CONDITION);


    return $this;
}

Usage examples

1

github.com

magento/magento2/blob/072a1ae/app/code/Magento/AdminNotification/Model/ResourceModel/Inbox/Collection/Critical.php#L25-L43


protected function _initSelect()
{
    parent::_initSelect();
    $this->addOrder(
        'notification_id',
        self::SORT_ORDER_DESC
    )->addFieldToFilter(
        'is_read',
        ['neq' => 1]
    )->addFieldToFilter(
        'is_remove',
        ['neq' => 1]
    )->addFieldToFilter(
        'severity',
        \Magento\Framework\Notification\MessageInterface::SEVERITY_CRITICAL
    )->setPageSize(
        1
    );
    return $this;

2

github.com

magento/magento2/blob/072a1ae/app/code/Magento/AdminNotification/Model/ResourceModel/System/Message/Collection.php#L92-L102


/**
 * Set message severity filter
 *
 * @param int $severity
 * @return $this
 */
public function setSeverity($severity)
{
    $this->addFieldToFilter('severity', ['eq' => $severity * 1]);
    return $this;
}

3

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Authorization/Model/ResourceModel/Role/Collection.php#L25-L37


/**
 * Add user filter
 *
 * @param int $userId
 * @param string $userType
 * @return $this
 */
public function setUserFilter($userId, $userType)
{
    $this->addFieldToFilter('user_id', $userId);
    $this->addFieldToFilter('user_type', $userType);
    return $this;
}

4

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Authorization/Model/ResourceModel/Role/Collection.php#L39-L48


/**
 * Set roles filter
 *
 * @return $this
 */
public function setRolesFilter()
{
    $this->addFieldToFilter('role_type', RoleGroup::ROLE_TYPE);
    return $this;
}

5

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Authorization/Model/ResourceModel/Role/Grid/Collection.php#L15-L25


/**
 * Prepare select for load
 *
 * @return $this
 */
protected function _initSelect()
{
    parent::_initSelect();
    $this->addFieldToFilter('role_type', RoleGroup::ROLE_TYPE);
    return $this;
}

6

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Authorization/Model/ResourceModel/Rules/Collection.php#L25-L35


/**
 * Get rules by role id
 *
 * @param int $roleId
 * @return $this
 */
public function getByRoles($roleId)
{
    $this->addFieldToFilter('role_id', (int)$roleId);
    return $this;
}

7

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Authorization/Setup/InstallData.php#L42-L48


$roleCollection = $this->authFactory->createRoleCollection()
    ->addFieldToFilter('parent_id', 0)
    ->addFieldToFilter('tree_level', 1)
    ->addFieldToFilter('role_type', RoleGroup::ROLE_TYPE)
    ->addFieldToFilter('user_id', 0)
    ->addFieldToFilter('user_type', UserContextInterface::USER_TYPE_ADMIN)
    ->addFieldToFilter('role_name', 'Administrators');

8

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Authorization/Setup/InstallData.php#L69-L71


$rulesCollection = $this->authFactory->createRulesCollection()
    ->addFieldToFilter('role_id', $admGroupRole->getId())
    ->addFieldToFilter('resource_id', 'all');

9

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Backend/Block/Dashboard/Sales.php#L60-L68


if ($this->getRequest()->getParam('store')) {
    $collection->addFieldToFilter('store_id', $this->getRequest()->getParam('store'));
} elseif ($this->getRequest()->getParam('website')) {
    $storeIds = $this->_storeManager->getWebsite($this->getRequest()->getParam('website'))->getStoreIds();
    $collection->addFieldToFilter('store_id', ['in' => $storeIds]);
} elseif ($this->getRequest()->getParam('group')) {
    $storeIds = $this->_storeManager->getGroup($this->getRequest()->getParam('group'))->getStoreIds();
    $collection->addFieldToFilter('store_id', ['in' => $storeIds]);
}

10

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Backend/Block/Dashboard/Totals.php#L67-L84


if ($this->getRequest()->getParam('store')) {
    $collection->addFieldToFilter('store_id', $this->getRequest()->getParam('store'));
} else {
    if ($this->getRequest()->getParam('website')) {
        $storeIds = $this->_storeManager->getWebsite($this->getRequest()->getParam('website'))->getStoreIds();
        $collection->addFieldToFilter('store_id', ['in' => $storeIds]);
    } else {
        if ($this->getRequest()->getParam('group')) {
            $storeIds = $this->_storeManager->getGroup($this->getRequest()->getParam('group'))->getStoreIds();
            $collection->addFieldToFilter('store_id', ['in' => $storeIds]);
        } elseif (!$collection->isLive()) {
            $collection->addFieldToFilter(
                'store_id',
                ['eq' => $this->_storeManager->getStore(\Magento\Store\Model\Store::ADMIN_CODE)->getId()]
            );
        }
    }
}

11

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Backend/Helper/Dashboard/Order.php#L61-L74


if ($this->getParam('store')) {
    $this->_collection->addFieldToFilter('store_id', $this->getParam('store'));
} elseif ($this->getParam('website')) {
    $storeIds = $this->getStoreManager()->getWebsite($this->getParam('website'))->getStoreIds();
    $this->_collection->addFieldToFilter('store_id', ['in' => implode(',', $storeIds)]);
} elseif ($this->getParam('group')) {
    $storeIds = $this->getStoreManager()->getGroup($this->getParam('group'))->getStoreIds();
    $this->_collection->addFieldToFilter('store_id', ['in' => implode(',', $storeIds)]);
} elseif (!$this->_collection->isLive()) {
    $this->_collection->addFieldToFilter(
        'store_id',
        ['eq' => $this->getStoreManager()->getStore(\Magento\Store\Model\Store::ADMIN_CODE)->getId()]
    );
}

12

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Braintree/Helper/Country.php#L42-L56


/**
 * Returns countries array
 *
 * @return array
 */
public function getCountries()
{
    if (!$this->countries) {
        $this->countries = $this->collectionFactory->create()
            ->addFieldToFilter('country_id', ['nin' => $this->countryConfig->getExcludedCountries()])
            ->loadData()
            ->toOptionArray(false);
    }
    return $this->countries;
}

13

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Braintree/Model/Adminhtml/System/Config/Country.php#L68-L71


$this->options = $this->countryCollection
    ->addFieldToFilter('country_id', ['nin' => $this->getExcludedCountries()])
    ->loadData()
    ->toOptionArray(false);

14

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Bundle/Model/ResourceModel/Option/Collection.php#L170-L184


/**
 * Sets filter by option id
 *
 * @param array|int $ids
 * @return $this
 */
public function setIdFilter($ids)
{
    if (is_array($ids)) {
        $this->addFieldToFilter('main_table.option_id', ['in' => $ids]);
    } elseif ($ids != '') {
        $this->addFieldToFilter('main_table.option_id', $ids);
    }
    return $this;
}

15

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Bundle/Observer/AppendUpsellProductsObserver.php#L110-L116


$bundleCollection->addFieldToFilter(
    'entity_id',
    ['in' => $bundleIds]
)->setFlag(
    'do_not_use_category_id',
    true
);

16

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Block/Adminhtml/Category/Tab/Product.php#L82-L86


if ($column->getFilter()->getValue()) {
    $this->getCollection()->addFieldToFilter('entity_id', ['in' => $productIds]);
} elseif (!empty($productIds)) {
    $this->getCollection()->addFieldToFilter('entity_id', ['nin' => $productIds]);
}

17

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Block/Adminhtml/Category/Tab/Product.php#L124-L124


$this->getCollection()->addFieldToFilter('entity_id', ['in' => $productIds]);

18

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Block/Adminhtml/Product/Edit/Tab/Crosssell.php#L139-L145


if ($column->getFilter()->getValue()) {
    $this->getCollection()->addFieldToFilter('entity_id', ['in' => $productIds]);
} else {
    if ($productIds) {
        $this->getCollection()->addFieldToFilter('entity_id', ['nin' => $productIds]);
    }
}

19

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Block/Adminhtml/Product/Edit/Tab/Crosssell.php#L171-L171


$collection->addFieldToFilter('entity_id', ['in' => $productIds]);

20

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Block/Adminhtml/Product/Edit/Tab/Related.php#L135-L141


if ($column->getFilter()->getValue()) {
    $this->getCollection()->addFieldToFilter('entity_id', ['in' => $productIds]);
} else {
    if ($productIds) {
        $this->getCollection()->addFieldToFilter('entity_id', ['nin' => $productIds]);
    }
}

21

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Block/Adminhtml/Product/Edit/Tab/Related.php#L166-L166


$collection->addFieldToFilter('entity_id', ['in' => $productIds]);

22

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Block/Adminhtml/Product/Edit/Tab/Attributes/Search.php#L92-L96


/** @var $collection \Magento\Catalog\Model\ResourceModel\Product\Attribute\Collection */
$collection = $this->_collectionFactory->create()->addFieldToFilter(
    'frontend_label',
    ['like' => $escapedLabelPart]
);

23

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Block/Product/TemplateSelector.php#L69-L83


$collection->setEntityTypeFilter(
    $entityType->getId()
)->addFieldToFilter(
    'attribute_set_name',
    ['like' => $labelPart]
)->addFieldToSelect(
    'attribute_set_id',
    'id'
)->addFieldToSelect(
    'attribute_set_name',
    'label'
)->setOrder(
    'attribute_set_name',
    \Magento\Eav\Model\ResourceModel\Entity\Attribute\Set\Collection::SORT_ORDER_ASC
);

24

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Controller/Adminhtml/Product/Attribute/Save.php#L224-L228


$groupCollection = $this->groupCollectionFactory->create()
    ->setAttributeSetFilter($attributeSetId)
    ->addFieldToFilter('attribute_group_code', $groupCode)
    ->setPageSize(1)
    ->load();

25

github.com

magento/magento2/blob/072a1ae/app/code/Magento/Catalog/Helper/Product/Edit/Action/Attribute.php#L174-L176


if ($this->_excludedAttributes) {
    $this->_attributes->addFieldToFilter('attribute_code', ['nin' => $this->_excludedAttributes]);
}

dmitrii_fediuk · April 14, 2016, 10:13am