Using Custom Queries in Magento 2

sales gp - November 20, 2024 2

Using Custom Queries in Magento 2

To run a custom SQL query in Magento 2, you can take advantage of Magento’s ResourceConnection class, which provides a safe and efficient way to interact with the database. This approach allows you to write custom queries, retrieve results, and manipulate database tables, all while adhering to Magento’s architecture.

Table Of Content

Working with Custom Queries in Magento 2

Working with Custom Queries in Magento 2

In Magento 2, sometimes using a model is not sufficient to retrieve data from the database as per specific requirements. To handle these cases, you can execute custom queries. This guide will walk you through using SELECT, INSERT, UPDATE, DELETE, and JOIN custom queries in Magento 2.

Establishing a Database Connection

To execute custom queries, you first need to establish a connection to the database. Here's how you can do that:

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()->get('Magento\Framework\App\ResourceConnection');

$connection = $this->_resources->getConnection();

Insert Query

To insert data into a table, you can use the insert() method. Define the column values in an array and pass it to the query:

$array = ['column1' => 'value1', 'column2' => 'value2'];

$connection->insert($this->_resources->getTableName('test_table'), $array);

Select Query

For selecting data, use the select() method with the from() method to specify the table. Here's an example:

$select = $connection->select()->from(['o' => $this->_resources->getTableName('test_table')]);

$result = $connection->fetchAll($select);

foreach ($result as $data) {

echo $data['id']; // Output each record's ID

}

Update Query

Updating data is straightforward using a raw SQL UPDATE query:

$id = 1;

$sql = "UPDATE " . $this->_resources->getTableName('test_table') . " SET `title` = 'test title' WHERE `id` = $id";

$connection->query($sql);

Delete Query

To delete records, use the delete() method, passing in the table and condition:

$id = 1;

$condition = $connection->quoteInto('id = ?', $id);

$connection->delete($this->_resources->getTableName('test_table'), $condition);

Join Query

To join multiple tables, you can use the joinLeft() method. This example shows a LEFT JOIN between two tables:

$select = $connection->select()

->from(['o' => $this->_resources->getTableName('test_table')])

->joinLeft(

['test_table1' => $this->_resources->getTableName('test_table1')],

'o.id = test_table1.id',

['column1', 'column2'],

FAQs

How Do I Establish a Database Connection in Magento 2?

To execute custom queries in Magento 2, you first need to establish a database connection. Use the following code:

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()
    ->get('Magento\Framework\App\ResourceConnection');
$connection = $this->_resources->getConnection();

This creates a connection instance to interact with the database.

How Can I Insert Data Using a Custom Query?

To insert data into a table, use the insert() method. Here’s an example:

$array = ['column1' => 'value1', 'column2' => 'value2'];
$connection->insert($this->_resources->getTableName('test_table'), $array);

This inserts the specified values into the test_table.

How Do I Retrieve Data Using a Select Query?

Use the select() method to fetch data from a table:

$select = $connection->select()->from(['o' => $this->_resources->getTableName('test_table')]);
$result = $connection->fetchAll($select);

foreach ($result as $data) {
    echo $data['id'];
}

This retrieves all records from the table and iterates through them.

How Can I Update Data Using a Custom Query?

To update table data, you can use an SQL query:

$id = 1;
$sql = "UPDATE " . $this->_resources->getTableName('test_table') . 
       " SET `title` = 'new title' WHERE `id` = " . $id;
$connection->query($sql);

This updates the record with ID 1 in the test_table.

How Do I Delete Records Using a Custom Query?

To delete records from a table, use the delete() method with a condition:

$id = 1;
$condition = $connection->quoteInto('id = ?', $id);
$connection->delete($this->_resources->getTableName('test_table'), $condition);

This deletes the record with the specified ID.

How Can I Join Tables in a Custom Query?

To join tables, use the joinLeft() method:

$select = $connection->select()
    ->from(['o' => $this->_resources->getTableName('test_table')])
    ->joinLeft(
        ['t1' => $this->_resources->getTableName('test_table1')],
        'o.id = t1.id',
        ['column1', 'column2']
    );