mysqllaraveldatabase-relations

How to get users information that liked each other using Laravel query builder


I have a query below that checks if the users like each other:

SELECT a.user_id, a.target_id 
FROM user_nices a
INNER JOIN user_nices b
ON a.user_id = b.target_id AND b.user_id = a.target_id

And I also have a users table where the user information are stored. And I would like also to get their information. I have created a function in modal in which id stands for the current user id.

public function getMatches($id){
    return \DB::table('user_nices')
            ->join("user_nices as b", function($q){
                $q->on("user_nices.user_id", "=", "b.target_id");
                $q->on("b.user_id", "=", "user_nices.target_id");
            })
            ->get();
}

My code for now retrieves data but without the users information and also should only returns the user's match user as well. How can I possibly attain it using Laravel's query builder join query?


Solution

  • Thanks for the inputs, I have finally do it this way, but if you have better solutions, I'm still open to it.

    public function getMatchPeople($uid)
    {
    
        return \DB::table('user_nices')
                ->join("user_nices as b", function($q){
                    $q->on("user_nices.user_id", "=", "b.target_id");
                    $q->on("b.user_id", "=", "user_nices.target_id");
                })
                ->join('users', function($q){
                    $q->on('users.id',"=", "user_nices.target_id");
                })
                //to only get users details who likes current user
                ->where("user_nices.user_id", '=', $uid)
                ->where("user_nices.target_id", '!=', $uid)
                ->selectRaw("user_nices.user_id as user_id, users.name as name,
                            user_nices.id as id, users.email as email, users.nickname as nickname ,user_nices.created_at as create_at")
                ->get();
    }