Bind data to DB::raw() in Laravel

Question

I wrote a query in raw SQL that pulls all records in a given date range, counts the records based on the day and hour they were inserted, and calculates the hours since the "start date" they were inserted.

I'm now attempting to stuff that query into Laravel. The problem I ran into was that I needed a few things that I couldn't find in the Eloquent documentation, so I had to use DB::raw() to accomplish them. This worked fine with hard-coded values, but then I got to the point that I needed to use dynamic values, which led me to this question. This question almost worked, but the problem I ran into was that since I'm using a where clause with variables, it's causing some weird side effects.

For example, using this query:

$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(*)'))
    ->where('actual_date', '>', $start_date)
    ->where('actual_date', '<', $end_date)
    ->whereIn('link_id', $link_ids)
    ->groupBy(DB::raw('DAY(actual_date)'))
    ->groupBy(DB::raw('HOUR(actual_date)'))
    ->orderBy('actual_date', 'asc')
    ->setBindings([$start_date])
    ->get();

Gives me this error:

SQLSTATE[HY093]: Invalid parameter number (SQL: select TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(actual_date,'%Y-%m-%d %H:00:00'),'2014-10-02 00:00:00'))/60/60+1 as hours_since_send, COUNT(*) from `trackers` where `actual_date` > ? and `actual_date` < ? and `link_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) group by DAY(actual_date), HOUR(actual_date) order by `actual_date` asc)

Which seems straight forward enough, so I then tried:

$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(*)'))
    ->where('actual_date', '>', $start_date)
    ->where('actual_date', '<', $end_date)
    ->whereIn('link_id', $link_ids)
    ->groupBy(DB::raw('DAY(actual_date)'))
    ->groupBy(DB::raw('HOUR(actual_date)'))
    ->orderBy('actual_date', 'asc')
    ->setBindings(array_merge([$start_date], $link_ids))
    ->get();

And that gives me the error:

SQLSTATE[HY093]: Invalid parameter number (SQL: select TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(actual_date,'%Y-%m-%d %H:00:00'),'2014-10-02 00:00:00'))/60/60+1 as hours_since_send, COUNT(*) from `trackers` where `actual_date` > 1156 and `actual_date` < 1157 and `link_id` in (1158, 1159, 1160, 1161, 1162, 1163, 1164, 1165, 1166, 1167, 1168, 1169, 1170, 1171, ?, ?) group by DAY(actual_date), HOUR(actual_date) order by `actual_date` asc)

So it seems that setBindings is just overriding the bindings that laravel is setting in the background. I did try filling them all in again with 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(*)'))
    ->where('actual_date', '>', $start_date)
    ->where('actual_date', '<', $end_date)
    ->whereIn('link_id', $link_ids)
    ->groupBy(DB::raw('DAY(actual_date)'))
    ->groupBy(DB::raw('HOUR(actual_date)'))
    ->orderBy('actual_date', 'asc')
    ->setBindings(array_merge([$start_date, $start_date, $end_date], $link_ids))
    ->get();

and although is does not throw an error, it returns no data.

So, my question: how can I use my DB::raw expression with a bound parameter in a query like this without messing with the ones laravel sets in the background? Or how can I rewrite this query to accomplish what I'm needing?

Here is the original raw query for reference:

SELECT
  TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(actual_date,'%Y-%m-%d %H:00:00'),'2014-10-02 00:00:00'))/60/60+1 as hours_since_send,
  COUNT(*)
FROM
  trackers
WHERE
  link_id BETWEEN 1156 AND 1171
  AND
  actual_date > '2014-10-02 00:00:00'
  AND actual_date < '2014-10-08 00:00:00'
GROUP BY
  DAY(actual_date),
  HOUR(actual_date)
ORDER BY
  actual_date

Jarek Tkaczyk · Accepted Answer · 2014-10-08T14:24:12.103

3

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

edited Oct 08 '14 at 14:24

answered Oct 08 '14 at 09:45

Jarek Tkaczyk

78,987
25
159
157

How does this know where the binding should be added? – Samsquanch Oct 08 '14 at 14:12
Because it is explicitly specified - `select` – Jarek Tkaczyk Oct 08 '14 at 14:20
Gotcha. I added this and it did work after a minor tweak -- the quotes need to be removed from around the `?`. Thanks! – Samsquanch Oct 08 '14 at 14:23
Yes, there shouldn't be any quote around `?`. It was c/p from your example – Jarek Tkaczyk Oct 08 '14 at 14:24

score 1 · Answer 2 · answered Oct 07 '14 at 21:52

1

Eloquent actually has a whereBetween method that can take two Carbon instances (and normal timestamps, too) and compare them.

Tracker::whereBetween('actual_date', [$start, $end])->get();

http://laravel.com/docs/4.2/queries

answered Oct 07 '14 at 21:52

MattM

49
6

Well, that shortened the code by a line, but I'm still getting the `Invalid parameter number` error due to there being the same number of parameters passed in as before. – Samsquanch Oct 07 '14 at 21:54
Sorry, my original answer had the wrong parameters. Did you try with the updated one? String of the column, then an array with the range? – MattM Oct 07 '14 at 21:56
Yes. The problem is that `setBindings` is trying to set the parameters for all the parameters throughout the query. Changing two `where`'s to one `whereBetween` doesn't change the parameter count. – Samsquanch Oct 07 '14 at 21:58
1

A note: `whereBetween` is equal to `x >= 1 AND x <= 2`, in this case there are `<` and `>`, so it's not the same. – Jarek Tkaczyk Oct 08 '14 at 09:40
That was easy. Thanks alot! – Ruben Mar 27 '18 at 10:09

Bind data to DB::raw() in Laravel

2 Answers2