Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 42 ยท Data and the Database

Collections, Filters and SearchCriteria

Filter Magento collections with addFieldToFilter conditions, join tables, paginate, and build SearchCriteria with filter groups for AND and OR logic.

In Models, Resource Models and Collections you saw that a collection is a lazily built SELECT. This lesson is about the part you will write most often: telling the collection which rows you want. Magento gives you two languages for that. The first is the collection's own addFieldToFilter() family, close to SQL and tied to a concrete collection class. The second is SearchCriteria, an object that describes a query in a storage-independent way; it is what repositories, the REST API and GraphQL accept.

You will learn the condition syntax, how multiple filters combine into AND or OR, how to join another table, how pagination works and how filter groups in SearchCriteria map to SQL. All Magento code is read-only; the runnable stand-ins build a collection filter and a SearchCriteria builder on SQLite, with bound parameters, so you can see the generated SQL.

addFieldToFilter

The method takes a field and a condition. A bare value means "equals". An array with a condition key lets you pick the operator.

PHP
// read-only: $collection came from a collection factory
$collection->addFieldToFilter('status', 'pending');                    // status = 'pending'
$collection->addFieldToFilter('priority', ['gteq' => 3]);              // priority >= 3
$collection->addFieldToFilter('title', ['like' => '%refund%']);        // title LIKE '%refund%'
$collection->addFieldToFilter('status', ['in' => ['new', 'pending']]); // status IN (...)
$collection->addFieldToFilter('closed_at', ['null' => true]);          // closed_at IS NULL
$collection->addFieldToFilter('created_at', ['from' => '2030-01-01', 'to' => '2030-01-31']);

The most common condition keys are:

Key SQL
eq, neq =, <>
gt, gteq, lt, lteq >, >=, <, <=
like, nlike LIKE, NOT LIKE
in, nin IN (...), NOT IN (...)
null, notnull IS NULL, IS NOT NULL
from and to a range (either or both)
finset FIND_IN_SET for comma-separated columns

Values are passed to the database as bound parameters, so you do not escape them yourself. That is one of the reasons to use the collection API instead of hand-built SQL (Performance and Security). Do concatenate the % wildcards for like yourself, though, and escape any literal % or _ from user input if you want an exact match.

AND and OR

Each call to addFieldToFilter() is joined to the others with AND:

PHP
$collection->addFieldToFilter('status', 'pending')       // status = 'pending'
           ->addFieldToFilter('priority', ['gteq' => 3]); // AND priority >= 3

To get OR, pass arrays of fields and conditions in the same call. The conditions are OR'd with each other, and the whole group is AND'd with the rest.

PHP
$collection->addFieldToFilter(
    ['status', 'priority'],
    [
        ['eq' => 'escalated'],
        ['gteq' => 5],
    ]
);
// WHERE (status = 'escalated' OR priority >= 5)

The two arrays are matched by position, which is easy to get wrong. If you find yourself writing many OR conditions, consider whether SearchCriteria or a custom query is clearer.

flowchart TD
    Q["WHERE"] --> G1["Group 1<br/>status = 'pending'"]
    Q --> G2["Group 2 (OR inside)<br/>priority >= 5 OR title LIKE '%refund%'"]
    G1 -->|"AND"| G2

Separate filter calls (or SearchCriteria groups) are combined with AND; the entries inside one group are combined with OR.

Sorting, limiting and paging

PHP
$collection->setOrder('created_at', 'DESC')   // ORDER BY created_at DESC
           ->setPageSize(20)                   // LIMIT 20
           ->setCurPage(3);                    // OFFSET 40
$pages = $collection->getLastPageNumber();     // total pages, from a COUNT query
$total = $collection->getSize();               // total rows ignoring paging

setCurPage is 1-based. Page 3 with a page size of 20 skips 40 rows. Set a page size whenever there might be many rows: without it, the collection loads everything into memory.

Joining another table

Collections give the main table the alias main_table. You can use the underlying Select object to join. Select the columns you need from the joined table, and prefer a left join if some rows have no match.

PHP
// read-only
$collection->getSelect()->joinLeft(
    ['customer' => $collection->getTable('customer_entity')],
    'main_table.customer_id = customer.entity_id',
    ['customer_email' => 'email']
);
$collection->addFieldToFilter('customer.email', ['like' => '%@example.com']);

Two cautions. First, addFieldToFilter on a joined column may need the table alias or may need the field to be mapped (addFilterToMap), because the collection otherwise assumes the field belongs to the main table. Second, joins on EAV attributes, such as product names, do not work like this: for those, EAV collections have addAttributeToSelect and addAttributeToFilter, covered in EAV: Entity-Attribute-Value.

A filterable collection you can run

The stand-in below turns the same condition arrays into SQL with bound parameters. Separate calls are AND'd, array calls are OR groups, and nothing a user types ever lands in the SQL text, only in the parameter list. It supports a subset of the operators. Real Magento's version also handles field mapping, aliases and many edge cases.

PHP Stand-in: addFieldToFilter with AND, OR and bound values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The result is "pending AND (priority at least 3 OR title contains refund)", so rows 2 and 5 appear from the priority branch and row 1 appears from the title branch. The injection attempt matched nothing, because its text is just a value.

SearchCriteria

addFieldToFilter is convenient but tied to a collection. The service contract layer (Service Contracts and Repositories) needs a way to ask "give me these entities" without exposing collections, SQL or storage details. That is Magento\Framework\Api\SearchCriteriaInterface. The same structure is what the REST endpoint receives as searchCriteria[...] query parameters (The REST API).

A SearchCriteria contains:

  • filter groups, each a list of filters (field, value, condition type);
  • sort orders;
  • a page size and a current page.

You rarely build the object by hand. Inject SearchCriteriaBuilder and, for sorting and OR filters, FilterBuilder, FilterGroupBuilder and SortOrderBuilder:

PHP
// read-only
$criteria = $this->searchCriteriaBuilder
    ->addFilter('status', 'pending', 'eq')                      // group 1
    ->addFilters([                                               // group 2 (OR inside)
        $this->filterBuilder->setField('priority')->setValue(3)->setConditionType('gteq')->create(),
        $this->filterBuilder->setField('title')->setValue('%refund%')->setConditionType('like')->create(),
    ])
    ->addSortOrder($this->sortOrderBuilder->setField('id')->setDirection('ASC')->create())
    ->setPageSize(20)
    ->setCurrentPage(1)
    ->create();

$result = $this->noteRepository->getList($criteria);
$items = $result->getItems();
$total = $result->getTotalCount();

The logic is the one rule worth memorising: filters inside one group are combined with OR; separate groups are combined with AND. Calling addFilter() several times therefore gives an AND of single-filter groups. To express OR you use addFilters([...]) with an array, or build a FilterGroup yourself.

Inside the repository, a CollectionProcessorInterface (typically injected as CollectionProcessorInterface with the default composite processor) translates the criteria onto the collection, so each filter ends up as an addFieldToFilter call. getTotalCount() comes from the collection's getSize(), which is why paging does not hide the total.

The next stand-in is a small builder with the same group semantics. It compiles to SQL with bound values and returns both the items and the total count. Compare the two filter groups to the WHERE clause that results.

PHP Stand-in: SearchCriteria with filter groups

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The total is 3 while the page holds 2 rows. That separation, the same one getTotalCount() gives you in Magento, is what lets a client build a pager.

Common mistakes

  • Expecting two addFilter() calls to be OR. They are AND. Use addFilters([...]) for OR.
  • Forgetting the page size. An unbounded collection loads the whole table.
  • Filtering on a column that is not in the main table without a mapping or alias.
  • Using like without wildcards. ['like' => 'refund'] matches only the exact text.
  • Building SQL strings from request data. Use conditions and bound values.
  • Using SearchCriteria for everything. Inside a single module, a collection is shorter. Reach for SearchCriteria at module boundaries, in repositories and APIs.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does calling addFieldToFilter twice on one collection produce?

    Separate calls are combined with AND. To get OR, pass arrays of fields and conditions in a single call.
  2. 2. In SearchCriteria, how are filters combined?

    A filter group is an OR set. The groups are AND'd, so addFilter() in several calls forms several single-filter groups and gives AND.
  3. 3. Why does a repository's getList() return a total count as well as the items?

    The page holds at most the page size, while the total counts all matches. Clients need both to show page numbers.
  4. 4. Which page does setCurPage(3) with setPageSize(20) show?

    Pages are 1-based, so page 3 skips the first 40 rows and shows rows 41 to 60.
  5. 5. What is the safest way to filter by a value typed by a visitor?

    Conditions are executed with bound parameters, so the value is data and never SQL text.

Summary

  • addFieldToFilter(field, condition) takes a value or a condition array (eq, gteq, like, in, null, from/to and more); values are bound parameters.
  • Separate filter calls are AND; arrays of fields and conditions in one call are OR.
  • Page with setPageSize and setCurPage (1-based); use getSize() for the total.
  • Join with getSelect()->joinLeft(...) using the main_table alias; EAV attributes use their own filter methods.
  • SearchCriteria describes a query as filter groups (OR inside, AND between), sorts and paging; repositories and APIs accept it.
  • Use SearchCriteria at module boundaries and plain collections inside a module.

Next: Service Contracts and Repositories