title: In Laravel , how do I number the rows in a subquery using Query Builder (or Eloquent)?
body: <p>I'm hoping that my explanation is clear about what I'm trying to do and someone can guide me on this.
  I'm trying to line up two sets of players (users), who have drawn different colors, opposite each other on the same table row. The players who will occupy the same row have no common field value to join them except their row number from their subqueries, the order in which they happened to draw, which is random (so id doesn't work). So the first player to draw green is joined on the same row as the first to draw yellow, and so on. Each subquery joins the players table and the draws table, and the outer query joins the two subs by the player's draw order within their subquery.</p>
  
  <p>EDIT:</p>
  
  <pre><code>Tables:
  players: id, name (simplified user);
  draws: id, player_id, color
  </code></pre>
  
  <p>Desired result (greatly simplified):</p>
  
  <pre><code>    Green     |Row|         Yellow
  Name   Draw             Name     Draw
  Joe   green     1       Ed      yellow
  Mary  green     2       John    yellow
  Sam   green     3       Clara   yellow
  </code></pre>
  
  <p>I was able to produce the table last year experimenting with php and sql, before I even heard of Laravel (or even OOP). I can also create it concatenating pure sql (below), but I can't paginate that (unless someone can show me how). So now I want to use Query Builder or Eloquent (esp. so I can paginate). I've made an attempt with Query Builder (below). But the row number attempts in the subqueries either cause errors or just don't work, and that prevents me setting the subquery rows equal in the outer query join ON clause. The subquery row numbers don't need to be displayed, they're just for lining up the rows.
  Numbering the outer query rows in the view shouldn’t be a problem using paginate: (thanks to @panjeh <a href="https://medium.com/@panjeh/how-to-increment-row-number-with-laravel-pagination-4953348b7103" rel="nofollow noreferrer">https://medium.com/@panjeh/how-to-increment-row-number-with-laravel-pagination-4953348b7103</a>).</p>
  
  <pre class="lang-xml prettyprint-override"><code>foreach($models AS $key=&gt;$model)
      $model-&gt;firstItem()+$key
  </code></pre>
  
  <p>But I don't know how to number rows in the subqueries using Query Builder. (It works in the concatenated sql version.) I need to number rows in the 2 subqueries, so that I can set result 1 of subquery1 on the same row as result 1 of subquery2. 
  So I need row numbers in each subquery (not displayed), then display row numbers in outer query. </p>
  
  <p>Concatenated raw sql version (this works):</p>
  
  <pre class="lang-php prettyprint-override"><code>$query = 'select colorY, playerY, ';                    // outer query fields
  $query .= '@rownum:= @rownum + 1 AS row_numOuter, ';   // outer query row number
  $query .= 'colorG, playerG ';                           // outer query fields
  $query .= 'from (SELECT @rownum:=0) AS Outerrow_num, ';// set outer row number=0
  $query .= '(select drawsG.color as colorG, ';           // start green subquery field list
  $query .= 'playersG.name as playerG, ';
  $query .= '(@rownumG:=@rownumG + 1) AS RowNumG ';       // green subquery row number
  $query .= 'from (select @rownumG:=0) AS rownumG, ';    // set green subquery row number=0
  $query .= 'draws AS drawsG ';                          // draws &amp; players are base tables
  $query .= 'inner join players as playersG ';            // join draws w/ players – green
  $query .= 'on playersG.id = drawsG.player_id ';
  $query .= 'where color = "green") ';
  $query .= 'as drawsGreen ';                             // green subquery alias
  $query .= 'right join ';                                // join green and yellow subqueries (yellow on right-has more in my db)
  $query .= '(select drawsY.color as colorY, ';           // start yellow subquery field list
  $query .= 'playersY.name as playerY, ';
  $query .= '(@rownumY:=@rownumY + 1) AS RowNumY ';       // yellow subquery row number
  $query .= 'from (select @rownumY:=0) AS rownumY, ';     // set yellow subquery row number=0
  $query .= 'draws AS drawsY ';
  $query .= 'inner join players as playersY ';            // join draws w/ players - yellow
  $query .= 'on playersY.id = drawsY.player_id ';
  $query .= 'where color = "yellow") ';
  $query .= 'as drawsYellow ';                            // yellow subquery alias
  $query .= 'on RowNumG = RowNumY';                       // ON clause for right join
  
  $draws = DB::select($query);
  return view('draws.index', compact(['draws']));
  </code></pre>
  
  <p>EDIT: So here's the raw mysql from above compiled:</p>
  
  <pre><code>select colorY, playerY, @rownum:= @rownum + 1 AS row_numOuter, colorG, playerG from (SELECT @rownum:=0) AS Outerrow_num, (select drawsG.color as colorG, playersG.name as playerG, (@rownumG:=@rownumG + 1) AS RowNumG from (select @rownumG:=0) AS rownumG, draws AS drawsG inner join players as playersG on playersG.id = drawsG.player_id where color = "green") as drawsGreen right join (select drawsY.color as colorY, playersY.name as playerY, (@rownumY:=@rownumY + 1) AS RowNumY from (select @rownumY:=0) AS rownumY, draws AS drawsY inner join players as playersY on playersY.id = drawsY.player_id where color = "yellow") as drawsYellow on RowNumG = RowNumY
  </code></pre>
  
  <p>Here's my (latest?) attempt to do it via Query Builder:</p>
  
  <pre><code>$subQYellow = DB::table('draws AS drawsY')                               // yellow subquery, on draws table
  -&gt;join('players AS playersY', 'playersY.id', '=', 'drawsY.player_id')   // join to players table on id
  -&gt;select('color AS colorY', 'playersY.name AS playerY',                 // yellow field list 
  DB::raw('(@rownum:=@rownum+1) AS RowNumY'))                             // set yellow subquery row number=0
  -&gt;from(DB::raw('(select @rownum:=0) AS rownumY'),                       // from "list", yellow subquery row number
  ('draws AS drawsY'))                                                        // repeat draws table (??)
  -&gt;where('color', 'yellow');                                             // color value filter
  
  $subQGreen = DB::table('draws AS drawsG')                                // green subquery, on draws table
  -&gt;join('players AS playersG', 'playersG.id', '=', 'drawsG.player_id')   // join to players table on id
  -&gt;select('color AS colorG', 'playersG.name AS playerG',                 // green field list 
  DB::raw('(@rownum:=@rownum+1) AS RowNumG'))                             // set yellow subquery row number=0
  -&gt;from(DB::raw('(select @rownum:=0) AS rownumG'),                       // from "list", green subquery row number 
  ('draws AS drawsG'))                                                    // repeat draws table (??)
  -&gt;where('color', 'green');                                              // color value filter
  
  $draws = DB::table($subgreen, 'drawsGrn')                                 // QB on subgreen w/alias
  -&gt;select('colorY AS colorYlw', 'playerY AS playerYlw',                  // outer query field list w/ aliases
  'colorG AS colorGrn', 'playerG AS playerGrn')                           // more fields
  -&gt;rightJoinSub($subyellow, 'drawsYlw', function ($join) {                 // joinSub on yellow with closure
      $join-&gt;on('RowNumG', '=', 'RowNumY');                                // outer query ON clause to join subs
  })              
  -&gt;get();
  </code></pre>
  
  <p>Note: I don't actually understand closures so I just copied. Also, never sure when mysql needs aliases. Is there a rule?</p>
  
  <p>Note: I had to repeat the draws table in FROM clause of each subquery because the row count code in the FROM clause seemed to overwrite it. If I put it in another ->from clause, the second FROM overwrites the first FROM. Catch 22.</p>
  
  <p>Note: there may be typos since I greatly simplified it to make names clearer and more meaningful. </p>
  
  <p>Any help? Thanks in advance.</p>