Basic Database Operations in Magento 2

Magento 2 recommends to use their wrapper functions for performing database operations. So let's have a quick look at some functions to accomplish basic tasks.

We can perform all the CRUD operations on the Magento 2 models, we can not perform CRUD operations over collections.

save(), load(), delete() and setData()

We can call save() and delete() methods on our model class to save and delete rows respectively. Before that we must ensure that data is loaded in the model.

$model->save(); // Save model data
$model->delete(); // Delete model data

To load data in model, we can use load() method. It requires primary key value as parameter for loading data to model. One row can be loaded at a time in model.

$model->load(1); 

Suppose we have entity_id column as primary key, so this will load the row with entity_id 1.

We can set data to model using setData() method. It accepts parameter as key-value pair and array.

$model->setData('column_name', 'value');

Differenece between setData() and addData()

Magento 2 models also uses magic getters and setters, you an refer to our blog - https://www.visheshdwivedi.com/post/how-magic-getters-and-setters-work-in-magento-2-orm

Let's have a look at what we can do with collection object.

addFieldToFIlter()

We can call this method on collection object. We can get collection object from model object using getCollection() method.

$collection = $this->model->getCollection();

Now, as we know, collection class contains table data. If we want to filter data on the basis of some conditions, we can use addFieldToFilter() method. It works exactly same as where clause in MySql.

$collection->addFieldToFilter('entity_id', ['eq' => 5]);

This will get the result where entity_id is equal to 5. We can use different conditions like neq, like, nlike, finset, in, nin, null, notnull, gt, lt, gteq, lteq according to our need.

addAttributeToSelect()

addAttributeToFilter()

getAllIds()

getColumnValues()

getSelect()

walk()

addFilterToMap()