Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

romatexdev's avatar

How to work correctly with a 3-way pivot table without losing performance?

Hello everyone Tell me how to work correctly with a 3-way pivot table.

My tables: Pivot table

Schema::create('product_property', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->foreignId('product_id')->constrained()->onDelete('cascade');
            $table->foreignId('property_id')->constrained()->onDelete('cascade');
            $table->foreignId('property_value_id')->nullable()->constrained()->onDelete('cascade');
        });

Product table

Schema::create('products', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('article')->nullable();
            $table->text('description')->nullable();
            $table->string('slug', 100)->unique();
            $table->foreignId('group_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('brand_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('catalog_id')->nullable()->constrained()->nullOnDelete();
            $table->softDeletes();
            $table->timestamps();
        });

Property table

Schema::create('properties', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name')->unique();
            $table->integer('order')->nullable();
            $table->softDeletes();
            $table->timestamps();
        });

PropertyValue table

Schema::create('property_values', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->foreignId('property_id')->nullable()->constrained()->onDelete('cascade');
            $table->string('value');
            $table->softDeletes();
            $table->timestamps();
        });

All Eloquent relationships in the models are configured according to Laravel documentation.

Product

    public function properties() :BelongsToMany
    {
        return $this->belongsToMany(Property::class,'product_property', 'product_id', 'property_id')
            ->with('values')
            ->withPivot('property_value_id');
    }

    public function values() :BelongsToMany
    {
        return $this->belongsToMany(PropertyValue::class,'product_property', 'product_id', 'property_value_id');
    }

Property

    public function products() :BelongsToMany
    {
        return $this
            ->belongsToMany(Product::class, 'product_property', 'property_id', 'product_id')->with('values');
    }

    public function values() :BelongsToMany
    {
        return $this->belongsToMany(PropertyValue::class, 'product_property','property_id','property_value_id');
    }

PropertyValue

    public function properties() :BelongsToMany
    {
        return $this
            ->belongsToMany(Property::class, 'product_property','property_id','property_value_id');
    }

    public function products() :BelongsToMany
    {
        return $this
            ->belongsToMany(Product::class, 'product_property','property_value_id','product_id');
    }

How do I request all the properties+values of the specified products and output as?

Array
(
    ['PROPERTY NAME 1'] => Array
        (
            ['PROPERTY_VALUE ID 1'] => 'PROPERTY_VALUE 1 VALUE'
            ['PROPERTY_VALUE ID 2'] => 'PROPERTY_VALUE 2 VALUE'
        )

    ['PROPERTY NAME 2'] => Array
        (
            ['PROPERTY_VALUE ID 1'] => 'PROPERTY_VALUE 1 VALUE'
            ['PROPERTY_VALUE ID 2'] => 'PROPERTY_VALUE 2 VALUE'
        )

    ['PROPERTY NAME 3'] => Array
        (
            ['PROPERTY_VALUE ID 1'] => 'PROPERTY_VALUE 1 VALUE'
            ['PROPERTY_VALUE ID 2'] => 'PROPERTY_VALUE 2 VALUE'
        )

)

I tried it like this:

$productsId = 'any_products_id'

Property::whereHas('products', function ($query) use ($productsId) {
            $query->whereIn('product_id', $productsId);
        })->whereHas('values', function ($query) use ($productsId) {
            $query->whereIn('product_id', $productsId);
        })->orderBy('order')->get();

But this creates the problem of a huge number of loaded models. How do I generate such an array without losing performance?

0 likes
2 replies
aletopo's avatar
aletopo
Best Answer
Level 34

Hi @romatexdev

You are talking about 2 thing here.

  1. The need of a specific format
  2. The need of an performance optimization

If you pretend to get directly from database that kind of nested array, you should avoid Eloquent ORM because it works with models, not with plain data or just arrays. You can try using pure PHP PDO features like this https://phpdelusions.net/pdo#group - Remember that Eloquent uses PDO behind the scenes if you perform queries through DB facade instead Model queries.

If database or the scrips works slowly may be it is not a problem to be solved with code changing, may be using another server or monitoring database to find what is the real problem. Try something like this https://github.com/supliu/laravel-query-monitor

1 like
romatexdev's avatar

@aletopo , Perhaps I spoke incorrectly. I don't need and I don't get such an array. I just want to get all the properties of the products and all the property_values of these properties.

Please or to participate in this conversation.