'Is there a way to use the same parameter into multiple place in the same query with Eloquent? [duplicate]

I am working on a Laravel 5.7 based project in which Eloquent is used as an ORM.

I need to execute a raw SQL statement on my database. However, my query uses the same parameter into multiple places.

Here is a query example "this is not my real query it is more for explaining the question. My actual query is very complex and reuses the same parameters multiple times"

SELECT * FROM table
WHERE a BETWEEN :from AND :to
AND c > :from
AND d < :to

I am expecting the following to work

$resorces = DB::select('SELECT * FROM table
    WHERE a BETWEEN :from AND :to
    AND c > :from
    AND d < :to', ['from' => '2017-01-01 00:00:00', 'to' => '2018-10-01 00:00:00']);

But that is giving me the following error

SQLSTATE[HY093]: Invalid parameter number

How can I re-use the same parameters multiple places in the same query using Eloquent?

phplaravellaravel-5pdoeloquent


Solution 1:[1]

Would something like this work for you?

$from = '2017-01-01 00:00:00';
$to = '2018-10-01 00:00:00';
$resorces = DB::select('SELECT * FROM table
    WHERE a BETWEEN ? AND ?
    AND c > ?
    AND d < ?', [$from, $to, $from, $to]);

Solution 2:[2]

How about this?

$from = '2017-01-01 00:00:00';
$to = '2018-10-01 00:00:00';
$resorces = DB::table('table')
    ->whereRaw('a BETWEEN ? AND ?', [$from, $to])
    ->whereRaw('c > ?', [$from])
    ->whereRaw('d < ?', [$to])
    ->get();

Solution 3:[3]

Create a Dynamic Query Scopes in your Model.

public function scopeQUERY ($query, $value1, $value2){

      $query = DB::(YOUR QUERY);

      return $query;
}

Your Controller

public function hello(Model name $Modelname){

         $query = $Modelname->QUERY($value1, $value2);

}

Documentation https://laravel.com/docs/5.7/eloquent#query-scopes

Solution 4:[4]

Recently i figured out how to do what you want.

I got the answer here.

You need to add the option ATTR_EMULATE_PREPARES to your database connection, just like the following:

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_EMULATE_PREPARES => true,
            ]) : [],
        ],

That works fine for me. Please let em know if it works for you ?

Bests

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 matt
Solution 2 krisanalfa
Solution 3 Matteo
Solution 4 Gabriel

Related Questions