How to Bind Parameters to a Raw Db Query in Laravel That's Used on a Model

How to bind parameters to a raw DB query in Laravel that's used on a model?

OK, after some experimenting, here's the solution that I came up with:

$property = 
    Property::select(
        DB::raw("title, lat, lng, ( 
            3959 * acos( 
                cos( radians(  ?  ) ) *
                cos( radians( lat ) ) * 
                cos( radians( lng ) - radians(?) ) + 
                sin( radians(  ?  ) ) *
                sin( radians( lat ) ) 
            )
       ) AS distance")
    )
    ->having("distance", "<", "?")
    ->orderBy("distance")
    ->take(20)
    ->setBindings([$lat, $lng, $lat,  $radius])
    ->get();

Basically, setBindings has to be called on the query. Wish this was documented!

Binding parameter to Db::raw laravel query

Try this:

$results = DB::select('SELECT HOUR(created_at) as hour, COUNT(*) as count FROM visited WHERE created_at >= DATE_SUB(NOW(),INTERVAL ? DAY) GROUP BY HOUR(created_at)', [16]);

You can even use named bindings:

$results = DB::select('SELECT HOUR(created_at) as hour, COUNT(*) as count FROM visited WHERE created_at >= DATE_SUB(NOW(),INTERVAL :days DAY) GROUP BY HOUR(created_at)', ['days' => 16]);

Don't need to use DB::raw(), just use DB::select() for simple raw select queries: https://laravel.com/docs/master/database#running-queries

In Laravel, how do you bind parameters to a query in query builder DB raw inside another query?

You could try merging the bindings from the first query into the second query before executing it:

$flavor->mergeBindings($color);

Or just the binding without the 'type' of the bindings:

$flavor->addBinding($color->getBindings());

The second one should work because you only have a binding for a where which is the default type for addBinding. For more advanced queries with other bindings you probably want to go the way of merging the bindings so the 'type' of the binding is accurate.

Bind data to DB::raw() in Laravel

Don't use setBindings, for you don't need to override all the where bindings (and that's what you did), simply do this:

$clicks = Tracker::select(DB::raw("TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(actual_date,'%Y-%m-%d %H:00:00'),?))/60/60+1 as hours_since_send"), DB::raw('COUNT(*)'))
    ->addBinding($start_date, 'select')
    ->where('actual_date', '>', $start_date)
    ...

Laravel binding parameter using insert() with convert() inside

Based on your last error message, when you are trying the full raw query:

SQLSTATE[22001]: [Microsoft][ODBC Driver 13 for SQL Server][SQL
Server]String or binary data would be truncated. (SQL: INSERT INTO
[dbo].[tbl_rfaccount] ([id] ,[password] ,[accounttype] ,[birthdate]
,[BCodeTU] ,[Email]) VALUES ((CONVERT(binary, user01)),
(CONVERT(binary, password01)), 0, 2011-11-11 00:00:00, 1,
user@example.com)

You need to specify the length of fields in CONVERT function.

Not CONVERT(binary, user01), but CONVERT(binary(16), user01). Specify the same length as your column is defined in the target table.

If you do not specify the length, then in some cases it is assumed to be 1 and in some 30.

Aaron Bertrand wrote a detailed article about this (and other) bad habits:
Bad habits to kick : declaring VARCHAR without (length). varchar or binary or varbinary is similar here.

As @Zhorov correctly pointed out in the comment the CONVERT function assumes that length is 30 if it is not specified.

-- CONVERT Syntax:  
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

...

length

An optional integer that specifies the length of the target
data type, for data types that allow a user specified length. The
default value is 30.

Here is a simple example that demonstrates what is going on:

SELECT
    CONVERT(binary(16), '1234567890123456') AS Bin16
    ,CONVERT(binary, '1234567890123456') as BinNoLength
;

The result:

+------------------------------------+----------------------------------------------------------------+
| Bin16                              | BinNoLength                                                    |
+------------------------------------+----------------------------------------------------------------+
| 0x31323334353637383930313233343536 | 0x313233343536373839303132333435360000000000000000000000000000 |
+------------------------------------+----------------------------------------------------------------+

So, when you don't specify the length in CONVERT, you'll get the binary(30) result.

And when you try to insert this long value into the column in your table, your column is not long enough to store it, so the long value is truncated and you see this error message.

---

Related Topics