Get Free Audit

Magento 2 Database. Group Operations

Feb 6, 2018

2210 Andrey Litvin

Magento 2 Database. Group Operations

The article is meant for Magento developers who are going to be familiar with Magento 2 and gain professional knowledge. It’s written in a clear and simple manner, but in case you face some difficulties, you’re welcome to comment on.

Creating Collection

Collections are Magento methods to provide the user with a simple interface for group operations on Magento entities. Collection objects allow the user to fetch model’s data from database with desired fields, joins, sorting and grouping. In order to create a simple collections class, we need to extend \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection class and override single-underscore _construct method with our own.

We need to pass our Model and Resource model class names to AbstractCollection’s _init method.

Group Operations


Collections provide a way to select only required fields from the database or create any join request to fetch related data. Let’s look at addFieldToSelect method from \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection class which can be used to tell collection what field it has to fetch from database.

If we ignore first two checks from this method, we’ll see that this method sets required field and its alias into class parameter _fieldsToSelect. This parameter is used later in our collection.

First, if block in this method checks for ‘*’ in our requested field and in this case sets _fieldsToSelect flag to null (collection will select all available fields) and the second if block checks if fields argument is an array and in this case recursively calls addFieldToSelect method for every value in passed array.

Parameter _fieldsToSelect and _fieldsToSelectChanged flag (which is set by addFieldToSelect method) are later used in extended getSelect Method. If any field has been changed, then \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection::_initSelectFields is called which passes our required columns to \Magento\Framework\DB\Select class as columns part.

Selecting custom columns
In order to add any custom query to our select query (like SUM or COUNT), we can additionally use \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection::addExpressionFieldToSelect method.


In order to filter entities in our collection, we can use \Magento\Framework\Data\Collection\AbstractDb::addFieldToFilter method. It requires field and condition.

Partner With Us Let's discuss how to grow your business. Get a Free Quote.
Talk to Andrey

For every field/condition pair that was passed, this method (if an array was passed) converts our conditions into simple SQL and passes them to \Magento\Framework\DB\Select class as WHERE part.


In order to sort our collection, we can use \Magento\Framework\Data\Collection\AbstractDb::setOrder, \Magento\Framework\Data\Collection\AbstractDb::addOrder or \Magento\Framework\Data\Collection\AbstractDb::unshiftOrder methods. All these methods pass our field and sort direction to private \Magento\Framework\Data\Collection\AbstractDb::_setOrder method.

This method sets our desired order into internal parameter _orders which is used later. In case of using unshiftOrder method, additional conditional block is used which sets passed order at first place into _orders array.


In order to load our collection, we can call \Magento\Framework\Data\Collection\AbstractDb::load method. This method checks if collection has been already loaded and if not, calls \Magento\Framework\Data\Collection\AbstractDb::loadWithFilter method.

First line of this method calls \Magento\Framework\Data\Collection\AbstractDb::_beforeLoad method. We can override this method in our custom collection if it requires some processing before accessing database. Then _render* methods are called. These methods convert our filter/order/etc. parameters from the array to SQL expressions and set them into \Magento\Framework\DB\Select object. By calling \Magento\Framework\Data\Collection\AbstractDb::printLogQuery method, we can print or log our combined SQL request. Actual data loading is done by calling \Magento\Framework\Data\Collection\AbstractDb::getData. For each row that we received from storage, engine collection creates instances of our Resource Model class and stores them into _items parameter after hydrating with data.


This is a very simple operation from the collection’s perspective.

Save method iterates through every item in collection and calls save method for each of them.

Andrey Dubina
Partner With Us Looking for a partner to grow your business? We are the right company to bring your webstore to success. Talk to Andrey

1 comment

Post a new comment

BelVG Newsletter
Subscribe to our mailing list and get interesting stuff and updates to your email inbox.
Email *