我正在尝试向这个查询添加一个左连接，我一直在使用它来获取相关记录的npergroup。我在sql中创建了查询，只是不知道如何将其转换为laravel查询生成器代码。
我想添加一个 left join 用于性能目的。当我从get go中获取所有列时，查询需要的负载太大而无法完成（在400k行上大约6秒），而 left join 那只需要半秒钟。
我试着添加一个 left join 之后 mergeBindings 但是，我不知道如何在第一个select中指定所需的列。无论我尝试了什么，第一次选择总是保持不变 select * .
以下是我需要更改的laravel范围代码：

public function scopeNPerGroup($query, $group, $n = 10, $columns)
{

    // queried table
    $table = ($this->getTable());

    // initialize MySQL variables inline
    $query->from(DB::raw("(SELECT @rank:=0, @group:=0) as vars, {$table}"));

    // if no columns already selected, let's select *
    if (! $query->getQuery()->columns && empty($columns)) {
        $query->select("{$table}.*");
    }
    elseif (!empty($columns)) {
        foreach ($columns as $column) {
            $query->addSelect($column);
        }
    }

    // make sure column aliases are unique
    $groupAlias = 'group_'.md5(time());
    $rankAlias  = 'rank_'.md5(time());

    // apply mysql variables
    $query->addSelect(DB::raw(
        "@rank := IF(@group = {$group}, @rank+1, 1) as {$rankAlias}, @group := {$group} as {$groupAlias}"
    ));

    // make sure first order clause is the group order
    $query->getQuery()->orders = (array) $query->getQuery()->orders;
    array_unshift($query->getQuery()->orders, ['column' => $group, 'direction' => 'asc']);

    // prepare subquery
    $subQuery = $query->toSql();

    // prepare new main base Query\Builder
    $newBase = $this->newQuery()
        ->from(DB::raw("({$subQuery}) as {$table}"))
        ->mergeBindings($query->getQuery())
        ->where($rankAlias, '<=', $n)
        ->getQuery();

    // replace underlying builder to get rid of previous clauses
    $query->setQuery($newBase);
}

下面是由上述代码生成的sql：

SELECT * 
FROM   (SELECT `positions`.`id`, 
               `positions`.`keyword_id`, 
               `positions`.`position`, 
               @rank := IF(@group = keyword_id, @rank + 1, 1) AS 
               rank_fa9d7a6f55c38becc0b28f348651a856, 
               @group := keyword_id                           AS 
                      group_fa9d7a6f55c38becc0b28f348651a856 
        FROM   (SELECT @rank := 0, 
                       @group := 0) AS vars, 
               positions 
        ORDER  BY `keyword_id` ASC, 
                  `created_at` DESC) AS positions 
WHERE  `rank_fa9d7a6f55c38becc0b28f348651a856` <= '2' 
       AND `positions`.`keyword_id` IN ('1', '2', ...)

下面是我需要它生成的sql：（只要它完成相同的任务，也就是获取额外的 positions.url 列。）

SELECT `positionsA`.`id`, `positionsA`.`keyword_id`, `positionsA`.`position`, `positions`.`url`
FROM   (SELECT `positions`.`id`, 
               `positions`.`keyword_id`, 
               `positions`.`position`, 
               @rank := IF(@group = keyword_id, @rank + 1, 1) AS 
               rank_e2d9373d3bb35d6aabe9ffc57ff29c1c, 
               @group := keyword_id                           AS 
                      group_e2d9373d3bb35d6aabe9ffc57ff29c1c 
        FROM   (SELECT @rank := 0, 
                       @group := 0) AS vars, 
               positions 
        ORDER  BY `keyword_id` ASC, 
                  `created_at` DESC) AS positionsA 
LEFT JOIN `positions` on `positionsA`.`id` = `positions`.`id`
WHERE  `rank_e2d9373d3bb35d6aabe9ffc57ff29c1c` <= '2' 
       AND `positionsA`.`keyword_id` IN ('1', '2', ...)