laravel - 在 Laravel 中，如何使用 Query Builder（或 Eloquent）对子查询中的行进行编号？

Question

我希望我的解释清楚地表明我正在尝试做什么，并且有人可以指导我。我正在尝试将绘制不同颜色的两组玩家（用户）排成一行，在同一个表行上彼此相对。占据同一行的玩家没有共同的字段值来加入他们，除了他们的子查询中的行号，他们碰巧绘制的顺序，这是随机的（所以 id 不起作用）。所以第一个画绿色的玩家与第一个画黄色的玩家在同一行，依此类推。每个子查询连接 player 表和 draws 表，外部查询通过子查询中玩家的绘制顺序连接两个子查询。

编辑：

Tables:
players: id, name (simplified user);
draws: id, player_id, color

期望的结果（大大简化）：

    Green     |Row|         Yellow
Name   Draw             Name     Draw
Joe   green     1       Ed      yellow
Mary  green     2       John    yellow
Sam   green     3       Clara   yellow

去年，我什至还没有听说过 Laravel（甚至是 OOP），所以我能够在去年尝试使用 php 和 sql 生成表格。我也可以创建它连接纯 sql（如下），但我不能分页（除非有人可以告诉我如何）。所以现在我想使用 Query Builder 或 Eloquent（特别是这样我可以分页）。我已经尝试使用查询生成器（如下）。但是子查询中的行号尝试要么导致错误，要么只是不起作用，这阻止了我在外部查询连接 ON 子句中设置子查询行相等。子查询行号不需要显示，它们只是用于排列行。使用分页对视图中的外部查询行进行编号应该不是问题：（感谢@panjeh https://medium.

foreach($models AS $key=>$model)
    $model->firstItem()+$key

但我不知道如何使用查询生成器对子查询中的行进行编号。（它适用于连接的 sql 版本。）我需要对 2 个子查询中的行进行编号，以便我可以将 subquery1 的结果 1 设置在与 subquery2 的结果 1 相同的行上。所以我需要每个子查询中的行号（不显示），然后在外部查询中显示行号。

连接的原始 sql 版本（有效）：

$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 & 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']));

编辑：所以这是上面编译的原始mysql：

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

这是我的（最新的？）尝试通过查询生成器进行的操作：

$subQYellow = DB::table('draws AS drawsY')                               // yellow subquery, on draws table
->join('players AS playersY', 'playersY.id', '=', 'drawsY.player_id')   // join to players table on id
->select('color AS colorY', 'playersY.name AS playerY',                 // yellow field list 
DB::raw('(@rownum:=@rownum+1) AS RowNumY'))                             // set yellow subquery row number=0
->from(DB::raw('(select @rownum:=0) AS rownumY'),                       // from "list", yellow subquery row number
('draws AS drawsY'))                                                        // repeat draws table (??)
->where('color', 'yellow');                                             // color value filter

$subQGreen = DB::table('draws AS drawsG')                                // green subquery, on draws table
->join('players AS playersG', 'playersG.id', '=', 'drawsG.player_id')   // join to players table on id
->select('color AS colorG', 'playersG.name AS playerG',                 // green field list 
DB::raw('(@rownum:=@rownum+1) AS RowNumG'))                             // set yellow subquery row number=0
->from(DB::raw('(select @rownum:=0) AS rownumG'),                       // from "list", green subquery row number 
('draws AS drawsG'))                                                    // repeat draws table (??)
->where('color', 'green');                                              // color value filter

$draws = DB::table($subgreen, 'drawsGrn')                                 // QB on subgreen w/alias
->select('colorY AS colorYlw', 'playerY AS playerYlw',                  // outer query field list w/ aliases
'colorG AS colorGrn', 'playerG AS playerGrn')                           // more fields
->rightJoinSub($subyellow, 'drawsYlw', function ($join) {                 // joinSub on yellow with closure
    $join->on('RowNumG', '=', 'RowNumY');                                // outer query ON clause to join subs
})              
->get();

注意：我实际上并不了解闭包，所以我只是复制了。此外，永远不能确定 mysql 何时需要别名。有规律吗？

注意：我不得不在每个子查询的 FROM 子句中重复绘制表，因为 FROM 子句中的行计数代码似乎覆盖了它。如果我把它放在另一个 ->from 子句中，第二个 FROM 会覆盖第一个 FROM。捕获 22。

注意：可能有错别字，因为我大大简化了它以使名称更清晰，更有意义。

有什么帮助吗？提前致谢。

标签： laravelsubqueryrow-number