Assume I have the following simplified table scheme

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

Albums can be organized as a tree and use the nested set approach.

Assume that I have the two integers p_lft and p_rgt (like parent left and parent right). I want to write the following SQL Query with Laravel/Eloquent

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)
  );

May actual problem is that I need to refer to a column of the outer query in the inner sub-query. While I know how aliasing of table is possible on the SQL layer, I have no idea how Laravel constructs its aliases under the hood.

On top, I cannot control the outer query. I need to write a function which gets a query builder as its parameter and I only know that its associated model is an instance of Album. This is how far I came

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);
}

And somewhere at other places in the code the method may be invoked like this

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

or like this

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

My main problem is that I have no control over the outer query and thus I have no idea how I can control the aliasing.

CodePudding user response：

Can you try this?

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)  
      });

This "should work" because if you do a POC like this:

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

In your tinker console, you will se something like this

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

...Hope it works

CodePudding user response：

My main problem is that I have no control over the outer query and thus I have no idea how I can control the aliasing.

You can use from() to alias anything. For example

Album::query()
    ->select('*')
    ->from('albums', 'child')
    ->get();

compiles to

SELECT * FROM `albums` as `child`

By default, the table alias should be the table itself. (Album -> albums -> albums._lft)

Translating your query line by line isn't complicated either assuming the grouping's already there (no missing (...) )

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')
            ->whereColumn('child._rgt', '<=', 'inner._rgt')
            ->where('inner._rgt', '<', $p_rgt);
            ->where(/* other conditions here */)
    })
    ->get();