請閱讀問題直到最后；關于使用的最后一部分包含說明限制和條件的重要示例

問題描述

假設我有以下簡化的表格方案

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不同的查詢。

uj5u.com熱心網友回復：

你能試試這個嗎？

DB::query()->fromSub($builder, "my_new_table_alias")
     ->whereNotExists(function (BaseBuilder $subQuery) use ($p_lft, $p_rgt) {
        $subQuery->from('albums')
          ->where('_lft', '>', p_lft)  
          // check if this is the field you are looking for
          ->where('_lft', '<=', 'my_new_table_alias._lft')  
          ->where('_rgt', '>=', 'my_new_table_alias._rgt')  
          ->where('_rgt', '<', p_rgt)  
      });

這“應該有效”，因為如果您執行這樣的 POC：

DB::query()->fromSub(Album::query(), 'my_album_table_alias')->toSql()

在你的 tinker 控制臺中，你會看到這樣的東西

"select * from (select * from `album`) as `my_album_table_alias`"

...希望它有效

uj5u.com熱心網友回復：

我的主要問題是我無法控制外部查詢，因此我不知道如何控制別名。

你可以使用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)
}

標籤：php 拉拉维尔 雄辩 子查询 存在

上一篇：Laravel：如何分組選擇兩列具有不同的值

下一篇：如何在刀片檔案Laravel中獲取唯一值