cicagorio
cicagorio
PostsComments
FFilament
Created by cicagorio on 4/2/2024 in #❓┊help
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.
4 replies