Framework: [5.3] MySQL ambiguous column in belongsToMany() relation withPivot()

[shadoWalker89]

• Laravel Version: 5.3.30
• PHP Version: 5.6.15
• Database Driver & Version: MySQL 5.6.31

Description:

I have a Meeting model with the following relation.

    public function agendas()
    {
        return $this->belongsToMany(MeetingAgenda::class, 'meeting_agenda_pivot')
            ->withPivot(['id']);
    }

When i execute the following query

$meeting->agendas()->pluck('name', 'id')

i get the following error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous (SQL: select `name`, `id` from `meeting_agendas` inner join `meeting_agenda_pivot` on `meeting_agendas`.`id` = `meeting_agenda_pivot`.`meeting_agenda_id` where `meeting_agenda_pivot`.`meeting_id` = 60)

For now i get the data from the database with ->get() and then apply the pluck() on the Collection

But maybe as a fix, for the query builder, the pluck method should prepend the table name, if there is no table name specified.

[fernandobandeira]

Prepending the table name would force you to add table names when using pluck on a column of another table, so using your example:

    public function agendas()
    {
        return $this->belongsToMany(MeetingAgenda::class, 'meeting_agenda_pivot')
            ->withPivot(['id', 'otherColumn']);
    }

$meeting->agendas()->pluck('name', 'otherColumn');

Would throw something like:

column `meeting_agendas`.`otherColumn` doesn't exist

Not sure which one is worse, just pointing it out...

[themsaid]

But maybe as a fix, for the query builder, the pluck method should prepend the table name

If you're making a JOIN between multiple tables you'll need to specify which table you want to read the column from, and thus why we can't enforce a table name, you need to do that yourself in case you know the two tables has the same column name.

$meeting->agendas()->pluck('meeting_agendas.id');

[linux019]

it's a bug in Eloquent. Laravel must always append table aliases if JOINs are performed between tables. I don't understand why it's too hard to add prefixing of tables, it's a bit of work as 99% queries are used to get only meeting data $meeting->agendas()->pluck('name', 'id')
and I don't want to hardcode table names in models.

[adromil]

it's a bug in Eloquent. Laravel must always append table aliases if JOINs are performed between tables. I don't understand why it's too hard to add prefixing of tables, it's a bit of work as 99% queries are used to get only meeting data $meeting->agendas()->pluck('name', 'id')
and I don't want to hardcode table names in models.

I agree. Is this fixed or has any updates on Laravel6.x?

[adromil]

If you're making a JOIN between multiple tables you'll need to specify which table you want to read the column from, and thus why we can't enforce a table name, you need to do that yourself in case you know the two tables has the same column name.

$meeting->agendas()->pluck('meeting_agendas.id');

Let's say I have setup the inverse of a hasMany relationship on my model, this mean I should include table name prefixes on every column I need?
ie:

$comments = Comment::with('member')->select('comments.id', 'comments.member_id', 'comments.title', 'comments.message', 'comments.tags', 'comments.created_at');

And if I need to add a special filter on certain conditions, it only means that it is required to do this:

 $comments->whereBetween('comments.created_at', [$start, $end]);

It seems tedious in a way (especially with long table names). Just saying.