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)

https://stackoverflow.com/questions/31059595/sqlstate42000-syntax-error-or-access-violation-1066-not-unique-table-alias-o — J. Doe, Jul 16 '18 at 13:46
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, Jul 16 '18 at 13:46
Possible duplicate of [SQLSTATE\[42000\]: Syntax error or access violation: 1066 Not unique table/alias on relationship](https://stackoverflow.com/questions/31059595/sqlstate42000-syntax-error-or-access-violation-1066-not-unique-table-alias-o) — Devon Bessemer, Jul 16 '18 at 13:59
@Devon i understanded the concept but i don't know how to do that in the eloqunt ? — Islam Mansour, Jul 16 '18 at 14:29
Have you created a pivot table? You need to specify the pivot table to use. — Devon Bessemer, Jul 16 '18 at 14:31
Yes, friends is the pivot table, it has 2 ids for friends users. — Islam Mansour, Jul 16 '18 at 14:33
@IslamMansour, ok I understand now. User is the entity, friends in the pivot table. See my answer. — Devon Bessemer, Jul 16 '18 at 14:39

Devon Bessemer · Accepted Answer · 2018-07-16T14:43:26.633

0

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.

edited Jul 16 '18 at 14:43

answered Jul 16 '18 at 14:38

Devon Bessemer

34,461
9
69
95

What returns null? – Devon Bessemer Jul 16 '18 at 14:43
i tried both solutions and pass it to the view then print it in json format, then it only gets null – Islam Mansour Jul 16 '18 at 14:45
belongsToMany should never return null, only an empty collection,so you must be doing something else wrong – Devon Bessemer Jul 16 '18 at 14:47
yes, i did something wrong, but now it worked, Thank you very much. – Islam Mansour Jul 16 '18 at 14:51

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

1 Answers1