Filament•9mo ago

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•9mo 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•9mo 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•9mo 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•9mo ago

can i see the whole $table

lazydog•9mo ago

make sure to utilize the relationship in displaying on a table ex author.name In your filter use SelectFilter::make('author_id')

Oddman•9mo 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•9mo 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•9mo ago

Never used it myself but might help.

Oddman•9mo 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•9mo ago

Dumb question, do you have a column for the author on table?

Oddman•9mo ago

I do not - it's a related table, via has many through. It's actually "authors"

awcodes•9mo 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•9mo 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•9mo 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•9mo ago

I’m also not understanding the $state[‘value’] part. Typically state is the value.

Oddman•9mo 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•9mo ago

I hear you, but multiple() is what allows the select to adjust its relationship query from belongsTo to HasMany.

Oddman•9mo ago

Alrighty, I'll give it a go.

awcodes•9mo ago

It could also be a limitation of select.js that is causing it to be that way too.

Oddman•9mo 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•9mo ago

Hmm. It’s progress though. Lol.

Oddman•9mo ago

I think this is a new brick wall if I'm being honest lol

awcodes•9mo ago

I’m sure it works though. I just don’t have all the right steps to give you. Wish I did.

Oddman•9mo 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•9mo 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•9mo 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•9mo 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•9mo ago

PRs are always better than issues. 😁

Oddman•9mo 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•9mo ago

If it doesn’t exist in the database then how can it filter, at least without a virtual column.

Oddman•9mo 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•9mo ago

What if you put your options() as a callback and use ->preload() on the select.

Oddman•9mo 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•9mo ago

So you have a custom table query and custom filter queries on top of that.?

Oddman•9mo ago

that's what I"m working through atm

awcodes•9mo 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•9mo 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•9mo ago

It exists for a reason, even if you don’t like it.😁

Oddman•9mo 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•9mo ago

Maybe you’re experiencing the reason. Lol.

Oddman•9mo ago

hahaha, I don't think it was designed for filament 😛

awcodes•9mo ago

Filament is defaulting to laravels builder. Not that I can’t guarantee it isn’t a bug.

Oddman•9mo ago

Sigh, now looks like filament is adding a filler clause ffs

awcodes•9mo 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•9mo ago

My relationships are fine, works everywhere else. The issue here is an opinionated select filter

awcodes•9mo ago

Not saying yours aren’t correct.

Oddman•9mo ago

I just can't understand why it's not applying my custom query clauses, despite executing them.

awcodes•9mo 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•9mo 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•9mo ago

What is your base table query.?

Oddman•9mo ago

Got it.

Solution

Oddman•9mo 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•9mo 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•8mo 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•8mo ago

if there a bugs, report them or they will not get solved i guess 🤷‍♂️

Oddman•8mo 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•8mo 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•8mo 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•8mo 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•8mo 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•8mo ago

anyway, someone will handle it when it is open with the required info

Oddman•8mo ago

I'd rather pay, as I am - so i can stay focused on my outputs 🙂

Gaming

Programming

Little bit confused by table filters