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 practice to avoid security breaches.

Example, You are searching for records from the core_config_data table,
Row query is:

SELECT * FROM core_config_data WHERE scope = ‘default’ AND path = ‘general/locale/code’

You can direct use above string as 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
{
    /**
     * @var ResourceConnection
     */
    private $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];
        $records = $connection->fetchAll($select, $bind);

        return $records;
    }
}

<?php

namespace Jesadiya\SelectQuery\Model\ResourceModel;

use Magento\Framework\App\ResourceConnection;

class Data

{

/**

* @var ResourceConnection

private $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];

$records = $connection->fetchAll($select, $bind);

return $records;

}

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

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

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

->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);

$select = $connection->select()

->from(

['c' => $tableName],

['*']

)

->where(

"c.path = ?", $path

)->where(

"c.scope = ?", $scope

);

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

Output

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

)

Array

(

[0] => Array

(

[config_id] => 6

[scope] => default

[scope_id] => 0

[path] => general/locale/code

[value] => en_US

[updated_at] => 2020-02-10 06:27:49

)

Share this:

Related