Ifch0o1 Ifch0o1 - 5 days ago 6
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

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.

Comments