Laravel或者与相关的表

Question

I am trying to get rows from a related table with:

        $Product->where('name', 'LIKE', '%' . $filters['search'] . '%')
                ->orWhere('brand', 'LIKE', '%' . $filters['search'] . '%');

But it is not working because it is only selecting from the products table.

My product model is:

class Product extends Eloquent {

    protected $table = 'products';

    public function brands() {
        return $this->hasOne('ProductBrands', 'id', 'brand_id');
    }

    public function ages() {
        return $this->hasOne('ProductAges', 'id', 'age_id');
    }

    public function types() {
        return $this->hasOne('ProductTypes', 'id', 'type_id');
    }

    public function images() {
        return $this->hasMany('ProductImages');
    }

    public function toArray() {

        $ar = $this->attributes;

        $ar['brand'] = $this->brand;
        $ar['age'] = $this->age;
        $ar['type'] = $this->type;

        return $ar;
    }

    public function getBrandAttribute() {
        $brands = $this->brands()->first();
        return (isset($brands->brand) ? $brands->brand : '');
    }

    public function getAgeAttribute() {
        $ages = $this->ages()->first();
        return (isset($ages->age) ? $ages->age : '');
    }

    public function getTypeAttribute() {
        $types = $this->types()->first();
        return (isset($types->type) ? $types->type : '');
    }

}

With the relationship specified correctly I am unsure how to proceed.

How do I select from the main table and/or the related table products_brands?

---

After adding in the orWheres the rest of my filter has broken:

public function fetchProducts($filters, $sorts = null, $perpage = 2) {
    $Product = Product::query();
    if (!empty($filters['search']))
        $Product->where('name', 'LIKE', '%' . $filters['search'] . '%')
                ->orWhereHas('brands', function($q) use ($filters) {
                    $q->where('brand', 'LIKE', '%' . $filters['search'] . '%');
                })
                ->orWhereHas('types', function($q) use ($filters) {
                    $q->where('type', 'LIKE', '%' . $filters['search'] . '%');
                });
    if (isset($filters['type']))
        $Product->where('type_id', $filters['type']);
    if (isset($filters['brand']))
        $Product->where('brand_id', $filters['brand']);
    if (isset($filters['age']))
        $Product->where('age_id', $filters['age']);

    if (isset($sorts['sort']))
        $Product->orderBy($sorts['sort'], $sorts['sortdir']);

    return $Product;
}

If both search and one or more of the others is present e.g. age, it only obeys whats in if (!empty($filters['search']))

How can I fix this?

Answer 1

To write a where condition for a relationship you can use whereHas. (or orWhereHas in your caseI) Laravel Docs

Try this:

$Product->where('name', 'LIKE', '%' . $filters['search'] . '%')
        ->orWhereHas('brands', function($q) use ($filters){
            $q->where('brand', 'LIKE', '%' . $filters['search'] . '%');
        });

Update

To be on the save side with all those wheres you better use a nested where

$Product = Product::query();
if (!empty($filters['search'])){
    $Product->where(function($q) use ($filters){
        $q->where('name', 'LIKE', '%' . $filters['search'] . '%')
        $q->orWhereHas('brands', function($q) use ($filters) {
            $q->where('brand', 'LIKE', '%' . $filters['search'] . '%');
        });
        $q->orWhereHas('types', function($q) use ($filters) {
            $q->where('type', 'LIKE', '%' . $filters['search'] . '%');
        });
}
if (isset($filters['type']))
    $Product->where('type_id', $filters['type']);
if (isset($filters['brand']))
    $Product->where('brand_id', $filters['brand']);
if (isset($filters['age']))
    $Product->where('age_id', $filters['age']);

if (isset($sorts['sort']))
    $Product->orderBy($sorts['sort'], $sorts['sortdir']);

return $Product;