Filament•7mo ago

Filtering by a related table column changes the table id

Hi, I'm following the Owners, Pets, Treatments demo and I'm having issues creating a simple toggle filter by owner's email for the Patients resource.

Tables\Filters\Filter::make('owner_email')
    ->label('Owner email is John Doe')
    ->toggle()
    ->baseQuery(function (Builder $query): Builder {
        return $query
            ->join('owners', 'patients.owner_id', '=', 'owners.id');
    })
    ->query(function (Builder $query): Builder {
        return $query
            ->where('owners.email', '=', 'john@doe.com');
    }),

Tables\Filters\Filter::make('owner_email')
    ->label('Owner email is John Doe')
    ->toggle()
    ->baseQuery(function (Builder $query): Builder {
        return $query
            ->join('owners', 'patients.owner_id', '=', 'owners.id');
    })
    ->query(function (Builder $query): Builder {
        return $query
            ->where('owners.email', '=', 'john@doe.com');
    }),

My problem is that after applying filter, the Patients table now uses the owner's id instead of the patients id. What I'm doing wrong?

Solution:

You are adding a join with a table that also has an ID. Try adding a select('your_table.*') via the table query

Jump to solution

6 Replies

Solution

Dennis Koch•7mo ago

You are adding a join with a table that also has an ID. Try adding a select('your_table.*') via the table query

Miguel García•7mo ago

like this?

Tables\Filters\Filter::make('owner_email')
    ->label('Owner email is John Doe')
    ->toggle()
    ->baseQuery(function (Builder $query): Builder {
        return $query
            ->select('patients.*')
            ->leftJoin('owners', 'patients.owner_id', '=', 'owners.id');
    })
    ->query(function (Builder $query): Builder {
        return $query
            ->where('owners.email', '=', 'john@doe.com');
    }),

Tables\Filters\Filter::make('owner_email')
    ->label('Owner email is John Doe')
    ->toggle()
    ->baseQuery(function (Builder $query): Builder {
        return $query
            ->select('patients.*')
            ->leftJoin('owners', 'patients.owner_id', '=', 'owners.id');
    })
    ->query(function (Builder $query): Builder {
        return $query
            ->where('owners.email', '=', 'john@doe.com');
    }),

Thanks @Dennis Koch , btw great export plugin

Dennis Koch•7mo ago

Thanks ☺️

Miguel García•6mo ago

Ouch, ... not that fast. It turns out that now the ids are showing as expected but when the filter is active and I try to use the search box (individual or not) a new error raises because the name is ambiguous. Any advice on how to fix it?

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous
SELECT
  count(*) AS aggregate
FROM
  `patients`
  LEFT JOIN `owners` ON `patients`.`owner_id` = `owners`.`id`
WHERE
  (`owners`.`email` = john@doe.com)
  AND (
    `name` LIKE % s %
    OR EXISTS (
      SELECT
        *
      FROM
        `owners`
      WHERE
        `patients`.`owner_id` = `owners`.`id`
        AND `name` LIKE % s %
    )
  )

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous
SELECT
  count(*) AS aggregate
FROM
  `patients`
  LEFT JOIN `owners` ON `patients`.`owner_id` = `owners`.`id`
WHERE
  (`owners`.`email` = john@doe.com)
  AND (
    `name` LIKE % s %
    OR EXISTS (
      SELECT
        *
      FROM
        `owners`
      WHERE
        `patients`.`owner_id` = `owners`.`id`
        AND `name` LIKE % s %
    )
  )

awcodes•6mo ago

Is this not a relationship? Why do you need the join? I think a SelectFilter with a relationship would handle this for you.

Miguel García•6mo ago

Ok, ok now I think I'm getting it. Modifying the base query is a bad idea, it would be better to use Eloquent's with and whereHas. sorry for the dumb question, the beginning is always the hardest.

Gaming

Programming

Filtering by a related table column changes the table id