php - 在 Laravel 查询/子查询构建器中添加左连接

Question

我正在尝试将左连接添加到我一直用来获取相关记录的 nPerGroup 的查询中。我在 SQL 中创建了查询，只是不知道如何将其转换为 Laravel 查询生成器代码。

我想添加 left join 的原因是为了提高性能。当我从一开始就获取所有列时，查询负载太大而无法完成(40 万行大约需要 6 秒)，而使用 left join 则只需要半秒。

我已经尝试在 mergeBindings 之后添加一个 left join，但是，我不知道如何在第一次选择中指定我需要的列。无论我尝试什么，第一个选择始终保持为 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', ...) 

Answer 1

我想你需要这样的东西:

$newBase = $this->newQuery()
    ->from(DB::raw("({$subQuery}) as {$table}A"))
    ->leftJoin('positions', 'positionsA.id', '=', 'positions.id')
    ->mergeBindings($query->getQuery())
    ->where($rankAlias, '<=', $n)
    ->getQuery();