title: Add raw SQL to database query builder
body: <p>I have the following SQL which provides me with the position of a particular record in the database given specific filters and ordering.</p>
  
  
  
  <pre class="lang-php prettyprint-override"><code>\DB::select("SELECT
      t.id,
      t.position
    FROM (
      SELECT estimations.id,
      @rownum := @rownum + 1 AS position
      FROM estimations
      JOIN (SELECT @rownum := 0) r
      WHERE estimations.is_archived = 1
      ORDER BY estimations.start desc, estimations.name) t
    WHERE t.id = " . $this-&gt;id);
  </code></pre>
  
  <p>For the inner query, I want to use the eloquent model <code>Estimation</code> so that I can use its query scopes. I want to use the model so that if the scope is updated down the line, this query will reflect the change...</p>
  
  <pre class="lang-php prettyprint-override"><code>$innerQuery = Estimation::select('id')
      -&gt;addSelect(\DB::raw('@rownum := @rownum + 1 AS position'))
      // Need to add "JOIN (SELECT @rownum := 0) r" 
      -&gt;archived();
  </code></pre>
  
  <hr>
  
  <h3>Attempt 1 - failed</h3>
  
  <p>Use the <code>join</code> method and pass in some generic arguments...</p>
  
  <pre class="lang-php prettyprint-override"><code>$innerQuery = Estimation::select('id')
     -&gt;addSelect(\DB::raw('@rownum := @rownum + 1 AS position'))
     -&gt;join('(SELECT @rownum := 0) r', '1', '=', '1')
     -&gt;archived();
  </code></pre>
  
  <p>While the above gives me the correct SQL if I dump it, <code>$innerQuery-&gt;toSql()</code> it fails when I run it, <code>$innerQuery-&gt;get()</code> with the following error...</p>
  
  <blockquote>
    <p>SQLSTATE[42S02]: Base table or view not found: 1146 Table 'my_db.(select @rownum := 0) r' doesn't exist (SQL: SELECT temp.id, temp.position FROM (select `id`, @rownum := @rownum + 1 AS position from `estimations` inner join `(SELECT @rownum := 0) r` on `1` = `1` where `is_archived` = ? order by `start` desc, `name` asc) temp WHERE temp.id = 4)</p>
  </blockquote>
  
  <hr>
  
  <h3>Attempt 2 - failed</h3>
  
  <p>As above but using <code>DB::raw()</code> for the <code>join()</code> (suggested by Jonas Staudenmeir), </p>
  
  <pre class="lang-php prettyprint-override"><code>$innerQuery = Estimation::select('id')
     -&gt;addSelect(\DB::raw('@rownum := @rownum + 1 AS position'))
     -&gt;join(\DB::raw('(SELECT @rownum := 0) r'), '1', '=', '1')
     -&gt;archived();
  </code></pre>
  
  <p>Running <code>$innerQuery-&gt;get()</code> results in the following error...</p>
  
  <blockquote>
    <p>SQLSTATE[42S22]: Column not found: 1054 Unknown column '1' in 'on clause' (SQL: select `id`, @rownum := @rownum + 1 AS position from `estimations` inner join (SELECT @rownum := 0) r on `1` = `1` where `is_archived` = 1 order by `start` desc, `name` asc)</p>
  </blockquote>
  
  <hr>
  
  <h3>Attempt 3 - failed</h3>
  
  <p>As above, adding <code>DB::raw()</code> for all <code>join()</code> values (thanks again Jonas Staudenmeir), </p>
  
  <pre class="lang-php prettyprint-override"><code>$innerQuery = Estimation::select('id')
     -&gt;addSelect(\DB::raw('@rownum := @rownum + 1 AS position'))
     -&gt;join(\DB::raw('(SELECT @rownum := 0) r'), \DB::raw('1'), '=', \DB::raw('1'))
     -&gt;archived();
  </code></pre>
  
  <p>This works on its own <code>$innerQuery-&gt;get();</code>, but when inserted into the outer query, </p>
  
  <pre class="lang-php prettyprint-override"><code>$res = \DB::select('SELECT
      t.id,
      t.position
    FROM (' . $innerQuery-&gt;toSql() . ') t
    WHERE t.id = ' . $this-&gt;id);
  </code></pre>
  
  <p>I get the error...</p>
  
  <blockquote>
    <p>SQLSTATE[HY000]: General error: 2031 (SQL: SELECT t.id, t.position FROM (select `id`, @rownum := @rownum + 1 AS position from `estimations` inner join (SELECT @rownum := 0) r on 1 = 1 where `is_archived` = ? order by `start` desc, `name` asc) t WHERE t.id = 3)</p>
  </blockquote>
  
  <p>Is this because <code>scopeArchived()</code> filters by a value, which is shown as a <code>?</code> in the error? How can I avoid this?</p>
  
  <p>Here's the code for <code>scopeArchived()</code>, </p>
  
  <pre class="lang-php prettyprint-override"><code>public function scopeArchived($query)
  {
      return $query-&gt;where('is_archived', true)-&gt;ordered('start', 'desc');
  }
  </code></pre>
  
  <p>Removing <code>-&gt;where('is_archived', true)</code> results in no error, but obviously not the results I'm after.</p>
  
  <hr>
  
  <h3>Attempt 4 - solved</h3>
  
  <p>(Thanks Jonas Staudenmeir) - use <code>fromSub()</code> for the outer query. Here's the complete code...</p>
  
  <pre class="lang-php prettyprint-override"><code>$innerQuery = Estimation::select('id')
      -&gt;addSelect(\DB::raw('@rownum := @rownum + 1 AS position'))
      -&gt;join(\DB::raw('(SELECT @rownum := 0) r'), \DB::raw('1'), '=', \DB::raw('1'))
     -&gt;archived();
  
  $res = \DB::query()-&gt;fromSub($innerQuery, 'temp')
      -&gt;select('id', 'position')
      -&gt;where('id', $this-&gt;id)
      -&gt;first();
  </code></pre>