Ifch0o1 Ifch0o1 - 9 months ago 100
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) */
$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 Source

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.