F
Filament•13mo ago
Oddman

Little bit confused by table filters

I'm trying to setup a select dropdown filter, that when selected then needs to join onto another table and find relevant records. If I use the baseQuery method, I can get the query to work, but it's applying, even when the filter isn't set to default. I have not been able to figure out another way to do it. My select filter needs to query a column on a related record, not the main records in the table.
Solution:
```php ->query(function(EloquentBuilder $query, $state) { $query->when(is_array($state) && !empty($state['values']), fn($query) => $query->whereHas('contributors', fn($query) => $query->select('contributors.id')...
Jump to solution
62 Replies
Oddman
OddmanOP•13mo ago
Some additional info, this is my query code now:
->query(fn (EloquentBuilder $query, $state): EloquentBuilder => $query->join('contributors', 'contributors.article_id', 'articles.id')->where('contributors.user_id', $state))
This complains about contributors being missing from the from clause, yet I'm applying the join. This is the resulting query (which is wrong):
select count(*) as aggregate from "articles" where ("contributors"."user_id" = ?)
I haven't even selected an author yet, so I don't know why it's doing that... IT doens't appear to be applying the join, at all. I honestly have no idea.... I've tried 4 different approaches to get the query to behave as i need it to, stumped. Now it's not applying to the query at all... witaf is going on here.. This is my code: 
Tables\Filters\SelectFilter::make('author')
  ->options(User::author()->orderBy('name')->pluck('name', 'id'))
  ->native(false)
  ->searchable()
  ->indicator('Author')
  ->query(fn (EloquentBuilder $query, $state): EloquentBuilder => $query->hasAuthor($state['value']))
Tables\Filters\SelectFilter::make('author')
  ->options(User::author()->orderBy('name')->pluck('name', 'id'))
  ->native(false)
  ->searchable()
  ->indicator('Author')
  ->query(fn (EloquentBuilder $query, $state): EloquentBuilder => $query->hasAuthor($state['value']))
This is my hasAuthor scope: 
public function scopeHasAuthor(Builder $query, $authorId): void
{
    $query->join('contributors', function($join) use ($authorId) {
        $join->on('contributors.article_id', 'articles.id');
        $join->where('contributors.user_id', $authorId);
    });
}
public function scopeHasAuthor(Builder $query, $authorId): void
{
    $query->join('contributors', function($join) use ($authorId) {
        $join->on('contributors.article_id', 'articles.id');
        $join->where('contributors.user_id', $authorId);
    });
}
This is not being applied to the articles query, at all. Yet when I do a dd($authorId), it's showing the correctly selected value.
khaltis
khaltis•13mo ago
try wrapping your query modifier in a if(!empty($state['value'])) { ... } 
->query(function (Builder $query, array $state) {
    if (!empty($state['value'])) {
        $query->hasAuthor($state['value']);
    }
    return $query;
})
->query(function (Builder $query, array $state) {
    if (!empty($state['value'])) {
        $query->hasAuthor($state['value']);
    }
    return $query;
})
this should replace your 
->query(fn (EloquentBuilder $query, $state): EloquentBuilder => $query->hasAuthor($state['value']))
->query(fn (EloquentBuilder $query, $state): EloquentBuilder => $query->hasAuthor($state['value']))
Oddman
OddmanOP•13mo ago
Yeah but it's not doing anything regardless. You would expect the default query conditions to be applied, even if there's no state value.
khaltis
khaltis•13mo ago
can i see the whole $table
lazydog
lazydog•13mo ago
make sure to utilize the relationship in displaying on a table ex author.name In your filter use SelectFilter::make('author_id')
Oddman
OddmanOP•13mo ago
@denknows it's from a related table though. Below: 
public static function table(Table $table): Table
{
    return $table
        ->defaultSort('published_at', 'desc')
        ->columns([
            TextColumn::make('id')->sortable(),
            CuratorColumn::make('heroImage')->size(40),
            TextColumn::make('title')->searchable(),
            IconColumn::make('early_access')->boolean()->alignCenter(),
            IconColumn::make('pro_series')->boolean()->alignCenter(),
            IconColumn::make('published')->boolean()->alignCenter(),
            TextColumn::make('published_at')->sortable()->dateTime()->color(function(string $state) {
                if (!$state) return '';

                $dateTime = new Carbon($state);

                return $dateTime->lte(now()) ? 'success' : 'warning';
            }),
        ])
        ->filters([
            Tables\Filters\SelectFilter::make('published')->options([
                'in-progress' => 'In progress',
                'published' => 'Published',
            ])
                ->query(function(array $data, EloquentBuilder $query): EloquentBuilder {
                    return match ($data['value']) {
                        'in-progress' => $query->draft(),
                        'published' => $query->published(),
                        default => $query
                    };
                }),
            Tables\Filters\SelectFilter::make('author')
                ->options(User::author()->orderBy('name')->pluck('name', 'id'))
                ->native(false)
                ->searchable()
                ->indicator('Author')
                ->query(fn (EloquentBuilder $query, $state): EloquentBuilder => $query->hasAuthor($state['value']))
        ])
        ->persistFiltersInSession()
        ->actions([
            Tables\Actions\EditAction::make(),
        ])
        ->bulkActions([
            Tables\Actions\BulkActionGroup::make([
                Tables\Actions\DeleteBulkAction::make(),
            ]),
        ])
        ->emptyStateActions([
            Tables\Actions\CreateAction::make(),
        ]);
}
public static function table(Table $table): Table
{
    return $table
        ->defaultSort('published_at', 'desc')
        ->columns([
            TextColumn::make('id')->sortable(),
            CuratorColumn::make('heroImage')->size(40),
            TextColumn::make('title')->searchable(),
            IconColumn::make('early_access')->boolean()->alignCenter(),
            IconColumn::make('pro_series')->boolean()->alignCenter(),
            IconColumn::make('published')->boolean()->alignCenter(),
            TextColumn::make('published_at')->sortable()->dateTime()->color(function(string $state) {
                if (!$state) return '';

                $dateTime = new Carbon($state);

                return $dateTime->lte(now()) ? 'success' : 'warning';
            }),
        ])
        ->filters([
            Tables\Filters\SelectFilter::make('published')->options([
                'in-progress' => 'In progress',
                'published' => 'Published',
            ])
                ->query(function(array $data, EloquentBuilder $query): EloquentBuilder {
                    return match ($data['value']) {
                        'in-progress' => $query->draft(),
                        'published' => $query->published(),
                        default => $query
                    };
                }),
            Tables\Filters\SelectFilter::make('author')
                ->options(User::author()->orderBy('name')->pluck('name', 'id'))
                ->native(false)
                ->searchable()
                ->indicator('Author')
                ->query(fn (EloquentBuilder $query, $state): EloquentBuilder => $query->hasAuthor($state['value']))
        ])
        ->persistFiltersInSession()
        ->actions([
            Tables\Actions\EditAction::make(),
        ])
        ->bulkActions([
            Tables\Actions\BulkActionGroup::make([
                Tables\Actions\DeleteBulkAction::make(),
            ]),
        ])
        ->emptyStateActions([
            Tables\Actions\CreateAction::make(),
        ]);
}
What's even trickier about this, is that authors, are found via a hasManyThrough relationship, which isn't supported by Filament, so I couldn't get the relationship working, either. (as we have multiple authors on many of our articles)
awcodes
awcodes•13mo ago
Some people have had success with the has many deep plugin. https://github.com/staudenmeir/eloquent-has-many-deep
GitHub
GitHub - staudenmeir/eloquent-has-many-deep: Laravel Eloquent HasMa...
Laravel Eloquent HasManyThrough relationships with unlimited levels - GitHub - staudenmeir/eloquent-has-many-deep: Laravel Eloquent HasManyThrough relationships with unlimited levels
awcodes
awcodes•13mo ago
Never used it myself but might help.
Oddman
OddmanOP•13mo ago
Yeah I don't need that. It's a standard hasmanythrough relationship. I"m either doing something seriously wrong, or select filters on tables were never meant for this use case, because I cannot get this to work, it's been days now. I just don't understand why it's not applying the query. At all. Okay, at a point where I think there's a big bug here. My methods are being called (including my scopes), but nothing is being reflected in the actual queries. Yeah filament is really getting in the way here. For some reason it's expecting a certain kind of relationship here - and that's just not going to work.
awcodes
awcodes•13mo ago
Dumb question, do you have a column for the author on table?
Oddman
OddmanOP•13mo ago
I do not - it's a related table, via has many through. It's actually "authors"
awcodes
awcodes•13mo ago
It seems like the table isn’t eager loading the relationship and therefore the filter can’t run against it. I’m probably totally wrong, but it’s my immediate thought. This area isn’t my strong point. Just trying to throw out possibilities.
Oddman
OddmanOP•13mo ago
It's a good theory - but, when I do it like this: 
Tables\Filters\SelectFilter::make('author')
                    ->options(User::author()->orderBy('name')->pluck('name', 'id'))
                    ->preload()
                    ->native(false)
                    ->searchable()
                    ->indicator('Author')
                    ->query(function(EloquentBuilder $query, $state) {
                        if (!empty($state['value'])) {
                            $query->hasAuthor($state['value']);
                        }
                    })
Tables\Filters\SelectFilter::make('author')
                    ->options(User::author()->orderBy('name')->pluck('name', 'id'))
                    ->preload()
                    ->native(false)
                    ->searchable()
                    ->indicator('Author')
                    ->query(function(EloquentBuilder $query, $state) {
                        if (!empty($state['value'])) {
                            $query->hasAuthor($state['value']);
                        }
                    })
There are no errors - but the query is never applied. I can even dd() and see it calling the query parameters. It's doing some check elsewhere that's preventing the query being executed, which makes no sense to me. tableFilters[author][value]=3 - in the query.
awcodes
awcodes•13mo ago
Have you tried doing it with an actual relationship for the select.? If it’s a has many then you’ll probably need to make it multiple so it can handle it. https://filamentphp.com/docs/3.x/tables/filters#relationship-select-filters
Filters - Table Builder - Filament
awcodes
awcodes•13mo ago
I’m also not understanding the $state[‘value’] part. Typically state is the value.
Oddman
OddmanOP•13mo ago
I have, the relationsihp on select won't accept hasManyThrough relationships. Not for filters. There's no need for multiple - I don't need to select multiple authors, I only want to search one at a time. There's a real strict setup for this selectfilter I'm finding. Setting the options, and defining the query should be enough. But it's doing something else in the background that despite executing my query() method, it's not actually including it as part of the executed query for hte table.
awcodes
awcodes•13mo ago
I hear you, but multiple() is what allows the select to adjust its relationship query from belongsTo to HasMany.
Oddman
OddmanOP•13mo ago
Alrighty, I'll give it a go.
awcodes
awcodes•13mo ago
It could also be a limitation of select.js that is causing it to be that way too.
Oddman
OddmanOP•13mo ago
Okay, I got it workign with relationship using belongsToMany, but then the problem is how many results in returns due to the duplication. HasManyThrough really should be able to work - the results are exactly the same as a hasMany lol Hmmm, there's a new problem - it's only ever returning one article.
awcodes
awcodes•13mo ago
Hmm. It’s progress though. Lol.
Oddman
OddmanOP•13mo ago
I think this is a new brick wall if I'm being honest lol
awcodes
awcodes•13mo ago
I’m sure it works though. I just don’t have all the right steps to give you. Wish I did.
Oddman
OddmanOP•13mo ago
NO worries, cheers dude Yeah, another problem - it's not adding the table of the main model to the query for the id lookup in the filter, so I get an ambiguous id error 😮‍💨
Oddman
OddmanOP•13mo ago
Seems it's a known problem: https://github.com/filamentphp/filament/issues/8468 - another issue lost due to having to setup a repository.
GitHub
Filter by "hasManyThrough" not working · Issue #8468 · filamentphp/...
Package filament/filament Package Version v3.0.47 Laravel Version v10.22.0 Livewire Version No response PHP Version 8.2 Problem description This PR added the ability to search via "HasManyThro...
awcodes
awcodes•13mo ago
Yea. But we really need reproduction repos. It’s not feasible for use to set up relationships and seed them in the same way as seeing the error occur in your particular use case.
Oddman
OddmanOP•13mo ago
FS, I can't use the relationship for the select filter population as it doens't include authors that are yet to post... Yeah, I know - my point previously however has been to just do the fix instead (most the stuff I've found has been pretty small)
awcodes
awcodes•13mo ago
PRs are always better than issues. 😁
Oddman
OddmanOP•13mo ago
I've figured it out. The select filter is making an assumption on data that exists in the database, which is not what I want here. So now back at square 1 figuring out why my query isn't being applied.
awcodes
awcodes•13mo ago
If it doesn’t exist in the database then how can it filter, at least without a virtual column.
Oddman
OddmanOP•13mo ago
So two things on that - first thing is that having to query a many-many relationship for existing data, rather than the 50 or so author records I have, is considerably slower. Secondly, it's okay if data doesn't exist, if you search for that author and they have nothing, no biggie.
awcodes
awcodes•13mo ago
What if you put your options() as a callback and use ->preload() on the select.
Oddman
OddmanOP•13mo ago
I tried doing that with the relationship, didn't do anything. I can get everything to work, except the query being applied to the table query. And I have nfi why. If I use baseQuery, I can see my query changes applied, but then it's querying the wrong column.
awcodes
awcodes•13mo ago
So you have a custom table query and custom filter queries on top of that.?
Oddman
OddmanOP•13mo ago
that's what I"m working through atm
awcodes
awcodes•13mo ago
Sounds like they might be competing with each other at the eloquent level. You might need some when’s to conditionally apply one or the other. This is a guess obviously.
Oddman
OddmanOP•13mo ago
I need both, it seems - baseQuery to setup the join and condition, then query() to customise the where (ignore it) I have a sneaking suspicion this is why I'm seeing developers use whereHas (imho, a really bad habit to fall into)
awcodes
awcodes•13mo ago
It exists for a reason, even if you don’t like it.😁
Oddman
OddmanOP•13mo ago
I'm not actually sure I understand the reason. It's a far less performant approach to writing SQL queries for exists/counts. And often you can use some SQL wizardry to be stupid fast
awcodes
awcodes•13mo ago
Maybe you’re experiencing the reason. Lol.
Oddman
OddmanOP•13mo ago
hahaha, I don't think it was designed for filament 😛
awcodes
awcodes•13mo ago
Filament is defaulting to laravels builder. Not that I can’t guarantee it isn’t a bug.
Oddman
OddmanOP•13mo ago
Sigh, now looks like filament is adding a filler clause ffs
awcodes
awcodes•13mo ago
Granted I haven’t had to do this kind of functionality but I have only have problems when my relationships weren’t actually setup correctly.
Oddman
OddmanOP•13mo ago
My relationships are fine, works everywhere else. The issue here is an opinionated select filter
awcodes
awcodes•13mo ago
Not saying yours aren’t correct.
Oddman
OddmanOP•13mo ago
I just can't understand why it's not applying my custom query clauses, despite executing them.
awcodes
awcodes•13mo ago
That’s what makes me the think there’s a conflict in the query. Can you dd the rawSql() Either at the table query or the relationship query.
Oddman
OddmanOP•13mo ago
select * from "articles" where exists (select * from "contributors" where "articles"."id" = "contributors"."article_id" and 0 = 1) that 0=1 is applying when I am not applying my query condition (as values aren't available) PS. This step is the closest I've come. I can get it to filter now, but no filter = busted query
awcodes
awcodes•13mo ago
What is your base table query.?
Oddman
OddmanOP•13mo ago
Got it.
Solution
Oddman
Oddman•13mo ago
->query(function(EloquentBuilder $query, $state) {
                        $query->when(is_array($state) && !empty($state['values']), fn($query) =>
                            $query->whereHas('contributors', fn($query) =>
                                $query->select('contributors.id')
                                    ->whereIn('contributors.user_id', $state['values'])));
->query(function(EloquentBuilder $query, $state) {
                        $query->when(is_array($state) && !empty($state['values']), fn($query) =>
                            $query->whereHas('contributors', fn($query) =>
                                $query->select('contributors.id')
                                    ->whereIn('contributors.user_id', $state['values'])));
Oddman
OddmanOP•13mo ago
Lessons: 1. Applying query methods wherever you want won't work, shit is getting filtered -somewhere-. 2. Use baseQuery to do things like joins 3. Use query to do things like custom clauses Now that I know this, I can go and clean this up so it's performant. Thanks for trying to work through it with me, @awcodes
Oddman
OddmanOP•13mo ago
I ended up sorting it out. It was a combination of ignorance plus certainly some bugs on the Filament side - but I found a solution in the end. Same problem?
Dan Harrin
Dan Harrin•13mo ago
if there a bugs, report them or they will not get solved i guess 🤷‍♂️
Oddman
OddmanOP•13mo ago
It takes far too long to setup a separate repository and get all the stuff working and setup to replicate, and you can't report bugs without it. The approach is a mistake - some bugs are so simple that just replicating the code in a project showcases it. I get why it's there, but would be better to have a follow-up to ask for more info if it's required. In my case, it does require a repo, as it's complicated.
Dan Harrin
Dan Harrin•13mo ago
if youve ever maintained open source youll know the frustration of attempting to replicate even the simplest of bugs and wasting time when we are expected to do free work on behalf of the community, we want people to make an effort
Oddman
OddmanOP•13mo ago
Oh I get it, I do run a fairly good one - but my project is also much simpler, so I work with the authors to help replicate, or ask for more info. I just think it would be better to accept the issues with a template, and if more info is really required, can ask for it. If none is forthcoming then can close it down after a period (via an automation)
Dan Harrin
Dan Harrin•13mo ago
yeah, well if you have a look at the number of issues we have then youll know that it would take a very long time to do that, and working asynchronously is much more efficient than waiting on authors to get their shit together i honestly dont want the issue open if i cant immediately jump on it and action it when the time comes
Oddman
OddmanOP•13mo ago
No doubt, but at least you'd be aware of the issues. I've had 4 now, all 4 haven't been easy to step through.
Dan Harrin
Dan Harrin•13mo ago
anyway, someone will handle it when it is open with the required info
Oddman
OddmanOP•13mo ago
I'd rather pay, as I am - so i can stay focused on my outputs 🙂
Want results from more Discord servers?
Add your server