Relation manager attach action duplicate callback breaks recursive relationships

[Quadrubo]

Package

filament/tables

Package Version

v3.0.25

Laravel Version

v10.19.0

Livewire Version

v3.0.0-beta.9

PHP Version

PHP 8.2.9

Problem description

I have a recursive relationship on my model. App\Models\Task can have many App\Model\Task and vice versa. When trying to attach on a relation manager, the duplicate check doesn't work correctly. I can attach as many models as I like.

Tables:

         Schema::create('task_edges', function (Blueprint $table) {
            $table->foreignId('parent_id')->references('id')->on('tasks');
            $table->foreignId('child_id')->references('id')->on('tasks');

            $table->unique(['parent_id', 'child_id']);
        });

Relationships:

    public function childTasks(): BelongsToMany
    {
        return $this->belongsToMany(Task::class, 'task_edges', 'parent_id', 'child_id');
    }

    public function parentTasks(): BelongsToMany
    {
        return $this->belongsToMany(Task::class, 'task_edges', 'child_id', 'parent_id');
    }

Problematic Code AttachAction.php:264

...
fn (Builder $query): Builder => $query->where(
    $table->getRelationship()->getParent()->getQualifiedKeyName(),
    $table->getRelationship()->getParent()->getKey(),
),
...

Generated SQL:

SELECT DISTINCT
    `tasks`.*
FROM
    `tasks`
LEFT JOIN `task_edges` ON `tasks`.`id` = `task_edges`.`child_id`
WHERE NOT
    EXISTS(
    SELECT
        *
    FROM
        `tasks` AS `laravel_reserved_0`
    INNER JOIN `task_edges` ON `laravel_reserved_0`.`id` = `task_edges`.`parent_id`
    WHERE
        `tasks`.`id` = `task_edges`.`child_id` AND `tasks`.`id` = ?
)
ORDER BY
    `tasks`.`name` ASC;

Because the tables in the query have the same name (because it is the same table), the query fails as the callback adds the sql condition AND tasks.id = ? to the query which excludes all entries.

This is fixed by using getKeyName() instead of getQualifiedKeyName() in which case just the condition AND id = ? is added which correctly uses the current scope laravel_reserved_0 and the query succeeds.

SELECT DISTINCT
    `tasks`.*
FROM
    `tasks`
LEFT JOIN `task_edges` ON `tasks`.`id` = `task_edges`.`child_id`
WHERE NOT
    EXISTS(
    SELECT
        *
    FROM
        `tasks` AS `laravel_reserved_0`
    INNER JOIN `task_edges` ON `laravel_reserved_0`.`id` = `task_edges`.`parent_id`
    WHERE
        `tasks`.`id` = `task_edges`.`child_id` AND `id` = ?
)
ORDER BY
    `tasks`.`name` ASC;

Expected behavior

The query should use the local scope incase both tables of the relationship are the same table. I don't know if this breaks other behavior.

Steps to reproduce

1) Create a model that relates to itself:

Schema::create('tasks', function (Blueprint $table) {
            $table->id();

            $table->string('name');

            $table->timestamps();
        });

        Schema::create('task_edges', function (Blueprint $table) {
            $table->foreignId('parent_id')->references('id')->on('tasks');
            $table->foreignId('child_id')->references('id')->on('tasks');

            $table->unique(['parent_id', 'child_id']);
        });

public function childTasks(): BelongsToMany
    {
        return $this->belongsToMany(Task::class, 'task_edges', 'parent_id', 'child_id');
    }

    public function parentTasks(): BelongsToMany
    {
        return $this->belongsToMany(Task::class, 'task_edges', 'child_id', 'parent_id');
    }

2) Create a relation manager for that relationship:

class ChildrenRelationManager extends RelationManager
{
    protected static string $relationship = 'childTasks';

    protected static ?string $inverseRelationship = 'parentTasks';
    ...
}

3) Try the attach action.

Relevant log output

No response

[sergejostir]

I also have the same problem https://github.com/filamentphp/filament/discussions/8950

[danharrin]

Fixed by #10078