Self Referencing on a separate table - BoM in Laravel -

i'm curious if there's easy way of implementing bill of materials (assemblies) type recursive system using eloquent? here 2 table structures i'm working with:

inventory table:

+----+------------+-------------+ | id | name | is_assembly | +----+------------+-------------+ | 1 | table | 1 | +----+------------+-------------+ | 2 | table top | 0 | +----+------------+-------------+ | 3 | table legs | 0 | +----+------------+-------------+

inventory_assemblies table:

+----+--------------+---------+----------+ | id | inventory_id | part_id | quantity | +----+--------------+---------+----------+ | 1 | 1 | 1 | 1 | +----+--------------+---------+----------+ | 2 | 1 | 2 | 1 | +----+--------------+---------+----------+ | 3 | 1 | 3 | 4 | +----+--------------+---------+----------+

this assembly table should mean '1 table contains 1 table top , 4 table legs'.

inventory model:

class inventory extends eloquent {     public function assemblies()     {         return $this->hasmany('inventoryassembly', 'inventory_id', 'id');     }      /**      * returns of assemblies items recursively.      *      * @param bool $recursive      *      * @return \illuminate\database\eloquent\collection      */     public function getassemblyitems($recursive = true)     {         /*          * grab of current item's assemblies not including          */         $assemblies = $this->assemblies()->where('part_id', '!=', $this->id)->get();          $items = new collection();          // we'll go through each assembly         foreach ($assemblies $assembly)         {             // assembly part             $part = $assembly->part;              if ($part)             {                 // dynamically set quantity attribute on item                 $part->quantity = $assembly->quantity;                  // dynamically set assembly id attribute item                 $part->assembly_id = $assembly->id;                  // if recursive true, we'll go through each assembly level                 if($recursive)                 {                     if($part->is_assembly)                     {                         /*                          * part assembly, we'll create new                          * collection , store part in it's own array key,                          * assembly.                          */                         $nestedcollection = new collection([                             'part' => $part,                             'assembly' => $part->getassemblyitems(),                         ]);                          $items->add($nestedcollection);                      } else                     {                         // part isn't assembly, we'll add list                         $items->add($part);                     }                 } else                 {                     /*                      * looks dev wants 1 level                      * of items, we'll add part list                      */                     $items->add($part);                 }             }         }          return $items;     } } 

inventoryassembly model:

class inventoryassembly extends basemodel {     public function item()     {         return $this->belongsto('inventory', 'inventory_id', 'id');     }      public function part()     {         return $this->belongsto('inventory', 'part_id', 'id');     } } 

now works, if add item assembly of itself, infinite loop. here questions:

• how can prevent infinite recursive queries?
• am doing right?
• is there easier way?
• would bom model better suited towards nested set design?

i'm having trouble understanding self-referencing recurring queries. really appreciate any help, in advance!!

edit: using belongs many relationship on inventory model suggested user3158900, i'm able perform recursive assembly query so:

the inventory model (modified below answer):

class inventory extends eloquent {      public function assemblies()     {         return $this->belongstomany('inventory', 'inventory_assemblies', 'inventory_id', 'part_id')             ->withpivot(['quantity']);     }      public function assembliesrecursive()     {         return $this->assemblies()->with('assembliesrecursive');     }  } 

retrieving single level of assemblies:

$item = inventory::with('assemblies')->find(1);  $items = $item->assemblies; 

retrieving complete recursive assemblies results:

$item = inventory::with('assembliesrecursive')->find(1);  $items = $item->assembliesrecursive;  $nesteditems = $items->get(0)->assemblies;  $nestednesteditems = $items->get(0)->assemblies->get(0)->assemblies; 

this gets lot easier. may not it, it's belongs-to-many inventory belongs many , inventory_assembly pivot table , doesn't require model go along it.

here inventory model

class inventory extends eloquent {      public function assemblies()     {         return $this->belongstomany('inventory', 'inventory_assemblies', 'inventory_id', 'part_id')             ->withpivot(['quantity']);     }  } 

and here how got collection of assemblies inventory item.

$item = inventory::with('assemblies')->find(1);  $assemblies = $item->assemblies; 

edit: realized using laravel 4. removed namespaces.

another edit: wouldn't count solved yet. if example legs assembly requires leg , hardware, hardware assembly requires different nuts/bolts/ sets of tools, if sets of tools assembly requires wrench , screwdriver b, etc... method deep leg , ignore else.

in case, going wrong , ignore i've said. what's known nested set model. can read more here http://en.wikipedia.org/wiki/nested_set_model

in case, there laravel package should handle relationship you. it's saved me few times smacking head against wall. https://github.com/etrepat/baum#node-relations.

for nested relationships in eloquent, possible , no longer need assemblyrecursive function. can go deep need to.

$item = inventory::with('assemblies.assemblies.assemblies')->find(1);  foreach($item->assemblies $assembly) {     if($assembly->is_assembly) {         // things parent items         foreach($assembly->assemblies $nested_assembly_a) {             // things 1 deep nests              if($nested_assembly_a->is_assembly) {                 foreach($nested_assembly_a->assemblies $nested_assembly_b) {                     // things 2 deep nests                  }             } else {                 // non-assembly 1 deep child             }         }     } else {         // non-assembly parent     } }