F
Filament•2y ago
kokpithua

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
toeknee•2y ago
add relationship to the filter?
kokpithua
kokpithuaOP•2y 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
toeknee•2y ago
Try placing relationship at the bottom
kokpithua
kokpithuaOP•2y 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
awcodes•2y ago
this is wrong ->relationship('task_id', 'tag_id'). task_id should be the name of the relationship, not the column name.
kokpithua
kokpithuaOP•2y 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
awcodes•2y ago
sounds like your relationship might not be setup correctly.
kokpithua
kokpithuaOP•2y 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
awcodes•2y 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.
kokpithua
kokpithuaOP•2y 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
awcodes•2y ago
what is the name of the relationship on your model class
kokpithua
kokpithuaOP•2y 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
awcodes•2y 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')
])
kokpithua
kokpithuaOP•2y ago
Now it worked. Thank you very much I geniunely appreciate it was struggling with this for like 2 hours

Did you find this page helpful?