Next someone is going to want eloquent to mow their lawn. See https://laracasts.com/discuss/channels/general-discussion/approach-question-for-showing-big-unchanging-outline
Recursive function many-to-many self referencing model subqueries
I have a model called Product that has the following self referencing many-to-many relation:
// parent products based on the product_product pivot table
public function parent_products()
{
return $this->belongsToMany('App\Product', 'product_product', 'child_id', 'parent_id')->withPivot('amount');
}
// child products based on the product_product pivot table
public function child_products()
{
return $this->belongsToMany('App\Product', 'product_product', 'parent_id', 'child_id')->withPivot('amount');
}
// movements
public function movements()
{
return $this->hasMany('App\Movement', 'product_id');
}
so any product can have "ideally" infinite ancestors and children.
Also any product is so a composition of other products as they are like "components".
So I need to return the amount of "producible" product based on availability of each children and their amounts needed to build the product.
No problems if this is a thing of one level depth, but the system should let you have many depth levels, so any child could be made from children as well.
I have no control on depth of each product->product->product chaining.
Apart from the performances issues, I need a recursive way to return the "producible" amount of each product in the chaining.
I managed how to get recursively the children till the end of the chain like this
public function child_products_recursive()
{
return $this->child_products()->with(['child_products_recursive','movements'=>function($query){
$query->whereDate('ready_date', '<=', date('Y-m-d'))->sum('amount');
}]);
}
and it works.
But can't figure out how to return values out of this or how to navigate each model.
Involved tables are "simplified" like that:
products
id | name
________________
1 | something
2 | something
3 | something
4 | something
5 | something
6 | something
7 | something
product_product
parent_id | child_id | amount (amount of children in the parent product)
___________________________________
1 | 2 | 2
1 | 3 | 4
2 | 4 | 1.5
2 | 5 | 3
4 | 6 | 0.7
4 | 7 | 0.3
movements table
id | product_id | date | amount
_______________________________
1 | 4 | 2016-01-06 | 300
2 | 5 | 2016-01-06 | 200
3 | 6 | 2016-01-06 | 125
4 | 7 | 2016-01-06 | 1000
so product ID 1 don't have movements but can be built in many pieces based on the child products that don't have movements but can be build by child products as well.
Thanks in advance. Ciao
Please or to participate in this conversation.