mardi 4 juin 2019

how to use whereHas with condition in many to many relationship

I have 3 tables like this : Centres table :

id | name 

Themes table

id | nomtheme

centre_theme table

id | centre_id | theme_id

And my models are :

Centre.php

 public function themes(){
    return $this->belongsToMany('App\Models\Theme');
}

Theme.php

 public function centres(){
    return $this->belongsToMany('App\Models\Centre');
}

I am trying to recover in a request the "centres" associated with a "theme" if user search for that.

My request is actually :

$theme_id = $request->input('theme');
$centres = Centre::where('statut', 'Publié')->with('region', 'dept', 
'photocentre', 'themes')
->wherehas('themes', function($centres)  {
            if(isset($theme_id) &&  !empty ($theme_id)) {
                $centres->where('theme_id', '=', 1);
            }
        })



        ->where(function($centres) use ($id_localisationdept)  {
            if(! empty($id_localisationdept)) {
                $centres->where('dept_id', $id_localisationdept->id);
            }})

        ->where(function($centres) use ($id_localisationregion)  {
            if(! empty($id_localisationregion)) {
                $centres->where('region_id', $id_localisationregion->id);

            }})

             ->when(isset($dept_id) || isset($region_id), function ($q) {
            return $q->orderByRaw("CASE WHEN plan = 'Basic' THEN 3 WHEN plan = 'Pro' THEN 2 WHEN plan = 'Premium' THEN 1  ELSE 4 END");
        },
            function ($q) {
                return $q->orderByRaw("CASE WHEN plan = 'Premium' THEN 1  ELSE 4 END");
            }
        )
        ->inRandomOrder()
        ->get();

But my whereHas function actually return a query like that :

select * from `centres` where `statut` = 'Publié' and exists (select * from `themes` inner join `centre_theme` on `themes`.`id` = `centre_theme`.`theme_id` where `centres`.`id` = `centre_theme`.`centre_id`) and (`capacite` > 80 and `capacite` <= 120) order by CASE WHEN plan = 'Premium' THEN 1  ELSE 4 END, RAND()



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire