Laravel advanced search between product and relationships

Question

I am kind of new to laravel, and would like to work with relationship behavior laravel provides.

I have a search function that searches;

Keywords, Category, Media Type, Artist, Minimum and Maximum Price

My tables are as follows (showing only pertinent data)

Table: products (model: Products)

id -> integer (primary)
name -> string
artist -> integer
active -> boolean

Table: options_products (model: ProductOptions)

id -> integer (primary)
products_id -> integer
price -> decimal(14,2)
active -> boolean

Table: categories (model: Category)

id -> integer (primary)
parent_id -> integer
name -> string

Table: category_products (model ProductCategories) <- Pivot table

products_id -> integer
category_id -> integer

Table: media_type (model: Media)

id -> integer (primary)
name -> string

Product Model

public function categories() {
    return $this->belongsToMany('App\Category');
}

public function options() {
    return $this->hasMany('App\ProductOptions');
}

ProductOptions Model:

public function mediaType()
{
    return $this->hasOne('App\Media', 'id', 'media');
}

public function product()
{
    return $this->hasOne('App\Products', 'id', 'products_id');
}

public function productsMany() {
    return $this->hasMany('App\Products', 'id', 'products_id');
}

ProductCategories Model:

public function products()
{
    return $this->hasOne('App\Products', 'id', 'products_id');
}

Category Model:

public function products()
{
    return $this->belongsToMany('App\Products', 'category_products');
}

public function parent()
{
    return $this->belongsTo(self::class, 'parent_id');
}

public function children()
{
    return $this->hasMany(self::class, 'parent_id', 'id');
}

For the life of me i cannot figure out how to incorporate everything into the search, for example if they select a category, and min and max price, since the category is linked to the product table and the price is in the options_product table. Basically I need it to search either one argument, a few, or all arguments. I think I am out of my league as I searched and searched and could not find an answer unless i result in a raw query.

I have a rather long winded routine currently that handles most of the search options, separately however and not with the min and max pricing. For example for the text search:

Text Search:

if ($request->exists('search')) {
        /* filter & split */
        $search = Helpers::filter_search($request->get('search'));
        $products = Products::where(function ($q) use ($search) {
            foreach ($search as $value) {
                $q->orWhere('name', 'like', "%{$value}%");
                $q->orWhere('description', 'like', "%{$value}%");
            }
        })->where('active', 1)->paginate($views_per_page)->setPath('');
        $products->appends(array(
            'search' => $request->get('search')
        ));
    }

Here is the routine to get min/max:

if ($request->exists('min') || $request->exists('max')) {
        $min = ($request->get('min')) ?? 0.00;
        $max = ($request->get('max')) ?? null;
        $products = Products::with('options')
            ->whereHas('options', function($q) use ($min,$max) {
                $q->where('options_products.price', '>=', $min);
                if ($max) {
                    $q->where('options_products.price', '<=', $max);
                }
            })->where('active', 1)->paginate($views_per_page);

    }

Helper function:

public static function filter_search($query)
{
    $q = preg_split('/[;,+ ]+/', $query, -1, PREG_SPLIT_NO_EMPTY);
    $q = preg_replace("/[^ \w-]/", "", $q);
    return $q;
}

So basically, I want to be able to put this all in one query if need be. So if they customer chooses the Artist, Category, Media, Min And/or Max Price, and text-search it will create one full query. Any help on this would be appreciated, thanks

Answer 1

Search Product name or products that have a category name

 $products = Product::with('category')->where('name', 'LIKE', '%' . $data['name'] . '%')->orWhere( function($query) use ($data){
                $query->whereHas('category', function($q) use ($data){
                    $q->where('name', 'LIKE', '%' . $data['name'] . '%');
                });
            })->get();

This can be modified to fit your need.