Rediska's avatar

How to get data on multiple many-to-many relationships?

I have a product table:

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

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

I have category table:

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

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

I have a color table:

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

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

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

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

All relationships are many-to-many:

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();
        });
Schema::create('brand_product', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('product_id')->unsigned()->index();
            $table->foreign('product_id')->references('id')->on('products');
            $table->bigInteger('brand_id')->unsigned()->index();
            $table->foreign('brand_id')->references('id')->on('brands');
            $table->timestamps();
        });

How do I get products that have, for example, category = t-shirts, color = green or red and brand = adidas?

0 likes
3 replies
automica's avatar

how are you planning on recording the inventory levels of these products?

if you need to know your have a product that is

  • category = tshirt
  • color = red
  • size = large
  • brand = adidas
  • quantity = 20

then you'll need a way to firstly categorise your product

Product

  • category = tshirt
  • brand = Adidas eg product.id = 1

and then capture the variations in a separate model

  • product_id = 1
  • color = red
  • size = large
  • quantity = 20

and:

  • product_id = 1
  • color = red
  • size = medium
  • quantity = 20

a product would have many variations a variation would have one colour a variation would have one size a variation would have one quantity

you may want to adjust your initial models to account for variations before development your current structure.

Rediska's avatar

@automica Through a many-to-many relationship. I use intermediate tables. I don't need stock levels.

I learned how to get products by category:

$category = Category::getCategoryByTitle($categoryTitle);
$products = $category->products;

But I can't figure out how to make more complex queries:

  1. Get the products as I did above, but also get their attributes (brand, color, size)
  2. Get products based on the results of the selected filter. For example, category=T-shirts brand=Adidas color=red and yellow And they also need to get their attributes (brand, color, size, etc.)
PovilasKorop's avatar

I would do it with joins, something like:

Product::query()
  ->join('product_color', 'products.id', '=', 'product_color.product_id')
  ->join('colors', 'product_color.color_id', '=', 'colors.id')
  // other tables: ->join('...')
  ->whereIn('colors.name', ['green', 'red'])
  ->get();

Please or to participate in this conversation.