Laravel scope query with nested models in 3-way pivot table
<p>I have a <strong>Product</strong> Model that needs to be connected with <strong>Processings</strong>, that describe <em>how</em> the Product can be manipulated, and <strong>Positions</strong>, that describe <em>where</em> the Product can be manipulated.</p>

<p>In other words, a <strong>Product</strong> can be <em>processed</em> into <em>multiple positions</em>.</p>

<p>The "Product Data" should be returned with <em>nested processings into positions</em>, so that, for every Position into the database, a Product can be associated with related Processings, in a scheme similar to the one below:</p>

<pre><code>Products [
    product_attributes,
    Positions [
        position_attributes,
        Processings: [
            processing_attributes
        ]
    ]
]
</code></pre>

<p>So, I have created a <strong>3-way Pivot Table</strong> with the following code:</p>

<pre><code>Schema::create('product_position_processing', function (Blueprint $table) {

    // 3-way pivot
    $table-&gt;integer('product_id')-&gt;unsigned()-&gt;index();
    $table-&gt;foreign('product_id', 'ppp_aid_foreign')-&gt;references('id')-&gt;on('products')-&gt;onDelete('cascade');
    $table-&gt;integer('position_id')-&gt;unsigned()-&gt;index();
    $table-&gt;foreign('position_id', 'ppp_bid_foreign')-&gt;references('id')-&gt;on('positions')-&gt;onDelete('cascade');
    $table-&gt;integer('processing_id')-&gt;unsigned()-&gt;index();
    $table-&gt;foreign('processing_id', 'ppp_cid_foreign')-&gt;references('id')-&gt;on('processings')-&gt;onDelete('cascade');
    $table-&gt;primary(['product_id', 'position_id', 'processing_id'], 'index-base-combined');

    // these attributes define the minimum and maximum order amount based on the product + position + processing combination
    $table-&gt;integer('minimum_order_amount')-&gt;nullable();
    $table-&gt;integer('maximum_order_amount')-&gt;nullable();

    $table-&gt;timestamps();
});
</code></pre>

<p>Then, I have defined the relations between the 3 different models in this way:</p>

<p><strong><em>Processing</em></strong> Model</p>

<pre><code>class Processing extends Model {
    use SoftDeletes;

    protected $dates = ['deleted_at'];
    protected $guarded = ['id', 'created_at', 'updated_at'];
    protected $casts = [
        'is_publicly_hidden' =&gt; 'boolean'
    ];
    protected $hidden = ['pivot'];

    public function products() {
        return $this -&gt; belongsToMany ( Product::class, 'product_position_processing', 'processing_id', 'product_id' );
    }

    public function positions () {
        return $this -&gt; belongsToMany ( Position::class, 'product_position_processing', 'processing_id', 'position_id' );
    }

    ...
    ...

}
</code></pre>

<p><strong><em>Position</em></strong> Model</p>

<pre><code>class Position extends Model {
    use SoftDeletes;

    protected $dates = ['deleted_at'];
    protected $guarded = ['id', 'created_at', 'updated_at'];
    protected $hidden = ['pivot'];

    public function products() {
        return $this-&gt;belongsToMany ( Product::class, 'product_position_processing', 'position_id', 'product_id' );
    }

    public function processings ( ) {
        return $this -&gt; belongsToMany ( Processing::class, 'product_position_processing', 'position_id', 'processing_id' );
    }

    ...
    ...
}
</code></pre>

<p><strong><em>Product</em></strong> Model</p>

<pre><code>class Product extends Model implements SluggableInterface {
    use SoftDeletes;
    use SluggableTrait;

    protected $dates = ['deleted_at'];
    protected $guarded = ['id', 'created_at', 'updated_at'];
    protected $sluggable = [
        'build_from' =&gt; 'name',
        'save_to'    =&gt; 'slug',
        'include_trashed' =&gt; true
    ];

    ...
    ...

    public function processings() {
        return $this-&gt;belongsToMany(Processing::class, 'product_position_processing', 'product_id', 'processing_id');
    }

    public function positions() {
        return $this-&gt;belongsToMany(Position::class, 'product_position_processing', 'product_id', 'position_id');
    }

    ...
    ...

    public function scopeWithCompleteData ($query) {
        return $query-&gt;with([
            ...
            ...
            'positions' =&gt; function($query) {
                return $query-&gt;with(['processings' =&gt; function ( $query ) {
                    return $query-&gt;select('id', 'name')-&gt;groupBy('processing_id');
                }])-&gt;groupBy('position_id');
            },
            ...
            ...
        ]);
    }

}
</code></pre>

<p>Now, the Products are returned in json via the <strong><em>ProductsController</em></strong> that, with the following code, returns a JSON in this format.</p>

<p>Product Getter:</p>

<pre><code>...
...
$products = Product::withCompleteData() -&gt; get();
return $this -&gt; ok ( $products );
</code></pre>

<p>Example "ALL Products" @get JSON:</p>

<pre><code>{
    "status": "OK",
    "data": [
        {
            "id": 1,
            "category_id": 1,
            "subcategory_id": 0,
            "brand_id": 1,
            "sku": "BD615149D6",
            ...
            ...
            "positions": [
                {
                    "id": 1,
                    "name": "POS1",
                    "processings": [
                        {
                            "id": 1,
                            "name": "PROC1",
                            "pivot": {
                                "position_id": 1,
                                "processing_id": 1
                            }
                        },
                        {
                            "id": 2,
                            "name": "PROC2",
                            "pivot": {
                                "position_id": 1,
                                "processing_id": 2
                            }
                        },
                        {
                            "id": 3,
                            "name": "PROC3",
                            "pivot": {
                                "position_id": 1,
                                "processing_id": 3
                            }
                        }
                    ]
                },
                {
                    "id": 2,
                    "name": "POS2",
                    "created_at": "2016-08-01 07:39:11",
                    "updated_at": null,
                    "deleted_at": null,
                    "processings": []
                },
                {
                    "id": 3,
                    "name": "POS3",
                    "created_at": "2016-08-01 07:39:11",
                    "updated_at": null,
                    "deleted_at": null,
                    "processings": []
                }
            ]
        },
        {
            "id": 2,
            "category_id": 2,
            "subcategory_id": 0,
            "brand_id": 2,
            "sku": "BD615149D6",
            ...
            ...
            "positions": [
                {
                    "id": 5,
                    "name": "POS5",
                    "created_at": "2016-08-01 07:39:11",
                    "updated_at": null,
                    "deleted_at": null,
                    "processings": []
                }
            ]
        }
    ]
}
</code></pre>

<p>The problem is, the data returned is not correct. The Query Builder seems to connect the various relationships in a strange way. Unfortunately I'm still not enough proactive in understanding what lies below the magic of the query builder, and don't know how to debug and where to make modifications to the code.</p>

<p>Can anyone help out with this 3-way pivot table with nested objects into the Laravel query builder?</p>