F
Filamentcicagorio

Column not found with table filter and joins

Hello everyone, I am working on my first project with filament and I have hit a brick wall. I'm trying to use SelectFilter with a query that has a join in it, but I can't seem to get it to work.
SelectFilter::make('Has unpaid')
->query(
fn (array $data, Builder $query): Builder =>
$query->select('persons.*')->when(
$data['value'],
fn (Builder $query, $value): Builder => $query->leftJoin('transactions', function ($join) use ($data) {
$join->on('transactions.person_id', '=', 'person.id')
->where('transactions.transaction_purpose_id', '=', $data["value"]);
})->whereNull('transactions.transaction_purpose_id')
)
)
SelectFilter::make('Has unpaid')
->query(
fn (array $data, Builder $query): Builder =>
$query->select('persons.*')->when(
$data['value'],
fn (Builder $query, $value): Builder => $query->leftJoin('transactions', function ($join) use ($data) {
$join->on('transactions.person_id', '=', 'person.id')
->where('transactions.transaction_purpose_id', '=', $data["value"]);
})->whereNull('transactions.transaction_purpose_id')
)
)
So transactions table can have multple transactions by a person, which are payments for years. I want to create a filter dropdown that will enable to find everyone who hasn't paid for the selected year. The error I keep getting is:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'transactions.transaction_purpose_id' in 'where clause'

select
count(*) as aggregate
from
`persons`
where
(`transactions`.`transaction_purpose_id` is null)
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'transactions.transaction_purpose_id' in 'where clause'

select
count(*) as aggregate
from
`persons`
where
(`transactions`.`transaction_purpose_id` is null)
When I open the base page without any filters applied it works fine. I also tried getting and running the query generated by this code and it works fine, I can run it on the DB directly without any issues. The generated SQL from the Select eloquent query looks like this:
select
`persons`.*
from
`persons`
left join `transactions` on `transactions`.`person_id` = `persons`.`id`
and `transactions`.`transaction_purpose_id` = 3
where
`transactions`.`transaction_purpose_id` is null;
select
`persons`.*
from
`persons`
left join `transactions` on `transactions`.`person_id` = `persons`.`id`
and `transactions`.`transaction_purpose_id` = 3
where
`transactions`.`transaction_purpose_id` is null;
However the error looks completely different and I suspect it has something to do with pagination, but I am not sure how to solve this. Pls help.
Solution
M
<Λmir>17d ago
joining non-distinct will break table functionality. You can use the selected year from dropdown and instead of joining use the whereHas method to filter persons. Something like this:
->query(fn (array $data, Builder $query): Builder =>
$query->when(
$data['value'],
fn (Builder $query, $value): Builder =>
$query->whereHas('transactions', fn (Builder $query) =>
$query->where('transactions.year', $data[...])
)
)
)
->query(fn (array $data, Builder $query): Builder =>
$query->when(
$data['value'],
fn (Builder $query, $value): Builder =>
$query->whereHas('transactions', fn (Builder $query) =>
$query->where('transactions.year', $data[...])
)
)
)
C
cicagorio17d ago
Thank you very much for the reply and the clarification! I just needed to replace whereHas with whereDoesntHave in your code but works perfectly now 🎉