Illuminate \ Database \ QueryException (42000) SQLSTATE[42000]: Syntax error or access violation

Question

trying to execute the following query on the FriendController

$friends = Auth::User()->friends;

and that is the friends function on the User model

public function friends()
{
    return $this->belongsToMany('App\Friend', 'friends', 'user_id', 'friend_id');
}

but on hitting the route i get the following error

Illuminate \ Database \ QueryException (42000) SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'friends' (SQL: select friends.*, friends.user_id as pivot_user_id, friends.friend_id as pivot_friend_id from friends inner join friends on friends.id = friends.friend_id where friends.user_id = 2)

Check your models User.php and Friends.php to see if they are not both pointing to the same table in the protected $table attribute. — Anwar
Possible duplicate of SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias on relationship — Devon
@Devon i understanded the concept but i don't know how to do that in the eloqunt ? — Islam Mansour

Devon Devon · Accepted Answer · 2018-07-16T14:38:16

Pivot tables should almost never have a model. If you're trying to use friends as a pivot table for two users, then the relationship should be referencing App\User, not App\Friend (or static which represents the current class).

public function friends()
{
    return $this->belongsToMany(static::class, 'friends', 'user_id', 'friend_id');
}

You'd want to return a collection of users, not "friends" since friend isn't an entity, it's a relationship.

Illuminate \ Database \ QueryException (42000) SQLSTATE[42000]: Syntax error or access violation

1 Answers