List of models where root or descendants have a condition applied

[HelenaGwyn]

Hi

I need some help figuring out if and how this is possible.

I have a list of recursive messages.

Each message has an array of ids of groups of users that can read the message stored in a json-array.
I want a list of all the root-messages (with parent_id null) and their descendants (not as a tree) with a condition whereRaw(JSON_OVERLAPS...) for the readrights where the root, one of the descendants or both can be read.
It has to be possible to show a descendant without having the right to read the root. In the interface the root will be shown without content.

Each message can also be pinned.
On top of the rights I would also like to check or filter whether the root or one of the descendants is pinned.
The whole messagethread will then be pinned in the interface.

Can this be done somehow?

[staudenmeir]

Hi @HelenaGwyn,
Can you please share the message table migration and some sample data?

[HelenaGwyn]

I'm sorry for the delay. I got the flu and the holidays came in between.

It's not the full scope of my project, but should have enough info for the use case.

Schema::create('messages', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('parent_id')->nullable();
    $table->text('content');
    $table->json('can_read');
    $table->boolean('is_pinned');
});

/*
 * A user can be a member of multiple groups.
 * Each group has an ID. Could be an EloquentModel or an Enum. Not that important for now.
 * The column can_read is a JSON-array of the groups-IDs that can read this message.
 * A reply can be readable by more groups than can_read of the root-message.
 * With Messages::whereRaw("JSON_OVERLAPS(can_read, '"[2,4]"')") you can get all messages that have group-ID 2 or 4
 * In the examples I limited it to just two IDs 1 and 2.
 * - I'm looking for a query that gets all roots with descendants where either the root or one of the descendants is readable by f.e. group-ID 2.
 * - I'm looking for a query that gets all roots with descendants where either the root or one of the descendants is readable by f.e. group-ID 2 and none of them has is_pinned true.
 * - I'm looking for a query that gets all roots with descendants where either the root or one of the descendants is readable by f.e. group-ID 2 and the root or one of the descendants has is_pinned true.
 */

DB::table('messages')->insert([
    // Root readable, all replies readable
    ['id' => 1, 'parent_id' => null, 'content' => 'root 1', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 2, 'parent_id' => 1, 'content' => 'root 1 / reply 2', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 3, 'parent_id' => 2, 'content' => 'root 1 / reply 2 / reply 3', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 4, 'parent_id' => 1, 'content' => 'root 1 / reply 4', 'can_read' => "[1,2]", 'is_pinned' => false],
    // Root readable, some replies readable
    ['id' => 5, 'parent_id' => null, 'content' => 'root 5', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 6, 'parent_id' => 5, 'content' => 'root 5 / reply 6', 'can_read' => "[1]", 'is_pinned' => false],
    ['id' => 7, 'parent_id' => 5, 'content' => 'root 5 / reply 7', 'can_read' => "[1]", 'is_pinned' => false],
    ['id' => 8, 'parent_id' => 7, 'content' => 'root 5 / reply 7 / reply 8', 'can_read' => "[1,2]", 'is_pinned' => false],
    // Root unreadable, all replies readable
    ['id' => 9, 'parent_id' => null, 'content' => 'root 9', 'can_read' => "[1]", 'is_pinned' => true],
    ['id' => 10, 'parent_id' => 9, 'content' => 'root 9 / reply 10', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 11, 'parent_id' => 10, 'content' => 'root 9 / reply 10 / reply 11', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 12, 'parent_id' => 9, 'content' => 'root 9 / reply 12', 'can_read' => "[1,2]", 'is_pinned' => false],
    // Root unreadable, some replies readable
    ['id' => 13, 'parent_id' => null, 'content' => 'root 13', 'can_read' => "[1]", 'is_pinned' => false],
    ['id' => 14, 'parent_id' => 13, 'content' => 'root 13 / reply 14', 'can_read' => "[1]", 'is_pinned' => false],
    ['id' => 15, 'parent_id' => 14, 'content' => 'root 13 / reply 14 / reply 15', 'can_read' => "[1,2]", 'is_pinned' => true],
    ['id' => 16, 'parent_id' => 13, 'content' => 'root 13 / reply 16', 'can_read' => "[1,2]", 'is_pinned' => false],

    // Root readable and pinned, all replies readable and unpinned
    ['id' => 17, 'parent_id' => null, 'content' => 'root 17', 'can_read' => "[1,2]", 'is_pinned' => true],
    ['id' => 18, 'parent_id' => 17, 'content' => 'root 17 / reply 18', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 19, 'parent_id' => 17, 'content' => 'root 17 / reply 19', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 20, 'parent_id' => 19, 'content' => 'root 17 / reply 19 / reply 20', 'can_read' => "[1,2]", 'is_pinned' => false],
    // Root readable and unpinned, some replies readable and pinned
    ['id' => 21, 'parent_id' => null, 'content' => 'root 21', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 22, 'parent_id' => 21, 'content' => 'root 21 / reply 22', 'can_read' => "[1,2]", 'is_pinned' => true],
    ['id' => 23, 'parent_id' => 21, 'content' => 'root 21 / reply 23', 'can_read' => "[1]", 'is_pinned' => false],
    ['id' => 24, 'parent_id' => 23, 'content' => 'root 21 / reply 23 / reply 24', 'can_read' => "[1,2]", 'is_pinned' => true],
    // Root readable and unpinned, some replies readable and unpinned, some replies unreadable and pinned
    ['id' => 25, 'parent_id' => null, 'content' => 'root 25', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 26, 'parent_id' => 21, 'content' => 'root 25 / reply 26', 'can_read' => "[1]", 'is_pinned' => true],
    ['id' => 27, 'parent_id' => 17, 'content' => 'root 25 / reply 26', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 28, 'parent_id' => 19, 'content' => 'root 25 / reply 27 / reply 28', 'can_read' => "[1]", 'is_pinned' => true],
    // Root unreadable and pinned, all replies readable and unpinned
    ['id' => 29, 'parent_id' => null, 'content' => 'root 29', 'can_read' => "[1]", 'is_pinned' => true],
    ['id' => 30, 'parent_id' => 21, 'content' => 'root 29 / reply 30', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 31, 'parent_id' => 17, 'content' => 'root 29 / reply 31', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 32, 'parent_id' => 19, 'content' => 'root 29 / reply 31 / reply 32', 'can_read' => "[1,2]", 'is_pinned' => false],
    // Root unreadable and unpinned, some replies readable and pinned
    ['id' => 33, 'parent_id' => null, 'content' => 'root 33', 'can_read' => "[1]", 'is_pinned' => false],
    ['id' => 34, 'parent_id' => 21, 'content' => 'root 33 / reply 34', 'can_read' => "[1]", 'is_pinned' => false],
    ['id' => 35, 'parent_id' => 17, 'content' => 'root 33 / reply 35', 'can_read' => "[1,2]", 'is_pinned' => true],
    ['id' => 36, 'parent_id' => 19, 'content' => 'root 33 / reply 35 / reply 36', 'can_read' => "[1,2]", 'is_pinned' => true],
    // Root unreadable and unpinned, some replies readable and unpinned, some replies unreadable and pinned
    ['id' => 37, 'parent_id' => null, 'content' => 'root 37', 'can_read' => "[1]", 'is_pinned' => false],
    ['id' => 38, 'parent_id' => 21, 'content' => 'root 37 / reply 38', 'can_read' => "[1]", 'is_pinned' => true],
    ['id' => 39, 'parent_id' => 17, 'content' => 'root 37 / reply 39', 'can_read' => "[1,2]", 'is_pinned' => false],
    ['id' => 40, 'parent_id' => 19, 'content' => 'root 37 / reply 39 / reply 40', 'can_read' => "[1]", 'is_pinned' => true],
]);

[staudenmeir]

Thanks. Try something something like this and add the remaining constraints:

Messages::isRoot()
    ->whereHas(
        'descendantsAndSelf',
        function(Builder $query) use($groupIdsJson) {
            $query->whereRaw("JSON_OVERLAPS(can_read, '$groupIdsJson')"); // TODO: Use parameter binding
     })
     ->get();

I'm looking for a query that gets all roots with descendants

Do you want to get all descendants of the matching roots or only the descendants that also match the constraints?

[HelenaGwyn]

I was able to get two queries that filter out the roots with the constraints as far as I can see.

$pinned = Message::isRoot()
      ->whereHas(
          'descendantsAndSelf',
          function($query) use ($groupIdsJson) {
              $query->whereRaw("JSON_OVERLAPS(can_read, '$groupIdsJson')")->where('is_pinned',1);
      })
      ->get();

$unpinned = Message::isRoot()
      ->whereHas(
          'descendantsAndSelf',
          function(Illuminate\Database\Eloquent\Builder $query) use($groupIdsJson) {
              $query->whereRaw("JSON_OVERLAPS(can_read, '$groupIdsJson')");
      })
      ->whereDoesntHave(
          'descendantsAndSelf',
          function(Illuminate\Database\Eloquent\Builder $query) {
              $query->where('is_pinned',1);
      })
      ->get();

I would like to eager load only the descendants with where the can_read-constraint is met.
I tried adding

->with(['descendants' => function($query) use ($groupIdsJson) {
     $query->whereRaw("JSON_OVERLAPS(can_read, '$groupIdsJson')");
}])

but gives an Undefined array key-error when the parent of a message doesn't meet the constraint.
Message 7 can't be read. Message 8 which has 7 as parent can be read. Which gives Undefined array key 7.
Is there a way around this?

I could solve this with adding an extra root_id-column and creating a custom scoped relation that gets all the replies without using the recursiveness.

[staudenmeir]

but gives an Undefined array key-error when the parent of a message doesn't meet the constraint.
Message 7 can't be read. Message 8 which has 7 as parent can be read. Which gives Undefined array key 7.

There's not a good solution to this issue, unfortunately. Due to the way eager loading works, all intermediate messages also need to be in the result set.

I could solve this with adding an extra root_id-column and creating a custom scoped relation that gets all the replies without using the recursiveness.

An alternative approach would be eager loading all descendants and then filtering them afterwards with an accessor/getter. Of course this adds some inefficiency. Do you know what percentage of descendants are filtered out on average?

[HelenaGwyn]

Do you know what percentage of descendants are filtered out on average?

In the old system we're moving away from we had recursiveness in the back-end but in a very unclear rather unvisible way on the front-end. Messages were shown individually although being part of a tree.
The new system and front-end shows everything inline underneath the root (cfr. gmail-messages). The old data needs to fit into this. So depending on the user retrieving the messages, they might just see one or two messages from a whole tree even without possibly being able to read the root. Letting them wait while loading the whole tree for just a few messages doesn't feel right, although we don't have many of those users. Most users will probably see most or all the messages.

For now I created a relationship using an extra root_id column.

public function replies(): HasMany
{
    return $this->hasMany(Message::class, 'root_id', 'id');
}

and eager load them with a constraint

->with(['replies' => function($query) use ($groupIdsJson) {
     $query->whereRaw("JSON_OVERLAPS(can_read, '$groupIdsJson')")
}])

I'll try it out with an accessor as well and measure the speed in every use case once our old data is imported.

Thank you, this answered my question for now.

[HelenaGwyn]

Is it so that these ->whereHas('descendantsAndSelf', function($query) ... are not possible on MariaDB?
Is there a workaround splitting it into more queries?

[staudenmeir]

Is it so that these ->whereHas('descendantsAndSelf', function($query) ... are not possible on MariaDB?

No, MariaDB doesn't support that yet:
https://github.com/staudenmeir/laravel-adjacency-list?tab=readme-ov-file#known-issues

Is there a workaround splitting it into more queries?

I haven't found a good workaround yet, unfortunately.