时间戳

首页 > 解决方案 > Laravel/Eloquent：如何在 Larvael `->whereNotExists` 子查询中引用外部选择的列？

php - Laravel/Eloquent：如何在 Larvael `->whereNotExists` 子查询中引用外部选择的列？

问题描述

请阅读问题直到最后；使用的最后部分包含说明限制和条件的重要示例

问题描述

假设我有以下简化的表格方案

CREATE TABLE albums (
  id SEQUENCE PRIMARY KEY,
  parent_id BIGINT,
  _lft BIGINT NOT NULL,
  _rgt BIGINT NOT NULL
  ...
)

可以将相册组织为树并使用嵌套集方法。

假设我有两个整数p_lft和p_rgt（如父左和父右）。最终，Eloquent Builder 应该构造一个查询，该查询应该在 SQL 层上编译成这样的东西：

SELECT * FROM albums AS child
WHERE
  p_lft < child._lft AND child._rgt < p_rgt
AND
  NOT EXISTS (
    SELECT * FROM albums AS inner
    WHERE
      p_lft < inner._lft AND inner._lft <= child._lft
    AND
      child._rgt <= inner._rgt AND inner._rgt < p_rgt
    AND
      (more conditions here)
  );

实际问题是查询是在程序中的两个不同点构建的：

a) 查询的外部部分（即SELECT * FROM albums AS child上面对应的部分） b) 内部子查询

特别是内部子查询应该写成一个通用函数——称为过滤函数——它可以应用于外部查询的任意实例。外部查询的唯一要求是它查询正确的模型，即Album. 特别是，我无法控制外部查询，因此我不知道是否已经发生了任何别名。因此，我不知道如何从内部查询（即从过滤器内部）引用外部查询。

解决方案的尝试

这是我走了多远

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as BaseBuilder;

function applyFilter(Builder $builder, int $p_lft, int $p_rgt): void {
  // Ensure that the outer query queries for the right model
  $model = $query->getModel();
  if (!($model instanceof Album )) {
    throw new \InvalidArgumentException();
  }
  // We must wrap everything into an outer query to avoid any undesired
  // effects in case that the original query already contains an
  // "OR"-clause.
  $filter = function (Builder $query) use ($p_lft, $p_rgt) {
    $query
      ->where('_lft', '>', $p_lft)   // _lft corresponds to child._lft in SQL
      ->where('_rgt', '<', $p_rgt)   // _rgt corresponds to child._rgt in SQL
      ->whereNotExists(function (BaseBuilder $subQuery) use ($p_lft, $p_rgt) {
        $subQuery->from('albums')
          ->where('_lft', '>', p_lft)  // here _lft corresponds to inner._lft in SQL
          ->where('_lft', '<=', ????)  // how do I refer to the outer _lft here?
          ->where('_rgt', '>=', ????)  // some question
          ->where('_rgt', '<', p_rgt)  // here _rgt corresponds to inner._rgt in SQL
      });
    };
  
  $builder->where($filter);
}

使用示例

并且在代码中的其他地方的某个地方可以像这样调用过滤器

applyFilter(
  Albums::query()
    ->where(some_condition)
    ->orWhere(some_other_condition),
  $left, $right
)->get();

或者像这样

Photo::query()
  ->where(some_condition)
  ->whereHas('album', fn(Builder $b) => applyFilter($b, $left, $right))

甚至像这样

Album::query()
  ->whereHas('parent', fn(Builder $b) => applyFilter($b, $left, $right))

请注意，最后一种情况特别棘手。filter 函数在 a 内部用于与自身whereHas关联。Album因此，过滤器函数被调用用于已经别名album为不同名称的查询。

标签： phplaraveleloquentsubqueryexists

解决方案

我的主要问题是我无法控制外部查询，因此我不知道如何控制别名。

您可以使用from()任何别名。例如

Album::query()
    ->select('*')              // ->select('*') is completely optional in this query.
    ->from('albums', 'child')
    ->get();

编译为

SELECT * FROM `albums` as `child`

默认情况下，表别名应该是表本身。( Album-> albums-> albums._lft)

(...)假设分组已经存在（没有丢失），逐行翻译您的查询并不复杂

Album::query()
    ->select('*')
    ->from('albums', 'child')
    ->where('child._lft', '>', $p_lft)
    ->where('child._rgt', '<', $p_rgt)
    ->whereNotExists(function ($sub) use ($p_lft, $p_rgt) {
        $sub->select('*')
            ->from('albums', 'inner')
            ->where('inner._lft', '>', $p_lft)
            ->whereColumn('inner._lft', '<=', 'child._lft')  // when comparing two columns, whereColumn/orWhereColumn must be used.
            ->whereColumn('child._rgt', '<=', 'inner._rgt')
            ->where('inner._rgt', '<', $p_rgt);
            ->where(/* other conditions here */)
    })
    ->get();

在函数中获取表名：

use Illuminate\Support\Str;

function applyFilter(Builder $builder, int $p_lft, int $p_rgt): void {
    // get table name or aliased table name.
    $table = Str::after($builder->getQuery()->table, ' as '); 
    // similarly, you can get an array of all joined tables in the outer query
    $joined_tables = array_map(fn($join) => Str::after($join->table, ' as '), $builder->getQuery()->joins)
}

推荐阅读