Laravel Nova Column in where clause is ambiguous/Unknown column in where clause

Question

Laravel Version: 5.8
Nova Version: 2.0.0
PHP Version: 7.2.1
Operating System and Version: Max OSX 10.13.6
Browser type and version: Firefox 66.0.3

Description:

Hi, I have 2 models relevant to this bug; Event and TimeSlot. They have a BelongsToMany relationship with each other.

I've created an index query so that only the time slots associated with the available events are retrieved.

The problem is that if I use return $query->whereIn('id',$time_slot_ids); I get a message on the Event entry that says SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous.

Screen Shot 2019-04-12 at 13 47 09

If I instead use return $query->whereIn('time_slot_id',$time_slot_ids); the query works on the Event and returns the attached time slots correctly. However this in turn leads to an error message on the Time Slots page which reads SQLSTATE[42S22]: Column not found: 1054 Unknown column 'time_slot_id' in 'where clause'.

Screen Shot 2019-04-12 at 13 47 49

My full block of code;

/**
     * 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)
    {

        if(auth()->user()->userLevel->level == 'Group') {

            $time_slot_ids = [];

            foreach(auth()->user()->group->events as $event) {

                foreach($event->timeSlots as $timeSlot) {

                    $time_slot_ids[] = $timeSlot->id;

                }

            }

            return $query->whereIn('time_slot_id',$time_slot_ids);

        }

    }

Steps To Reproduce:

Create a User, UserLevel, Group, Event and Time Slot model and accompanying Nova resource.

User belongs to a User Level. User belongs to a Group. User belongs to many Events. User belongs to many Time Slots.

User Level has many Users.

Group has many Users. Group has many Events.

Event belongs to a Group. Event belongs to many Users. Event belongs to many Time Slots.

Time Slot belongs to many Events. Time Slot belongs to many Users.

Attempt to create an index query where only the time slots attached to the authenticated user's group's events are returned.

Am I doing something wrong?

Here's the full list of queries executed before I get the first error;

We'd have to see the full query build for that, but for the first one, you have to specify which table the id is coming from on the and `id` in (...), and the second it's trying to find a time_slot_id in the table time_slots. — aynber
I understand that. But for the first one, why isn't Laravel/Laravel Nova automatically looking in the time_slots table when I have created the index query in TimeSlot.php in the app/Nova directory? — Jordan D
Because of the way it's building the query. You don't show the rest of the query build, so it's hard to tell, but it's an inner join instead of a relationship search, so you have to tell mysql which table the columns belong to. — aynber
OK, I've provided the full list of queries for the first error at the bottom of my question now. Does that help? How do you tell mysql which table the column belongs to when hooking onto an Eloquent query in Laravel? From Laravel (laravel.com/docs/5.8/eloquent): "By convention, the "snake case", plural name of the class will be used as the table name unless another name is explicitly specified" — Jordan D
You kind of have to know yourself, when using join queries. It's less of a Laravel thing and more of an SQL thing. When using one table, it's not an issue. But when you have two or more tables, and you're querying a column that exists in more than one table (such as ID), you have to tell MySQL which table that column belongs to — aynber

aynber aynber · Accepted Answer · 2019-04-12T14:00:36

When querying multiple tables at once, you have to specify the table names for common column names. So when querying the id, use this:

return $query->whereIn('time_slots.id',$time_slot_ids);

Laravel Nova Column in where clause is ambiguous/Unknown column in where clause

Description:

Steps To Reproduce:

1 Answers