Built tree from selecting specific nodes

[marvinschroeder]

Let's say i have a tree like in your documentation with the "Catalog" and i would like to get the nodes "Samsung" in "Mobile" and "Apple" in "Netbooks" including all ancestor nodes up to the root, but without the root. All other nodes should not be included in the tree.

My code now looks like this:

$channels = array(4, 6);
Channel::withoutRoot()->whereIn('id', $channels)->get()->toTree();

But then i would only get the nodes "Samsung" and "Apple" and not the ancestor elements and so no tree could be built. Think of it as "a user has access on only specific nodes and the tree should be displayed up to the root".

I could get the specific nodes and built the tree up with one query per node, but this would be extremly overhead. Do you have any ideas?

[lazychaser]

You can achieve this at low level:

Channels::withoutRoot()->where(function ($q) use ($channels)
{
    $q->whereIn('id', $channels)->orWhereExists(function ($iq) use ($channels)
    {
        $iq
            ->select('child.id')
            ->from('channels child')
            ->whereIn('child.id', $channels)
            ->whereRaw('child.lft between channels.lft and channels.rgt')
            ->limit(1);
    });
})
->get()->toTree();

We just check here if either node is allowed or it has allowed descendant. Note that inner query selected from channels table. You need to specify your own if it's different.

[marvinschroeder]

What table do you mean with 'child' in the inner query? I tried it like this:

Channel::withoutRoot()->where(function ($q) use ($channels)
                {
                    $q->whereIn('id', $channels)->orWhereExists(function ($iq) use ($channels)
                    {
                        $iq->raw('select child.id from pp_channels, pp_channels as child where child.id in ('.implode(",", $channels).') and child._lft between pp_channels ._lft and pp_channels._rgt limit 1');

                    });
                })
                ->get()->toTree();

This query selects more channels which are for example siblings, but they aren't allowed to be selected.
I only have _lft, _rgt and parent_id in the pp_channels table.

[lazychaser]

You don't need to select from pp_channels again, this table is taken from main query. You need to get following raw query:

select *
from pp_channels
where parent_id is not null and
  (id in (:channels) or exists (
    select 1
    from pp_channels child
    where child.id in (:channels) and child._lft between pp_channels._lft and pp_channels._rgt)

[marvinschroeder]

Ahh, the raw query you posted last works like charm!
I had to trick laravel's query builder to get rid of some errors (Unknown column 'pp_child.id, Base table or view not found: 1146 Table 'xxxxx.pp_channels child' doesn't exist), but now it works for me with this code:

$results = Channel::withoutRoot()->where(function ($q) use ($channels)
                {
                    $q->whereIn('id', $channels)->orWhereExists(function ($iq) use ($channels)
                    {
                        $iq
                            ->selectRaw('child.id')
                            ->from('channels` `child')
                            ->whereRaw('child.id in ('.implode(",", $channels).')')
                            ->whereRaw('child._lft between pp_channels._lft and pp_channels._rgt')
                            ->limit(1);
                    });
                })
                ->get()->toTree();

Thank you!