Search in the main model relationship in Laravel

I am beginner in Laravel. I have small problem with my code

In my system I have cabinets and boards from which it is made. I’m doing a cupboard builder. Each cabinet has specific plates assigned to it. Each cabinet may have different plates.

In this moment I have:

class Product extends Model
{
    use ScopeActiveTrait;
    use Slugable;

    public function setNameAttribute($value)
    {
        $this->attributes['name'] = $value;
        $this->attributes['slug'] = $this->makeSlug($value);
    }

    protected $fillable = ['mark_popular', 'mark_new', 'delivery_time', 'product_type', 'name', 'title', 'description', 'keywords', 'content', 'vat_id', 'main_category_id', 'enable', 'slug', 'small_description'];
    protected $guarded = ['id'];
    public $timestamps = false;

    public function vat()
    {
        return $this->belongsTo('AppModelsVat', 'vat_id');
    }

    public function category()
    {
        return $this->belongsTo('AppModelsCategory', 'main_category_id');
    }

    public function selectedCategory()
    {
        return $this->hasMany('AppModelsSelectedProductCategory', 'product_id', 'id');
    }


    public function related()
    {
        return $this->hasMany('AppModelsRelatedProduct', 'product_id', 'id');
    }

    public function features()
    {
        return $this->hasMany('AppModelsSelectedProductFeature');
    }


    public function frontImage()
    {
        return $this->hasMany('AppModelsUploadImage', 'file_id', 'id')->orderBy('order', 'ASC')->where('file_type', 'products');
    }

    public function selectedPlates()
    {
        return $this->hasMany('AppModelsSelectedPlates', 'product_id', 'id');
    }

}


class Plate extends Model
{
    use ScopeActiveTrait;

    protected $fillable = ['name', 'enable', 'price', 'vat_id', 'type', 'description'];
    protected $guarded = ['id'];
    public $timestamps = false;

    public function frontImage()
    {
        return $this->hasMany('AppModelsUploadImage', 'file_id', 'id')->orderBy('order', 'ASC')->where('file_type', 'plates');
    }

    public function vat()
    {
        return $this->belongsTo('AppModelsVat', 'vat_id');
    }

}


class SelectedPlates extends Model
{
    use ScopeActiveTrait;

    protected $guarded = ['id'];
    protected $fillable = ['plate_id', 'status', 'maxvalue', 'minvalue', 'product_id'];

    public function plate()
    {
        return $this->belongsTo('AppModelsPlate', 'vat_id');
    }
}

SelectedPlates – assigned to a product / plate cabinet

Plate – plates

Product – cabinets

I need a searchable display of all boards assigned to a given product / cabinet

I make this function:

public function getPlates(string $query, int $type, int $productId)
{
Product::active()->with(['vat', 'frontImage', 'selectedPlates'])->where(function ($q) use ($query, $type){
            if ($query != "") {
                $q->where('name', 'LIKE', '%' . $query . '%');
                $q->orWhere('description', 'LIKE', '%' . $query . '%');
            }
        })->orderBy('name', 'asc')->get()
}

this code return me 2 plates:

IlluminateDatabaseEloquentCollection {#1669 ▼
  #items: array:1 [▼
    0 => AppModelsProduct {#1605 ▼
      #fillable: array:14 [▶]
      #guarded: array:1 [▶]
      +timestamps: false
      #connection: "mysql"
      #table: "products"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:16 [▶]
      #original: array:16 [▶]
      #changes: []
      #casts: []
      #classCastCache: []
      #dates: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: array:3 [▼
        "vat" => AppModelsVat {#1640 ▶}
        "frontImage" => IlluminateDatabaseEloquentCollection {#1606 ▶}
        "selectedPlates" => IlluminateDatabaseEloquentCollection {#1642 ▼
          #items: array:2 [▼
            0 => AppModelsSelectedPlates {#1702 ▶}
            1 => AppModelsSelectedPlates {#1703 ▶}
          ]
        }
      ]
      #touches: []
      #hidden: []
      #visible: []
    }
  ]
}

Now I need search in this plates by:

if ($query != "") {
                $q->where('plates.name', 'LIKE', '%' . $query . '%');
                $q->orWhere('plates.description', 'LIKE', '%' . $query . '%');
            }

and filter by

->where(function ($q) use ($type) {
                $q->where('plates.type', $type);
                $q->orWhere('plates.type', 3);
            })

something like this:

public function getMaterials(string $query, int $type, int $produktId)
    {
        return Product->active()->with(['vat', 'frontImage'])->where(function ($q) use ($query, $type) {
            if ($query != "") {
                $q->where('plates.name', 'LIKE', '%' . $query . '%');
                $q->orWhere('plates.description', 'LIKE', '%' . $query . '%');
            }
        })
            ->where(function ($q) use ($type) {
                $q->where('plates.type', $type);
                $q->orWhere('plates.type', 3);
            })
            ->orderBy('name', 'asc')->get();
    }

The above code doesn’t work. Does not search / filter. how can I make it?

Answer

You need to change few lines of code in order to this work. First add relation for plates and then, for example:

public function getMaterials(string $query, int $type, int $produktId)
    {
        return Product->active()->with(['selectedPlates', 'selectedPlates.plate'])
            ->whereHas('selectedPlates', function ($q) use ($query, $type) {
                if (!empty($query)) {
                    $q->where('name', 'LIKE', '%' . $query . '%');
                    $q->orWhere('description', 'LIKE', '%' . $query . '%');
                }
                
                $q->whereHas('plate', function($q) use ($type) {
                    $q->where('type', $type ?: 3);
                }
            })
            ->orderBy('name', 'asc')->get();
    }

Learn more of whereHas on https://stackoverflow.com/a/30232227/3740460