Ifch0o1 Ifch0o1 - 7 months ago 91
SQL Question

Laravel 5 Eloquent relation "Has Many Through" SQL Exception

Hello. Yesterday I spend more than 6 hours to understand how the ORM relations working in laravel 5. But I am not very familiar with them yet.

After hundred errors, test, researches, etc... I made a part of my relations to work correctly.

Relations between the models:

/* User.php (Model) */
public function lists() {
return $this->hasMany('App\ListModel');
public function tasks() {
return $this->hasManyThrough('App\Task', 'App\ListModel',
'user_id', 'list_id');

/* ListModel.php */
protected $table = 'lists';

public function user() {
return $this->belongsTo('\App\User', 'list_id');
public function tasks() {
return $this->hasMany('App\Task', 'list_id');

/* Task.php (Model) */
public function listModel() {
return $this->belongsTo('App\ListModel', 'list_id');

What working in an controller:

/* Returning all tasks (I have only one user yet. All tasks belongs to him) */

/* Working - Returning the list | lists */
$list = $request->user()->lists()->where('id', 'some-id')->first();
$lists = $request->user()->lists()->get();

Where the exception occurs

/* Next line throws the SQL Exception */
$task = $request->user()->tasks()->where('id', '5')->first();

The exception:

Integrity constraint violation: 1052
Column 'id' in where clause is ambiguous
(SQL: select `tasks`.*, `lists`.`user_id` from `tasks`
inner join `lists` on `lists`.`id` = `tasks`.`list_id` where
`tasks`.`deleted_at` is null and `lists`.`deleted_at` is null
and `lists`.`user_id` = 1 and `id` = 5 limit 1)
/* The problem in where clause ^^^^^^^ */

The SQL query generated by laravel is not working. I think the table of
must be defined.

Note: The
class uses
protected $table = 'lists'
. the
word is native
in PHP (T_LIST). This table name change made my days sick. Laravel's documentation about "ORM custom keys" is somehow small.

Thanks for your time.


The problem actually was is in the QueryBuilder.

$request->user()->tasks() Returns QueryBuilder
I am not sure how exactly it works. But I think the where() function adds additional SQL.


I changed this line:

$task = $request->user()->tasks()->where('id', '5')->first();


$task = $request->user()->tasks()->where('tasks.id', '5')->first();

Where tasks is the table in the DB and id is the column.