How to Write Select Query in Magento 2 with Standard way?

You can write a select query in Magento 2 to retrieve records from the table.

The select query can be written with best practices to avoid security breaches.

For example, You are searching for records from the core_config_data table,

Row query:

SELECT * FROM `core_config_data` WHERE `scope` = 'default' AND `path` = 'general/locale/code';

You can directly use the above string as a query and pass it to the query() method but it’s not a secure way to write a select query.

I will show you the standard Magento way to write database queries with the best secure standard Practice.

<?php
namespace Jesadiya\SelectQuery\Model\ResourceModel;

use Magento\Framework\App\ResourceConnection;

class Data
{
    private ResourceConnection $resourceConnection;

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

    /**
     * Select query to fetch records
     *
     * @return array
     */
    public function selectQuery()
    {
        $tableName = $this->resourceConnection->getTableName('core_config_data');

        //Initiate Connection
        $connection = $this->resourceConnection->getConnection();
        $path = 'general/locale/code';
        $scope = 'default';

        $select = $connection->select()
            ->from(
                ['c' => $tableName],
                ['*']
            )
            ->where(
                'c.path = :path'
            )->where(
                'c.scope = :scope'
            );
        $bind = ['path' => $path, 'scope' => $scope];

        return $connection->fetchAll($select, $bind);
    }
}

Above Query [‘*’] indicates fetching all the fields of the table. You can pass a specific field by comma-separated also if you want.

You need to pass each where condition separately in the query with the bind parameter.

->where(
    "c.path = :path"
)->where(
    "c.scope = :scope"
)
$bind = ['path'=>$path, 'scope'=>$scope];

Pass $path and $scope Value to the bind array instead of directly passing the value to the given where conditions.

An alternative of bind (Second Way)
If you don’t want to use $bind you can use an alternative way to pass the parameter in where conditions by the given way,

$select = $connection->select()
            ->from(
                ['c' => $tableName],
                ['*']
            )
            ->where(
                'c.path = ?', $path
            )->where(
                'c.scope = ?', $scope
            );
$records = $connection->fetchAll($select);

Output

Array
(
    [0] => Array
        (
            [config_id] => 3
            [scope] => default
            [scope_id] => 0
            [path] => general/locale/code
            [value] => en_US
            [updated_at] => 2023-10-30 06:27:49
        )

)

Share this:

Related