filterable() doesn't include table name = Column 'xxx' in where clause is ambiguous

[arjankapteijn]

• Laravel Version: v9.47.0
• Nova Version: v4.20.2
• PHP Version: v8.1.13

Description:

When using the filterable() on a field the SQL generated doesn't include the table name. This triggers an 'SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'xxx' in where clause is ambiguous' when an inners join is applied through the indexQuery().

Example:

<?php

namespace App\Nova\Models;

use Laravel\Nova\Fields\DateTime;
use Laravel\Nova\Fields\ID;
use Laravel\Nova\Http\Requests\NovaRequest;

class Contact extends Resource
{
    /**
     * The model the resource corresponds to.
     *
     * @var string
     */
    public static $model = \App\Models\Contact::class;

    /**
     * Get the fields displayed by the resource.
     *
     * @param  \Laravel\Nova\Http\Requests\NovaRequest  $request
     * @return array
     */
    public function fields(NovaRequest $request)
    {
        return [
            ID::make(__('app.id'), 'id')
                ->sortable(),

            DateTime::make(__('app.created_at'), 'created_at')
                ->readonly()
                ->sortable()
                ->exceptOnForms()
                ->filterable(),
        ];
    }

    /**
     * Build an "index" query for the given resource.
     *
     * @param  \Laravel\Nova\Http\Requests\NovaRequest  $request
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public static function indexQuery(NovaRequest $request, $query)
    {
        $query
            ->select('contacts.*')
            ->join('projects', 'contacts.project_id', '=', 'projects.id')
            ->whereIntegerInRaw('projects.company_id', $request->user()->companies->pluck('id'));

    }
}

Returns (when applying a filter) SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous;

SELECT 
    `contacts`.*
FROM
    `contacts`
        INNER JOIN
    `projects` ON `contacts`.`project_id` = `projects`.`id`
WHERE
    DATE(`created_at`) >= 2023 - 01 - 01
        AND `contacts`.`deleted_at` IS NULL
ORDER BY `contacts`.`id` DESC
LIMIT 26 OFFSET 0

When replacing DATE(created_at) with DATE(contacts.created_at) the error is fixed. I also noticed that the date format is off but that could be an issue on my side.

[crynobone]

You need to manually define filterable callback: https://nova.laravel.com/docs/4.0/resources/fields.html#filterable-fields

[crynobone]

But honestly I would just skip join and use something like below,

   /**
     * Build an "index" query for the given resource.
     *
     * @param  \Laravel\Nova\Http\Requests\NovaRequest  $request
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public static function indexQuery(NovaRequest $request, $query)
    {
        $query->whereIn(
            'project_id',
            \App\Models\Project::select(['id'])->whereIntegerInRaw('projects.company_id', $request->user()->companies->pluck('id'))
        );
    }

Reference: https://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in

[arjankapteijn]

Hi @crynobone,

Thanks for your input! This works in the company/project situatie; but gets more difficult if there are more tables between them. I fixed it by useing the filterable callback for now but I will look into this to compare the performance!

[smartens80]

How did you get it to work with the 'FROM' and 'TO' inputs that a user can filter with? These 2 filter inputs are automatically created by Nova 4 when using filterable() on a Date column.

Date::make('Date', 'created_at')
    ->sortable()
    ->displayUsing(fn ($value) => $value ? $value->format('Y-m-d') : '')
    ->filterable(function ($request, $query, $value, $attribute) {
        $query->where("orders.{$attribute}", '>=', $value);
    });

The above will only work for the 'FROM' date, but how can I tell if the user is choose the 'TO' date?