How to insert a variable into an eloquent request?

@Snapey

Basically I do this:

$category = Category::getCategoryByTitle($categoryTitle);
$products = Product::whereHas('categories', function ($query) use ($category){
            $query->where('id',  $category->id);
        })
->with('categories',  'genders', 'brands', 'colors')
->get();

I use with to get all related data from tables in one query.

Also, I'll need to filter the query later. Get not just products by category, but also by filter by color and brand, etc. That is, "products from the category of sneakers, black and blue, brand adidas and nike."

If you tell me the right way - I will be very grateful) Following your example, I was unable to load all products along with other related tables brand, color, etc.

@Rediska I would probably

$category = Category::getCategoryByTitle($categoryTitle);
$products = $category->products()->with('categories',  'genders', 'brands', 'colors')->get();

since whereHas is quite slow

@Snapey It works. But I still can't figure out how to filter the result now. How should a query look like in order to filter by color (blue, green) and brand (adidas) in addition to category?

Moreover, my next question (apparently already in a separate topic) will be - how to search in an array of categories. Because I have a category tree. And if this is the final category (sneakers), then everything is clear. But if the category is shoes, then it should contain sneakers, sneakers, moccasins, etc. And the request will be like this: Select products with category IDs = 1,2,3 + brand = 1,2 + color = 1,2,3,4.

@Rediska Your filter needs to be done at the sql query, not afterwards.

How do you store the attributes? ie, I assume a product can only belong to one brand? Where is this stored?

@Snapey I store all the attributes through many-to-many relationships. Products table:

Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('title', 100);
            $table->integer('price')->index()->nullable();

            $table->timestamps();
            $table->softDeletes();
        });

Category table:

Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('title', 100);

            $table->timestamps();
            $table->softDeletes();
        });

Color table

Schema::create('colors', function (Blueprint $table) {
            $table->id();
            $table->string('title', 100);

            $table->timestamps();
            $table->softDeletes();
        });

Relationship tables:

Schema::create('category_product', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('product_id')->unsigned()->index();
            $table->foreign('product_id')->references('id')->on('products');
            $table->bigInteger('category_id')->unsigned()->index();
            $table->foreign('category_id')->references('id')->on('categories');
            $table->timestamps();
        });
Schema::create('color_product', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('product_id')->unsigned()->index();
            $table->foreign('product_id')->references('id')->on('products');
            $table->bigInteger('color_id')->unsigned()->index();
            $table->foreign('color_id')->references('id')->on('colors');
            $table->timestamps();
        });

In the category model:

public function products()
    {
        return $this->belongsToMany(Product::class);
    }

In the product model:

public function categories()
    {
        return $this->belongsToMany(Category::class);
    }
public function colors()
    {
        return $this->belongsToMany(Color::class);
    }

@Snapey You probably already forgot, but I'll remind you)) I once asked a similar question, but at that time I generally stored all the attributes in one product table)) You laughed at me for a very long time and advised me to learn communications. Here I am following your advice. Now I want to deal with this approach. Perhaps the next project will be with this approach)

But at this stage, I want to refine the existing scheme. It turns out that I can't do without ->whereHas?

@Rediska Sorry if we had a bad encounter before. I can't remember it.

I do think you will end up with a horrendous query that might be quite slow, whereas it seems quite sensible to have a record that represents each individual item that is sold.

@Snapey No. Just the opposite. Thanks to your advice, I am learning how to write correct code))