Many to many query builder laravel

Question

Here is my query code:

$apartments = DB::table('apartments')
    ->when($request->filled('wifi'), function ($query) {
        $query->join('apartment_service' , function($join){
            $join -> on('apartments.id' , '=' , 'apartment_service.apartment_id')
            ->where('apartment_service.service_id' , '=' , '1');
        });
    })
    ->when($request->filled('pool'), function ($query) {
        $query->join('apartment_service' , function($join){
            $join -> on('apartments.id' , '=' , 'apartment_service.apartment_id')
            ->where('apartment_service.service_id' , '=' , '2');
        });
    })
    ->when($request->filled('sea'), function ($query) {
        $query->join('apartment_service' , function($join){
        $join -> on('apartments.id' , '=' , 'apartment_service.apartment_id')
        ->where('apartment_service.service_id' , '=' , '3');
        });
    })
    ->when($request->filled('Terrace'), function ($query)  {
        $query->join('apartment_service' , function($join){
        $join -> on('apartments.id' , '=' , 'apartment_service.apartment_id')
        ->where('apartment_service.service_id' , '=' , '5');
        });
    })
    ->when($request->filled('sauna'), function ($query)  {
        $query->join('apartment_service' , function($join){
        $join -> on('apartments.id' , '=' , 'apartment_service.apartment_id')
        ->where('apartment_service.service_id' , '=' , '6');
        });
    })
    ->get();
        return response()-> json($apartments);

where is my mistake with the pivot table?

If I select only one service the query works, but when I select two or more services the query doesn't work.

Please do not share information as images unless absolutely necessary. See: https://meta.stackoverflow.com/questions/303812/discourage-screenshots-of-code-and-or-errors, https://idownvotedbecau.se/imageofcode, https://idownvotedbecau.se/imageofanexception/. — AMC, Feb 20 '20 at 18:27
What do you mean "doesn't work"? Do you get an error or just empty result? Have you tried [viewing the SQL](https://laravel.com/docs/6.x/queries#debugging) to see if it makes sense? — miken32, Feb 20 '20 at 20:50
You should edit your question to remove the SQL tag, as you are not writing SQL, but add the PHP tag, since this is PHP! — miken32, Feb 20 '20 at 20:51

Kevin Bui · Answer 1 · 2020-02-21T02:56:44.710

I believe that your query doesn't work because it ends up to something like:

SELECT * FROM apartments
INNER JOIN apartment_service ON apartments.id = apartment_service.apartment_id
INNER JOIN apartment_service ON apartments.id = apartment_service.apartment_id
WHERE
AND apartment_service.service_id = 3
AND apartment_service.service_id = 5
AND apartment_service.service_id = 6

Its gonna fail because there's NO record on apartment_service table that have a single service_id field of all 3, 5 and 6!

Of course its gonna work with with a single when condition.

I can think of two solutions to solve your problem.

SOLUTON 1:

Lets rewrite your query from the controller:

class ApartmentsController extends Controller
{
    public function index()
    {
        return Apartment::join('apartment_service', 'apartments.id', '=', 'aparment_service.apartment_id')
            ->when($this->getServices(), function ($query) {
                return $query>whereIn('apartment_service.service_id', $this->getServices());
            })
            ->get();
    }

    protected function getServices()
    {
        $serviceIds = [];

        if (request()->filled('wifi')) {
            $serviceIds[] = 1;
        }

        if (request()->filled('pool')) {
            $serviceIds[] = 2;
        }

        if (request()->filled('sea')) {
            $serviceIds[] = 3;
        }

        if (request()->filled('terrace')) {
            $serviceIds[] = 5;
        }

        if (request()->filled('sauna')) {
            $serviceIds[] = 6;
        }

        return $serviceIds;
    }
}

SOLUTION 2:

I see some sort of MANY TO MANY relationship between apartment and service. So lets define a relationship between these two.

class Service extends Model
{
}

class Apartment extends Model
{
    public function services()
    {
        return $this->belongsToMany(Service::class, 'apartment_service');
    }
}

Then from the controller, you can rewrite to something like:

class ApartmentsController extends Controller
{
    public function index()
    {
        return Apartment::when($this->getServices(), function ($query) {
            return $query>whereHas('services', function ($query) {
                $query->whereKeyIn($this->getServices());
            });
        })
        ->get();
    }
}

@folipso are you using the 2nd solution? If so, pls copy the getServices() function from the first one. — Kevin Bui, Feb 21 '20 at 13:10

Many to many query builder laravel

1 Answers1