Column not found: 1054 Unknown column 'depth' in 'where clause'

[Luckycleri]

Hi @staudenmeir, first thank you for creating this wonderful package, it helped us a lot improving performance all over our project.

Currently we're running into an issue regarding missing cte columns and im wondering if you may help us or nudge us into the right direction.

Issue

When trying to interact with the depth column in any way (eg. using whereDepth() or orderByDesc("depth") s.o) we get the following Error: Column not found: 1054 Unknown column 'depth' in 'where clause'. The package runs fine otherwise - children, ancestors, descendants and the likes work fine.

The error is thrown in:

/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(608)

Here an example of what we tried:

$this->descendantsAndSelf()->whereDepth(2)->delete();

the query (please note that i anonymized it):

with recursive `laravel_cte` as ((SELECT `cool_table_name`.*, -1 as `depth`, cast(`id` as char(65535)) as `path` FROM `cool_table_name` WHERE `cool_table_name`.`id` = 17) union all (SELECT `cool_table_name`.*, `depth` - 1 as `depth`, concat(`path`, '.', `cool_table_name`.`id`) FROM `cool_table_name` inner join `laravel_cte` on `laravel_cte`.`cool_table_name_parent_id` = `cool_table_name`.`id`)) SELECT * FROM `laravel_cte` WHERE `type` = 'cool-type'

Relevant Versions

We are using:

Mysql@8.0.32
staudenmeir/laravel-adjacency-list@1.20.1
php@8.2
Laravel@10.48.17

What we already tried

• Trying mysql@9
• Trying newer laravel-adjacency-list plugin
• Trying older laravel-adjacency-list plugin
• Checking if staudenmeir/laravel-cte is installed as peer dependency (it is @1.10.1)
• Checking if the query works when i run it on the database directly (it does work - which is odd)

Some thoughts

We theorized that it might be an issue with laravel, eloquent or some mysql connector not knowing how to handle cte.

[staudenmeir]

Hi @Luckycleri,

the query (please note that i anonymized it):
with recursive laravel_cte as ((SELECT cool_table_name., -1 as depth, cast(id as char(65535)) as path FROM cool_table_name WHERE cool_table_name.id = 17) union all (SELECT cool_table_name., depth - 1 as depth, concat(path, '.', cool_table_name.id) FROM cool_table_name inner join laravel_cte on laravel_cte.cool_table_name_parent_id = cool_table_name.id)) SELECT * FROM laravel_cte WHERE type = 'cool-type'

Where is the depth constraint from ->whereDepth(2) constraint? Did you remove it from the SQL?

[Luckycleri]

Hi @staudenmeir,

this is the whole query. Only thing i did was change the table name and parent id name.

Thank you for looking into this.

[staudenmeir]

Something's not right here, the error message doesn't match the query: It doesn't have a where clause with a depth column.

Is the query coming from this code?

$this->descendantsAndSelf()->whereDepth(2)->delete();

[Luckycleri]

I reexamined the code and it seems the query i sent you was wrong. For some reason it was logged incorrectly. Here is the new anonymized query:

with recursive `laravel_cte` as (
                                   (select cool_table_name.*,
                                           0 as `depth`,
                                           cast(`id` as char(65535)) as `path`
                                    from cool_table_name
                                    where cool_table_name.`id` = 14)
                                 union all
                                   (select cool_table_name.*,
                                           `depth` + 1 as `depth`,
                                           concat(`path`, '.', cool_table_name.`id`)
                                    from cool_table_name
                                    inner join `laravel_cte` on `laravel_cte`.`id` = cool_table_name.`parent_id`))
delete
from cool_table_name
where `depth` = 2
  and cool_table_name.`id` in
    (select `id`
     from `laravel_cte`)

When run directly on my database it yields the same Unknown column 'depth' in 'where clause'-Error which is at least more coherent with my percieved error.

[staudenmeir]

Due to the structure of DELETE query, it's more complicated to add a depth constraint:

Use this (more efficient) approach instead:

static::withMaxDepth(2, function () {
    $this->descendantsAndSelf()->delete();
});

[Luckycleri]

Thank you very much, your approach works fine!

Can the same be done if i want to order by depth? For example if i want to delete elements bottom up?

[staudenmeir]

That requires a different approach, unfortunately. I'll look into it.

[staudenmeir]

You can use whereIn():

static::query()
    ->whereIn(
        'id',
        $this->descendantsAndSelf()->whereDepth('>', 3)->select('id')
    )
    ->delete();