Filament•6mo ago

Column 'name' in where clause is ambiguous

I have quite an extensive admin-panel with lots of columns and custom filters/queries. This works fine for most options, but when I try to search for things (and sometimes with sorting but that's an issue for later), I get the error as mentioned in the title. This is most likely caused by the fact that I have two tables with the column 'name': Patients and Practices, which makes SQL unsure about in what table to look for. Part of the Laravel SQL error:

...
  AND (
    EXISTS (
      SELECT
        *
      FROM
        `patients`
      WHERE
        `examinations`.`patient_id` = `patients`.`id`
        AND `name` LIKE % sudw %
        AND `patients`.`deleted_at` IS NULL
    )
    OR EXISTS (
      SELECT
        *
      FROM
        `practices`
        INNER JOIN `patients` ON `patients`.`practice_id` = `practices`.`id`
      WHERE
        `patients`.`id` = `examinations`.`patient_id`
        AND `name` LIKE % sudw %
        AND `practices`.`deleted_at` IS NULL
        AND `patients`.`deleted_at` IS NULL
    )
...

...
  AND (
    EXISTS (
      SELECT
        *
      FROM
        `patients`
      WHERE
        `examinations`.`patient_id` = `patients`.`id`
        AND `name` LIKE % sudw %
        AND `patients`.`deleted_at` IS NULL
    )
    OR EXISTS (
      SELECT
        *
      FROM
        `practices`
        INNER JOIN `patients` ON `patients`.`practice_id` = `practices`.`id`
      WHERE
        `patients`.`id` = `examinations`.`patient_id`
        AND `name` LIKE % sudw %
        AND `practices`.`deleted_at` IS NULL
        AND `patients`.`deleted_at` IS NULL
    )
...

My columns are defined as followed:

    public static function patient(): TextColumn
    {
        return self::createColumn('patient.name', 'Patient')
            ->searchable()
            ->sortable();
    }

    public static function practice(): TextColumn
    {
        return self::createColumn('practice.name')
            ->searchable()
            ->sortable()
            ->limit(25);
    }

    protected static function createColumn(string $name, ?string $label = null, bool $defaultHidden = false): TextColumn
    {
        return TextColumn::make($name)
            ->label($label)
            ->toggleable(Admin::isGlobalAdmin(), $defaultHidden)
            ->alignCenter();
    }

    public static function patient(): TextColumn
    {
        return self::createColumn('patient.name', 'Patient')
            ->searchable()
            ->sortable();
    }

    public static function practice(): TextColumn
    {
        return self::createColumn('practice.name')
            ->searchable()
            ->sortable()
            ->limit(25);
    }

    protected static function createColumn(string $name, ?string $label = null, bool $defaultHidden = false): TextColumn
    {
        return TextColumn::make($name)
            ->label($label)
            ->toggleable(Admin::isGlobalAdmin(), $defaultHidden)
            ->alignCenter();
    }

What could be a good way to solve this problem? Thanks in advance!

1 Reply

DrByte•6mo ago

Does adding specificity to declared relationships help? https://filamentphp.com/docs/3.x/panels/resources/global-search#globally-searching-across-multiple-columns

Global search - Panel Builder - Filament

Gaming

Programming

Column 'name' in where clause is ambiguous