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

joining non-distinct will break table functionality.
You can use the selected year from dropdown and instead of joining use the

whereHas

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[...])
            )
    )
)

Jump to solution

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')
                        )
                    )

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)

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

joining non-distinct will break table functionality.
You can use the selected year from dropdown and instead of joining use the

whereHas

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[...])
            )
    )
)

Jump to solution

Column not found with table filter and joins

Column not found with table filter and joins

Similar Threads

Similar Threads

Similar Threads