Filament•13mo ago

Filtering many to many relations

Does anyone know how to filter based on two tables with a many to many relations? I keep geeting the following error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tags' in 'where clause' SELECT count(*) AS aggregate FROM tasks WHERE (tags IN (2))

14 Replies

toeknee•13mo ago

add relationship to the filter?

kokpithuaOP•13mo ago

                Tables\Filters\SelectFilter::make('tags')
                ->relationship('task_id', 'tag_id')
                ->multiple()
                ->options($tagOptions)
                ->label('Tags')
                ->placeholder('Filter by Tags')
            ])

                Tables\Filters\SelectFilter::make('tags')
                ->relationship('task_id', 'tag_id')
                ->multiple()
                ->options($tagOptions)
                ->label('Tags')
                ->placeholder('Filter by Tags')
            ])

Still doesn't work getting the same error

toeknee•13mo ago

Try placing relationship at the bottom

kokpithuaOP•13mo ago

I think i figured out the issue. The sql is searching for the column tags = 2 when it should search for the id = 2

    public static function table(Table $table): Table
    {

            $tags = Tag::all();

            $tagOptions = [];
            foreach ($tags as $tag) {
                $tagOptions[$tag->id] = $tag->name;
            }
            return $table

    public static function table(Table $table): Table
    {

            $tags = Tag::all();

            $tagOptions = [];
            foreach ($tags as $tag) {
                $tagOptions[$tag->id] = $tag->name;
            }
            return $table

    Tables\Filters\SelectFilter::make('tags')
                    ->multiple()
                    ->options($tagOptions)
                    ->label('Tags')
                    ->placeholder('Filter by Tags'),
                ])

    Tables\Filters\SelectFilter::make('tags')
                    ->multiple()
                    ->options($tagOptions)
                    ->label('Tags')
                    ->placeholder('Filter by Tags'),
                ])

so the query should look something like this

SELECT
  count(*) AS aggregate
FROM
  task_tag
WHERE
  (tag_id IN (2))

SELECT
  count(*) AS aggregate
FROM
  task_tag
WHERE
  (tag_id IN (2))

Any tips on how i would go on with implementing this?

awcodes•13mo ago

this is wrong ->relationship('task_id', 'tag_id'). task_id should be the name of the relationship, not the column name.

kokpithuaOP•13mo ago

Ok but i think the issue stems from the fact that the filter is trying to select a value from the task table when instead it should do so from the table dedicated to their many to many relation task_tag. Where in the resource.php do i change the table from which te data is being taken from

awcodes•13mo ago

sounds like your relationship might not be setup correctly.

kokpithuaOP•13mo ago

I dont think so because in other cases where i display the data the relation is set up properly and its still displayed its here when i try to filter Because if i convert it to an array i get back the tag and the id of the task it belongs to

awcodes•13mo ago

not sure then, but the method signature is ->relationship(name, titleAttribute) and if 'task_id' is the name of your relationship then something sounds off to me. Sorry.

kokpithuaOP•13mo ago

Maybe I haven't really explained it properly

        Schema::create('task_tag', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('task_id');
            $table->unsignedBigInteger('tag_id');

            $table->foreign('task_id')->references('id')->on('tasks')->onDelete('cascade');
            $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');
        });
    }

        Schema::create('task_tag', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('task_id');
            $table->unsignedBigInteger('tag_id');

            $table->foreign('task_id')->references('id')->on('tasks')->onDelete('cascade');
            $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');
        });
    }

this is the relation and each of the models uses the return $this->belongsToMany()

awcodes•13mo ago

what is the name of the relationship on your model class

kokpithuaOP•13mo ago

//Tag model
public function tasks()
    {
        return $this->belongsToMany(Task::class);
    }
//Task Model
public function tags()
    {
        return $this->belongsToMany(Tag::class, 'task_tag', 'task_id', 'tag_id');
    }

//Tag model
public function tasks()
    {
        return $this->belongsToMany(Task::class);
    }
//Task Model
public function tags()
    {
        return $this->belongsToMany(Tag::class, 'task_tag', 'task_id', 'tag_id');
    }

in other scenarios where i display the tags beloning to the id everything works correctly

awcodes•13mo ago

->relationship('tags', 'title') ->relationship('tags', 'id') etc. if you are on the Task list page

Tables\Filters\SelectFilter::make('tags_filter')
    ->relationship('tags', 'id')
    ->multiple()
    ->options($tagOptions)
    ->label('Tags')
    ->placeholder('Filter by Tags')
])

Tables\Filters\SelectFilter::make('tags_filter')
    ->relationship('tags', 'id')
    ->multiple()
    ->options($tagOptions)
    ->label('Tags')
    ->placeholder('Filter by Tags')
])

kokpithuaOP•13mo ago

Now it worked. Thank you very much I geniunely appreciate it was struggling with this for like 2 hours

Gaming

Programming

Filtering many to many relations