mysql - Advanced eloquent filtering with pivot table -

a challenge community! have map presents multiple stores. when user clicks on store should load relevant products. have 4 tables (see below).

i able bring relevant products brand if user applied gender filter $gender using if(!is_null($gender)) $q->where($gender, '=', 1);.however, i able present correct products if user did not filter.

the challenge a brand have products both men , women, placed in separate stores (or in same store). track have pivot table named brands_stores.

in short - add

$q->with(['products' => function ($q) use ($product_ids [gender(s) pivot table]){     $q->wherein('gender', [gender(s) pivot table]; } 

to existing query below.

query

    // stores     $query = store::with(['brandsunfiltered' => function ($q) use ($active, $gender, $product_ids){         if(!is_null($active)) $q->where('active', '=', 1);  // active stores             if(!is_null($gender)) $q->where($gender, '=', 1);   // gender             $q->with(['products' => function ($q) use ($product_ids){                 $q->wherein('id', $product_ids);                 $q->orderby('brand', 'asc')->orderby('gender', 'desc')->orderby('category', 'asc')->orderby('sub_category', 'asc');                 $q->groupby('brand', 'name');                 $q->select('id product_id', 'name', 'brand', 'price', 'img_link');                 }]);             }])             ->wherein('id', $store_ids)             ->select('id', 'name', 'lat', 'lng', 'formatted_address address', 'street_number', 'route', 'open_monday', 'open_saturday', 'open_sunday', 'close_monday', 'close_saturday', 'close_sunday', 'formatted_phone_number phone' );          $stores = $query->get();     } 

store model

class store extends eloquent {      public function brandsunfiltered(){         return $this->belongstomany('\app\brand', 'brands_stores', 'store_id', 'brand_id')             ->withpivot('active', 'brand_store', 'women', 'men');     } } 

stores table

• id
• name
• ...

brands table

• id
• name
• ...

brands_stores table

• id
• store_id
• brand_id
• women [boolean]
• men [boolean]
• ...

products table

• id
• name
• brand
• gender
• ...

=====edit=====

products linked brands linked stores

each product has brand column. brand column same name column in brand model. brands , products store carries brand linked through brands_stores table. logic flow:

• does store carry relevant brand?
• if carry relevant brand - products (male or female or both?)
• get relevant products

sql queries

select `brand` `products` `name` '%louboutin%' or `brand` '%louboutin%' group `brand`, `name`  select * `products` (`brand` in ('christian louboutin')) group `brand`, `name`  select `id` `brands` `name` in ('christian louboutin')  select `store_id` `brands_stores` (`active` = '1' , `brand_id` in ('278'))  select `id`, `name`,  `formatted_phone_number` `phone` `stores` `id` in ('561', '562', '563', '2182')  select `brands`.*, `brands_stores`.`store_id` `pivot_store_id`, `brands_stores`.`brand_id` `pivot_brand_id`, `brands_stores`.`active` `pivot_active`, `brands_stores`.`brand_store` `pivot_brand_store`, `brands_stores`.`women` `pivot_women`, `brands_stores`.`men` `pivot_men`, `brands_stores`.`children` `pivot_children` `brands` inner join `brands_stores` on `brands`.`id` = `brands_stores`.`brand_id` `brands_stores`.`store_id` in ('562', '2182') , `active` = '1'  select `id` `product_id`, `name`, `brand`, `price`, `img_link` `products` `products`.`brand` in ('christian louboutin') , `id` in ('6800', '7538', '7612', '7582', '8095', '7104', '8053', '7995', '7115', '7485', '7997', '7866', '7622', '6820', '7682', '8000', '8055', '6838', '7589', '7046', '7232', '6810', '7609', '7429', '7597', '7557', '7593', '7458', '7481', '7572', '7620', '7238', '7537', '6843', '7619', '7598', '8036', '7284', '6956', '7993', '6863', '8039', '7614', '7493', '7315', '7318', '6841', '7509', '7198', '5813', '8203', '7623', '7441', '8096', '7957', '6522', '6850', '8056', '7821', '6753', '6632', '7569', '7994', '7784', '9388', '9431', '9440', '9392', '6348', '6373', '5989', '7339', '7329', '7340', '7502', '7544', '7586', '7636', '7252', '7179', '7564', '6771', '6461', '6554', '6563', '6442', '5756', '5770', '7439', '7373', '7313', '7349', '7293', '7595', '7323', '7565', '8073', '9363', '9411', '5910', '5899', '6136', '5828', '6577', '6601', '7551', '7392', '7543', '7872', '8076', '7517', '7849', '7531', '7602', '7410', '6836', '7401', '6851', '8034') group `brand`, `name` order `brand` asc, `gender` desc, `category` asc, `sub_category` asc 

if user selects store want "load relevant products".

lets start selecting store:

select stores.id  stores  stores.name = 'selectedstorename'; 

now fetch available brands in store:

select stores.id, brands_store.brand_id stores  join brands_store on stores.id = brands_store.store_id stores.name = 'selectedstorename'; 

brand names better brand-ids:

select stores.id, brands_store.brand_id, brands.name stores  join brands_store on stores.id = brands_store.store_id join brands on brands_store.brand_id = brands.id stores.name = 'selectedstorename';     

and fetch products available brands:

select stores.id, brands_store.brand_id, brands.name, products.name, products.gender stores  join brands_store on stores.id = brands_store.store_id join brands on brands_store.brand_id = brands.id join products on brands.name = products.brand_name stores.name = 'selectedstorename'; 

very clean data architecture way...