descendants + depth incompatible with PostgreSQL

[dinobot71]

Hi, I had to do hack today to get depth scoping to work with descendants. For example if you want just the next 2 levels of children on a given node (because its a big tree and you just don't want everything).

You are supposed to be able to do this:

$results = $node ->descendants() ->withDepth() ->having('depth', <= $target) ->get()

But it crashes with PostgreSQL saying "column" depth doesn't exist. That happens because PostgreSQL and MySQL differ on how they evaluate the columsn from sub-queries in where clauses. PostgreSQL can't see aliases for columns that are from sub-queries unless they are ordered a certain way. My understanding is that PostgreSQL evaluates the where clause before any subqueries in the select columns (i.e. before the FROM)....so that's why it can't see teh depth column alias...it doesn't exist yet.

You can work around it by evaluating the result to a table talias and then applying the depth condition to that, or by moving the depth condition to the where clause.

So, the simplest hack I could do was:

  $results = $node
    ->descendants()
    ->withDepth()
    ->whereRaw('(select count(1) - 1 from "idm_user_tree_ad" as "_d" where "idm_user_tree_ad"."_lft" between "_d"."_lft" and "_d"."_rgt") <= '.$target)
    ->get();

As a user of the package though, I really shouldn't have to do that. That's a lot of knowledge of how NodeTrait actually works.

I'm not sure how to fix it since your code is likely just using Eloquent and assuming eloquent does the right thing. Only it can't in this case withDepth createes a subquery in the select columns...which again, PostgreSQL can't handle.

That is the way your documentation says to do it, leads to this:

october=# select *, (select count(1) - 1 from "idm_user_tree_ad" as "_d" where "idm_user_tree_ad"."_lft" between "_d"."_lft" and "_d"."_rgt") as "depth" from "idm_user_tree_ad" where "_lft" between 31199 and 31283 having "depth" <= 5; ERROR: column "depth" does not exist LINE 1: ...e_ad" where "_lft" between 31199 and 31283 having "depth" <=... ^ october=#

Which is non-functional in PostgreSQL. Notice that its because of the subquery in the select columns.

[rdpascua]

I'm encountering the same issue as well. I'm having issue with the Undefined column: 7 ERROR: column "depth" does not exist.

I believe you can't use aliases in having while using postgresql, @lazychaser can we just repeat the query on the where clause instead? I've tried it and it works.

[dinobot71]

Guys, sorry for not responding in so long…I dropped the ball L Maxed out at my regular job. I will try to come back to this shortly and chip in where I can.

(^_^)/

Mike.

From: Darick Pascua Jr [mailto:notifications@github.com] Sent: Tuesday, April 18, 2017 5:39 AM To: lazychaser/laravel-nestedset Cc: Michael Garvin; Author Subject: Re: [lazychaser/laravel-nestedset] descendants + depth incompatible with PostgreSQL (#126)

I'm encountering the same issue as well. I'm having issue with the Undefined column: 7 ERROR: column "depth" does not exist

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/lazychaser/laravel-nestedset/issues/126#issuecomment-294749055 , or mute the thread https://github.com/notifications/unsubscribe-auth/ARtHPC_R8Gdhg8_xCnlsc1DxsJBussl8ks5rxISggaJpZM4JsOck . https://github.com/notifications/beacon/ARtHPMqE5DUIYPfFVNEn3D_mCEiTKdq_ks5rxISggaJpZM4JsOck.gif

[mpryvkin]

+1

[ryanrapini]

Just hit this, any updates?

[mackhankins]

Need this fix also..

[tonges]

Any update?

[dinobot71]

I’m still maxed out, but if anyone wants the delta that I did for postgresql, I can easily shoot people a .tgz file. Just let me know some place to send it.

(^_^)/

Mike.

From: tonges notifications@github.com Sent: November 14, 2020 5:44 AM To: lazychaser/laravel-nestedset laravel-nestedset@noreply.github.com Cc: Michael Garvin mgarvin@bell.net; Author author@noreply.github.com Subject: Re: [lazychaser/laravel-nestedset] descendants + depth incompatible with PostgreSQL (#126)

Any update?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/lazychaser/laravel-nestedset/issues/126#issuecomment-727185788 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AENUOPCEEJHBWA5LNYZC6FTSPZNODANCNFSM4CNQ44SA .

[londoh]

@dinobot71 hi friend, I just hit this issue with pg so I could use it at the moment. maybe you could just post it on here?

thanx l.

[dinobot71]

I’m sure I can dig up the patch, give me a few hours…

(^_^)/

Mike.

From: Londoh notifications@github.com Sent: December 15, 2020 6:42 AM To: lazychaser/laravel-nestedset laravel-nestedset@noreply.github.com Cc: Michael Garvin mgarvin@bell.net; Mention mention@noreply.github.com Subject: Re: [lazychaser/laravel-nestedset] descendants + depth incompatible with PostgreSQL (#126)

@dinobot71 https://github.com/dinobot71 hi friend, I just hit this issue with pg so I could use it at the moment. maybe you could just post it on here?

thanx l.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/lazychaser/laravel-nestedset/issues/126#issuecomment-745235624 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AENUOPFJLMO4W6E5PJQXK5DSU5DRZANCNFSM4CNQ44SA . https://github.com/notifications/beacon/AENUOPHP36W4UWQQZQDYIDLSU5DRZA5CNFSM4CNQ44SKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFRVWJKA.gif

[dinobot71]

Found my local version of this thing (in an old backup folder).

https://www.dropbox.com/s/fbqnhqhsf4403wq/phprbac.zip?dl=0

You'll have to do a big "diff" to see the changes, basically look in PhpRbac\src\PhpRbac\core\lib\Jf.php and Rbac.php

this is all several years old now, so at this point...likely better to do some fresh research and see what the options are now.

[dinobot71]

Also keep in mind Eloquent etc...have been evolving for 4 years...and the queries you see in my old copy of this project...may not even be relevent anymore :( But, if PostgreSQL support is still missing, perhaps at least it points you in the right direction.

[londoh]

I did find a solution to this back last Dec using @dinobot71's hints to convert the query to run under Postgres and then utilising a query Scope on the model But as per usual I forgot what I did so here's a note to future self:

    public function scopehasDepth($query, $depth){
        return $query
            ->selectRaw('*, (select count(1) - 1 from categories as _d where categories._lft between _d._lft and _d._rgt) as depth')
            ->whereRaw('(select count(1) - 1 from categories as _d where categories._lft between _d._lft and _d._rgt) = ? ', [$depth]);
    }

Note: This isnt tested to any extent except within the limited confines of the project I need it for - where it works OK

regards

l.