Search in the main model relationship in Laravel

Question

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('App\Models\Vat', 'vat_id');
    }

    public function category()
    {
        return $this->belongsTo('App\Models\Category', 'main_category_id');
    }

    public function selectedCategory()
    {
        return $this->hasMany('App\Models\SelectedProductCategory', 'product_id', 'id');
    }


    public function related()
    {
        return $this->hasMany('App\Models\RelatedProduct', 'product_id', 'id');
    }

    public function features()
    {
        return $this->hasMany('App\Models\SelectedProductFeature');
    }


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

    public function selectedPlates()
    {
        return $this->hasMany('App\Models\SelectedPlates', '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('App\Models\UploadImage', 'file_id', 'id')->orderBy('order', 'ASC')->where('file_type', 'plates');
    }

    public function vat()
    {
        return $this->belongsTo('App\Models\Vat', '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('App\Models\Plate', '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:

Illuminate\Database\Eloquent\Collection {#1669 ▼
  #items: array:1 [▼
    0 => App\Models\Product {#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" => App\Models\Vat {#1640 ▶}
        "frontImage" => Illuminate\Database\Eloquent\Collection {#1606 ▶}
        "selectedPlates" => Illuminate\Database\Eloquent\Collection {#1642 ▼
          #items: array:2 [▼
            0 => App\Models\SelectedPlates {#1702 ▶}
            1 => App\Models\SelectedPlates {#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?

maki10 · Accepted Answer · 2020-07-21T12:40:02.087

0

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

edited Jul 21 '20 at 12:40

answered Jul 21 '20 at 11:52

maki10

553
2
11

1

Thank you. I change yours code: active()->with(['selectedPlates']) ->whereHas('selectedPlates', function ($q) use ($query, $type) { if (!empty($query)) { $q->where('name', 'LIKE', '%' . $query . '%'); $q->orWhere('description', 'LIKE', '%' . $query . '%'); } $q->where('type', $type ?: 3); }) ->orderBy('name', 'asc')->get() – trifesk Jul 21 '20 at 12:10
and now I have error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'type' in 'where clause' (SQL: select * from `products` where `enable` = 1 and exists (select * from `selected_plates` where `products`.`id` = `selected_plates`.`product_id` and (`name` LIKE %produkt% or `description` LIKE %produkt% and `type` = 2)) order by `name` asc) – trifesk Jul 21 '20 at 12:11
Because you don't have that column.. Try `$q->plate()->where('type', $type ?: 3);` Maybe i have few typos, it's hard from this side, but that is the way you need to get that working – maki10 Jul 21 '20 at 12:34
Also you can do this: `$q->whereHas('plate', function ($q2) use ($type) {$q2->where('type', $type ?: 3);}` Please also note that `$q2` is just for your point of. You can call it like you wish. – maki10 Jul 21 '20 at 12:36
Answer is updated, so it will be easier for you to see what I am mean.. Regards – maki10 Jul 21 '20 at 12:40
If my answer is good please also give me arrow up :) – maki10 Jul 21 '20 at 12:56

Search in the main model relationship in Laravel

1 Answers1