Laravel 5 Eloquent relation "Has Many Through" SQL Exception

Question

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) */
$request->user()->tasks()->get();

/* 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:

SQLSTATE[23000]:
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 id must be defined.

Note: The ListModel class uses protected $table = 'lists'. the List 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.

Answer 1

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.

Solution:

I changed this line:

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

To:

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

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