title: Bind data to DB::raw() in Laravel
body: <p>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.</p>
  
  <p>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 <code>DB::raw()</code> 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 <a href="https://stackoverflow.com/questions/20864872/how-to-bind-parameters-to-a-raw-db-query-in-laravel-thats-used-on-a-model/20873009#20873009">this question</a>. 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.</p>
  
  <p>For example, using this query:</p>
  
  <pre><code>$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(*)'))
      -&gt;where('actual_date', '&gt;', $start_date)
      -&gt;where('actual_date', '&lt;', $end_date)
      -&gt;whereIn('link_id', $link_ids)
      -&gt;groupBy(DB::raw('DAY(actual_date)'))
      -&gt;groupBy(DB::raw('HOUR(actual_date)'))
      -&gt;orderBy('actual_date', 'asc')
      -&gt;setBindings([$start_date])
      -&gt;get();
  </code></pre>
  
  <p>Gives me this error:</p>
  
  <pre><code>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` &gt; ? and `actual_date` &lt; ? and `link_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) group by DAY(actual_date), HOUR(actual_date) order by `actual_date` asc)
  </code></pre>
  
  <p>Which seems straight forward enough, so I then tried:</p>
  
  <pre><code>$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(*)'))
      -&gt;where('actual_date', '&gt;', $start_date)
      -&gt;where('actual_date', '&lt;', $end_date)
      -&gt;whereIn('link_id', $link_ids)
      -&gt;groupBy(DB::raw('DAY(actual_date)'))
      -&gt;groupBy(DB::raw('HOUR(actual_date)'))
      -&gt;orderBy('actual_date', 'asc')
      -&gt;setBindings(array_merge([$start_date], $link_ids))
      -&gt;get();
  </code></pre>
  
  <p>And that gives me the error:</p>
  
  <pre><code>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` &gt; 1156 and `actual_date` &lt; 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)
  </code></pre>
  
  <p>So it seems that <code>setBindings</code> is just overriding the bindings that laravel is setting in the background. I did try filling them all in again with this:</p>
  
  <pre><code>$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(*)'))
      -&gt;where('actual_date', '&gt;', $start_date)
      -&gt;where('actual_date', '&lt;', $end_date)
      -&gt;whereIn('link_id', $link_ids)
      -&gt;groupBy(DB::raw('DAY(actual_date)'))
      -&gt;groupBy(DB::raw('HOUR(actual_date)'))
      -&gt;orderBy('actual_date', 'asc')
      -&gt;setBindings(array_merge([$start_date, $start_date, $end_date], $link_ids))
      -&gt;get();
  </code></pre>
  
  <p>and although is does not throw an error, it returns no data.</p>
  
  <p>So, my question: how can I use my <code>DB::raw</code> 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?</p>
  
  <p>Here is the original raw query for reference:</p>
  
  <pre><code>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 &gt; '2014-10-02 00:00:00'
    AND actual_date &lt; '2014-10-08 00:00:00'
  GROUP BY
    DAY(actual_date),
    HOUR(actual_date)
  ORDER BY
    actual_date
  </code></pre>