Advanced eloquent filtering with pivot table

Question

A challenge for the community! I have a map which presents multiple stores. When a user clicks on a store it should load only the relevant products. To do this I have four tables (see below).

I am able to bring up the relevant products from the brand if the user applied a gender filter $gender by using if(!is_null($gender)) $q->where($gender, '=', 1);.However, I would like to be able to present the correct products even if the user did not filter.

The challenge is that a brand could have products for both men and women, but placed in separate stores (or in the same store). To track this I have the pivot table named brands_stores.

In short - what I would like add is something like

$q->with(['products' => function ($q) use ($product_ids [GENDER(S) FROM PIVOT TABLE]){
    $q->whereIn('gender', [GENDER(S) FROM PIVOT TABLE];
}

to the existing query below.

Query

    // Get 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 AS product_id', 'name', 'brand', 'price', 'img_link');
                }]);
            }])
            ->whereIn('id', $store_ids)
            ->select('id', 'name', 'lat', 'lng', 'formatted_address AS address', 'street_number', 'route', 'open_monday', 'open_saturday', 'open_sunday', 'close_monday', 'close_saturday', 'close_sunday', 'formatted_phone_number AS 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 TO BRANDS WHICH ARE LINKED TO STORES

Each Product has a Brand column. That Brand column is the same as the Name column in the Brand model. Which Brands and what Products a store carries from that brand are linked through the Brands_Stores TABLE. Logic flow:

• Does the store carry the relevant brand?
• If it does carry the relevant brand - which products (male or female or both?)
• Get the relevant products

SQL QUERIES

select `brand` from `products` where `name` like '%louboutin%' or `brand` like '%louboutin%' group by `brand`, `name`

select * from `products` where (`brand` in ('Christian Louboutin')) group by `brand`, `name`

select `id` from `brands` where `name` in ('Christian Louboutin')

select `store_id` from `brands_stores` where (`active` = '1' and `brand_id` in ('278'))

select `id`, `name`,  `formatted_phone_number` as `phone` from `stores` where `id` in ('561', '562', '563', '2182')

select `brands`.*, `brands_stores`.`store_id` as `pivot_store_id`, `brands_stores`.`brand_id` as `pivot_brand_id`, `brands_stores`.`active` as `pivot_active`, `brands_stores`.`brand_store` as `pivot_brand_store`, `brands_stores`.`women` as `pivot_women`, `brands_stores`.`men` as `pivot_men`, `brands_stores`.`children` as `pivot_children` from `brands` inner join `brands_stores` on `brands`.`id` = `brands_stores`.`brand_id` where `brands_stores`.`store_id` in ('562', '2182') and `active` = '1'

select `id` as `product_id`, `name`, `brand`, `price`, `img_link` from `products` where `products`.`brand` in ('Christian Louboutin') and `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 by `brand`, `name` order by `brand` asc, `gender` desc, `category` asc, `sub_category` asc

Answer 1

If a user selects a store your want "load only the relevant products".

Lets start by selecting a store:

SELECT stores.id 
FROM stores 
WHERE stores.name = 'selectedStoreName';

Now we fetch the available brands in this store:

SELECT stores.id, brands_store.brand_id
FROM stores 
JOIN brands_store ON stores.id = brands_store.store_id
WHERE stores.name = 'selectedStoreName';

Brand names are better than brand-ids:

SELECT stores.id, brands_store.brand_id, brands.name
FROM stores 
JOIN brands_store ON stores.id = brands_store.store_id
JOIN brands ON brands_store.brand_id = brands.id
WHERE stores.name = 'selectedStoreName';    

And finally we fetch all products from the available brands:

SELECT stores.id, brands_store.brand_id, brands.name, products.name, products.gender
FROM 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
WHERE stores.name = 'selectedStoreName';

Very clean data architecture by the way...