Constraints on relationships

I have 3 tables

Products
|------|
|  id  |
|------|
|   1  |

Features
|------|--------------|-------------|
|  id  |   value_num  |   field_id  |        
|------|--------------|-------------|
|   1  |       2      |     1       |
|------|--------------|-------------|
|   2  |       3      |     2       |

Fields
|------|---------------|
|  id  |  show_list    |
|------|---------------|
|   1  |      1        |
|------|---------------|
|   2  |      0        |

My Models looks like:

Products

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    public function categories()
    {
        return $this->belongsToMany(Category::class);
    }

    public function features()
    {
        return $this->belongsToMany(Feature::class);
    }
}

Features

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Feature extends Model
{
    public function products()
    {
        return $this->belongsToMany(Products::class);
    }

    public function field()
    {
        return $this->belongsTo(Field::class);
    }
}

Fields

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Field extends Model
{
    public function categories()
    {
        return $this->belongsToMany(Category::class);
    }

    public function features()
    {
        return $this->hasMany(Feature::class);
    }
}

The farthest I've got with my query is:

        $category = $category->load(['products' => function ($query) {
            $query->with(['features' => function ($query) {
                $query->join('fields', 'features.field_id', '=', 'fields.id')
                    ->with('field')
                    ->where('show_list', 1)
                    ->where('value_number', 2);
            }]);
        }]);

I want to be able to fetch the record where the Feature value_num is 2 and the Field show_listis 1

JackJones

8 years ago

Best Answer

Level 14

I think this works, I'm not sure if it is the most efficient way though, but for anyone else:

        $category = $category->load(['products' => function ($query) {
            $query->with(['features' => function ($x) {
                $x->with('field')
                    ->whereHas('field', function($y) {
                        $y->where('show_list', 1);
                    });
            }])
                ->whereHas('features', function ($query) {
                    $query->where('value_number', 2);
                });}]);

Please or to participate in this conversation.