Laravel - How to query when you have a many to many relationship

Question

So I have two models: Product and Size. One product may have many sizes and one size may have many products that have that size. I made many ot many relationship between them with a product_size pivot table. Now I need to query all products that have a certain size. Here is my code:

Edit:

    $minPrice = $request['min'];
    $maxPrice = $request['max'];
    $colors = $request['color'];
    $sizes = $request['size'];

    if (count($request->all()) != 0) {

        $query = Product::with(['sizes' => function($query) use($request) {
            $sizeArray = $request->get('size');
            $query->whereIn('sizes.size', $sizeArray);
        }]);

        if(isset($minPrice) && isset($maxPrice)) {
            $query->whereBetween('price', array($minPrice, $maxPrice));
        }

        if(isset($colors)) {
            $query->whereIn('color_id', $colors);
        }

        $products = $query->get();
    }

Here are my models:

class Product extends Model
{
    public function color()
    {
        return $this->belongsTo('App\Color');
    }

    public function sizes()
    {
        return $this->belongsToMany('App\Size', 'product_size')->withTimestamps();
    }
}

Size:

class Size extends Model
{
    public function products()
    {
        return $this->belongsToMany('App\Product', 'product_size')->withTimestamps();
    }
}

Form:

<form id="filterOptions" method="get" action="{{ URL::current() }}">
    <button type="submit" class="btn-color">Filter</button>

    <div class="clearfix space20"></div>
    <h5>Color</h5>
    <ul class="color-list">
        @foreach($availableColors as $color)
            <li><input type="checkbox" name="color[]" value="{{ $color->id }}"><a href="#"><span class="{{ $color->name }}"></span> {{$color->name_bg}}</a></li>
            {{ $color->name }}
        @endforeach
    </ul>
    <div class="clearfix space20"></div>

    <h5>Price</h5>
    <div id="slider-container"></div>
    <p>
        <span class="{{--pull-right--}} sc-range">
            <input class="pull-left" name="min" type="text" id="min" style="border: 0; color: #333333; font-weight: bold;"/>
            <input class="pull-right" name="max" type="text" id="max" style="border: 0; color: #333333; font-weight: bold;"/>
        </span>
    </p>

    <div class="clearfix space30"></div>
    <h5>Size</h5>
    <ul class="size-list">
        @foreach($availableSizes as $size)
            <li><input type="checkbox" name="size[]" value="{{ $size->id }}">{{ $size->size }}</li>
        @endforeach
    </ul>
</form>

This is my current code. Everything works besides querying the sizes. It doesn't output any error. However it doesn't query the products according to the size.

Answer 1

The question is not clear enough and I could be wrong, but anyways. You may try this. So, first of all, you don't need to use something like the following:

$minPrice = $request['min'] ? $request['min'] : null;

Instead, you can just use something like the following:

$minPrice = $request->min;

This will return a value if exists otherwise, it'll return NULL value. So, in your example, you may simply use:

$minPrice = $request->min; // Or  : $request->get('min', 'default value if null');
$maxPrice = $reques->max;  // Also: $request->input('max');
$colors = $request->color;
$sizes = $request->size;

But, in your case, since you are conditionally building the query depending on the inputs so you may use something like: $request->has('String|Array'), for example, let's try the following approach to build the query conditionally:

// Begin query: No need to check for the size, query will work if
// there is no size available, hence 'sizes' will an be empty collection
$query = Product::with(['sizes' => function($query) use($request) {
    $sizeArray = $request->get('size'); // Make sure it's an array
    $query->whereIn('size', $sizeArray); // if null, the query'll work
}]);


// Now check min & max and make query
$minAndMaxKeys = ['min', 'max'];
if($request->has($minAndMaxKeys)) {
    // This'll return, ex: ['min' => 10, 'max' => 20]
    $minAndMaxArray = $request->only($minAndMaxKeys);

    // Build query using min and max values from the array
    $query->whereBetween('price', array_values($minAndMaxArray));
}

// Now check colors and make query
if($colors = $request->get('colors')) {
    $query->whereIn('color_id', $colors);
}

Now, just execute the query:

$result = $query->get();

The $result will contain a collection of Product model with matching price range and colors wherein each Product model will contain related sizes (collection of Size model) or an empty sizes collection if there was no matching found.

If you only want to get result only when sizes are available then use whereHas with the with. Check the other answer for that.

It's not possible to answer accurately because your question has less information. Check the Laravel documentation for better understanding.

Answer 2

Try this

    $criteria = array(
        "prices" => array(
            $request['min'] ? $request['min'] : false,
            $request['max'] ? $request['max'] : false
        ),
        "colors" => $request['color'] ? $request['color'] : false,
        "sizes" => $request['size'] ? $request['size'] : false
    );



    $products = Product::with(['sizes' => function ($query) use ($criteria) {

        if($criteria['sizes']) {
            foreach ($criteria['sizes'] as $size) {
                $query->where('size', '=', $size);
            }
        }

    }])->where(function ($query) use ($criteria) {

        if($criteria['colors']) {
            foreach ($criteria['colors'] as $color) {
                $query->where('color_id', '=', $color);
            }
        }

        if( $criteria['prices'][0] && $criteria['prices'][1] ) {
            $query->whereBetween('price', $criteria['prices'] );
        }

    })->get();

Answer 3

What you need is a combination of "Querying Relationship Existence" and the whereIn() method:

$products = Product::whereHas('size', function ($query) {
    // In the following line, replace "name" with the appropriate
    // field that relates to $request['size'].
    $query->whereIn('name', (array)$request['size']);
})->get();