QUery with 2 right joins make duplicate values

Hello i have this code that works well unless i add different value to the same existing user, this is the query:

 $userId = auth()->id();

        $contactsAssignedCars = CarBorrow::query()
            ->select([
                'users.id as id',
                'users.first_name as first_name',
                'users.last_name as last_name',
                DB::raw("IF (car_id = '{$carId}', true, false) as assigned")
            ])
            ->rightJoin('user_contacts', 'borrowed_to_user_id', 'user_contacts.contact_user_id')
            ->rightJoin('users', 'user_contacts.contact_user_id', '=', 'users.id')
            ->where('user_contacts.main_user_id', $userId)
            ->get();

This is the duplicate result:

    "data": [
        {
            "id": 3,
            "assigned": false
        },
        {
            "id": 4,
            "assigned": false
        },
        {
            "id": 4,
            "assigned": true
        },
        {
            "id": 5,
            "assigned": false
        },
        {
            "id": 6,
            "assigned": true
        }
    ],

as you can see ID 4 is 2 times, i did groupBy and Distinct but still doesnt work.

LaryAI

3 years ago

Level 58

The issue is caused by the right joins returning multiple rows for the same user if they have multiple contacts or borrowed cars. One solution is to use a subquery to get the latest borrowed car for each user, and then join that with the user_contacts table. Here's an example:

$userId = auth()->id();

$latestBorrows = DB::table('car_borrows')
    ->select('borrowed_to_user_id', DB::raw('MAX(id) as latest_id'))
    ->groupBy('borrowed_to_user_id');

$contactsAssignedCars = DB::table('user_contacts')
    ->select([
        'users.id as id',
        'users.first_name as first_name',
        'users.last_name as last_name',
        DB::raw("IF (car_borrows.car_id = '{$carId}', true, false) as assigned")
    ])
    ->joinSub($latestBorrows, 'latest_borrows', function ($join) {
        $join->on('user_contacts.contact_user_id', '=', 'latest_borrows.borrowed_to_user_id')
            ->join('car_borrows', function ($join) {
                $join->on('latest_borrows.latest_id', '=', 'car_borrows.id');
            });
    })
    ->join('users', 'user_contacts.contact_user_id', '=', 'users.id')
    ->where('user_contacts.main_user_id', $userId)
    ->get();

This code uses a subquery to get the latest borrowed car for each user, and then joins that with the user_contacts table. The IF statement is moved inside the select statement, and uses the car_borrows table instead of the CarBorrow model. Finally, the rightJoin calls are replaced with joinSub and join calls to join the subquery and other tables.

Note that this code assumes that the car_borrows table has an id column that can be used to get the latest borrowed car for each user. If this is not the case, you may need to modify the subquery to use a different column or combination of columns to get the latest borrowed car.

Please or to participate in this conversation.