Collections
How to load many rows with a collection, filter and join safely, and when a repository is the right call instead.
$collection->addFieldToFilter(string|array $field, null|string|array $condition = null)A collection is a select over one table plus the models it will produce from the rows. It is lazy: the query runs on the first iteration, on getItems(), or on an explicit load(). Always obtain one from its generated CollectionFactory, because filters accumulate on an instance and a reused collection answers the wrong question.
Filtering
addFieldToFilter($field, $condition = null) adds a WHERE clause. addFieldToFilter('is_active', 1) is shorthand for addFieldToFilter('is_active', ['eq' => 1]) — the same clause, and the condition keys are the condition types you already know from SearchCriteria. The field is the SQL column name, not an interface getter.
Put the value inside the condition array so the adapter quotes it. getSelect()->where('title = ' . $term) is an injection; the bound form is getSelect()->where('title = ?', $term). addFieldToFilter is not automatically safe if you hand it a string you built yourself.
Columns and joins
addFieldToSelect(['question_id', 'title']) narrows the columns. The main table is aliased main_table, and $collection->getTable('vendor_faq_question_store') applies the configured table prefix — never hardcode a table name.
Collection or repository
Use the repository at module boundaries and anywhere another module may call: it is @api, and it returns data interfaces. Use a collection inside your own module when you need a join, an aggregate, or only two columns. A collection names columns, so it ties the caller to your schema.
getSize() runs a COUNT and ignores paging; count() loads every row first.
Example
<?php
// app/code/Vendor/Faq/Model/UnansweredQuestions.php
declare(strict_types=1);
namespace Vendor\Faq\Model;
use Vendor\Faq\Model\ResourceModel\Question\CollectionFactory;
class UnansweredQuestions
{
public function __construct(private readonly CollectionFactory $collectionFactory)
{
}
/**
* @return array<int, string>
*/
public function getTitlesForStore(int $storeId, string $term): array
{
$collection = $this->collectionFactory->create();
$collection->addFieldToSelect(['question_id', 'title']);
$collection->addFieldToFilter('is_active', 1); // same as ['eq' => 1]
$collection->addFieldToFilter('answer', ['null' => true]);
$collection->addFieldToFilter('title', ['like' => '%' . $term . '%']);
$collection->getSelect()->joinInner(
['store_rel' => $collection->getTable('vendor_faq_question_store')],
'store_rel.question_id = main_table.question_id',
[]
)->where('store_rel.store_id = ?', $storeId);
$collection->setOrder('sort_order', 'ASC');
$collection->setPageSize(20);
$titles = [];
foreach ($collection as $question) {
$titles[(int)$question->getId()] = (string)$question->getTitle();
}
return $titles;
}
}
// Output: [4 => 'How do I return an item?', 7 => 'Where is my order?']
// Two columns, one query, the store id bound as a parameter.When to use it
- Join a custom entity to its store table for a storefront list.
- Sum or group rows of your own table without loading models.
- Show a count of matching records in the admin without loading them.
More examples
Quoted, bound, and broken
addFieldToFilter protects the value, not the field. Hand it a string you assembled and it goes into the WHERE clause untouched, which is why reviewers look at every getSelect()->where() call without a placeholder. Use form 1 by default and form 2 when you need an expression; never build the condition text from input.
<?php
// Three ways to filter on a value that came from the request.
// 1. Condition array: the adapter quotes the value. Correct.
$collection->addFieldToFilter('title', ['like' => '%' . $term . '%']);
$collection->addFieldToFilter('question_id', ['in' => $ids]);
// 2. Raw select with a placeholder: the value is bound. Correct.
$collection->getSelect()->where('main_table.title = ?', $term);
// 3. Concatenated into the SQL string. SQL injection.
$collection->getSelect()->where('main_table.title = "' . $term . '"');
// And the same mistake wearing a collection method:
$collection->addFieldToFilter('title = "' . $term . '"');An aggregate a repository cannot express
This is the case for a collection over a repository. SearchCriteria has no vocabulary for GROUP BY or SUM, and hydrating every row into a model to add numbers in PHP would be slower by orders of magnitude. Because the result is rows rather than entities, fetchAll on the select is honest about what you are getting. Keep a query like this inside your own module and expose the answer through a small service class.
<?php
// app/code/Vendor/Faq/Model/VotesPerStore.php (fragment)
$collection = $this->collectionFactory->create();
$collection->getSelect()
->reset(\Magento\Framework\DB\Select::COLUMNS)
->columns([
'store_id' => 'store_rel.store_id',
'votes' => new \Zend_Db_Expr('SUM(main_table.votes)'),
])
->joinInner(
['store_rel' => $collection->getTable('vendor_faq_question_store')],
'store_rel.question_id = main_table.question_id',
[]
)
->group('store_rel.store_id');
$rows = $collection->getConnection()->fetchAll($collection->getSelect());
// Output: [['store_id' => '1', 'votes' => '184'], ['store_id' => '2', 'votes' => '31']]Counting without loading
Reach for getSize() for a total, a pager or an empty check, because it issues a COUNT and builds no models. count() and iteration load the page. The two disagree the moment paging is set, which is the point: getSize() answers how many match, count() answers how many you are holding.
<?php
// Two counts that look alike and cost very differently.
$collection = $this->collectionFactory->create();
$collection->addFieldToFilter('is_active', 1);
$collection->setPageSize(20);
$total = $collection->getSize(); // SELECT COUNT(*) ... ignores the page size
$loaded = count($collection); // loads the 20 rows as models, then counts them
// Output: $total is 4310, $loaded is 20.
Discussion