How would I code this MySQL query in Laravel Eloquent?

Question

I'm trying to work with a semi-complex query (by my standards, anyway) but I can't think how to do it using the query builder or using eloquent relationships.

My relationships table is: user_id_1 | user_id_2 | status | action_user_id in the case of friends, action_user_id can be ignored and status = 1. There is only one row per relationship so if you want to select all relationships that belong to User 17 then you need to check both user_id_1 and user_id_2 because the id could be in either column. I followed the database structure from here: http://www.codedodle.com/2015/03/social-network-friends-relationship.html

The sort of query I'm trying to perform is:

SELECT users.*
FROM users
LEFT JOIN users_relationships AS r
    ON (
        users.id = r.user_id_1
        AND r.user_id_1 != $user_id
    ) OR (
        users.id = r.user_id_2
        AND r.user_id_2 != $user_id
    )
WHERE r.status = 1
    AND (
        r.user_id_1 = $user_id
        OR r.user_id_2 = $user_id
    )

Such a thing would be best in an eloquent relationship so it would be easy to perform: $user->friends and it would return the results of the above but I'm stuck in all directions.

My best attempt unfortunately not using relationship is this:

User::with('profile')->join('user_relationships as r', function($join) use ($user_id) {
    $join->on(DB::raw('( users.id = r.user_id_1 AND r.user_id_1 != ? )', [$user_id]), DB::raw(''), DB::raw(''));
    $join->orOn(DB::raw('( users.id = r.user_id_2 AND r.user_id_2 != ? )', [$user_id]), DB::raw(''), DB::raw(''));
})
->where('r.status', 1)
->where(function($query) use ($user_id) {
    $query->where('r.user_id_1', $user_id)
        ->orWhere('r.user_id_2', $user_id);
})
->get(['users.*']);

This however gives me errors relating to the parameters:

SQLSTATE[HY093]: Invalid parameter number (SQL: select `users`.* from `users` inner join `user_relationships` as `r` on ( users.id = r.user_id_1 AND r.user_id_1 != 1 ) or ( users.id = r.user_id_2 AND r.user_id_2 != 30 ) where `users`.`deleted_at` is null and `r`.`status` = 30 and (`r`.`user_id_1` = ? or `r`.`user_id_2` = ?))

I'm not sure how to do what I want to do.

Answer 1

The problem is here:

DB::raw('( users.id = r.user_id_1 AND r.user_id_1 != ? )', [$user_id])

DB::raw() doesn't take a second parameter for bindings. Use ->on()->where() instead (thanks to user4621032):

->leftJoin('users_relationships AS r', function($join) use($user_id) {
    $join->on('users.id','=','r.users_id_1')->where('r.users_id_1','!=',$user_id)
         ->orOn('users_id','=','r.users_id_2')->where('r.users_id_2','!=',$user_id);
})

So to setup your ->friends() function as desired, you can use:

public function friends()
{
    $user_id = $this->id;
    return self::with('profile')
        ->leftJoin('users_relationships AS r', function($join) use($user_id) {
            $join->on('users.id','=','r.users_id_1')->where('r.users_id_1','!=',$user_id)
                 ->orOn('users_id','=','r.users_id_2')->where('r.users_id_2','!=',$user_id);
        })
        ->where('r.status', 1)
        ->where(function($query) use ($user_id) {
            $query->where('r.user_id_1', $user_id)
            ->orWhere('r.user_id_2', $user_id);
        })
        ->get(['users.*']);
}