Write a Delete SQL query statement in Magento 2.

Write a MySQL delete query using Magento’s standard way of deleting specific rows from the database table by Magento.

You can write direct SQL query delete() without worrying about Model operation using a given code snippet in the blog.

/**
 * Deletes table rows based on a WHERE clause.
 *
 * @param  mixed $table The table to update.
 * @param  mixed $where DELETE WHERE clause(s).
 * @return int The number of affected rows.
 */
public function delete($table, $where = '');

Demo Code to delete table rows programmatically,

<?php
namespace Path\To\Class;

use Magento\Framework\App\ResourceConnection;

class DeleteQuery
{
    private const ORDER_TABLE = 'sales_order';
    private ResourceConnection $resourceConnection;

    public function __construct(ResourceConnection $resourceConnection)
    {
        $this->resourceConnection = $resourceConnection;
    }

    /** * Delete Sql Query */
    public function deleteQuery()
    {
        $connection = $this->resourceConnection->getConnection();
        $tableName = $connection->getTableName(self::ORDER_TABLE);
        $orderStatus = 'pending';
        $whereConditions = [$connection->quoteInto('status = ?', $orderStatus),];

        $connection->beginTransaction();
        try {
            $connection->delete($tableName, $whereConditions);
            $connection->commit();
        } catch (\Exception $e) {
            $connection->rollBack();
            throw $e;
        }
    }
}

Remove all the rows with Status equals Pending in the sales_order table.

We have taken a simple sales_order database table example to remove all the records that have status equals pending. Using the above code snippet, All the Pending statuses of order records will be removed from the table.

Check for other Direct Sql Query in Magento 2

Share this:

Related