Self Referencing on a separate table - BoM in Laravel

Question

I'm curious if there's an easy way of implementing a bill of materials (assemblies) type recursive system using eloquent? Here are the two 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 and 4 table legs'.

Inventory Model:

class Inventory extends Eloquent
{
    public function assemblies()
    {
        return $this->hasMany('InventoryAssembly', 'inventory_id', 'id');
    }

    /**
     * Returns all of the assemblies items recursively.
     *
     * @param bool $recursive
     *
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function getAssemblyItems($recursive = true)
    {
        /*
         * Grab all of the current item's assemblies not including itself
         */
        $assemblies = $this->assemblies()->where('part_id', '!=', $this->id)->get();

        $items = new Collection();

        // We'll go through each assembly
        foreach ($assemblies as $assembly)
        {
            // Get the assembly part
            $part = $assembly->part;

            if ($part)
            {
                // Dynamically set the quantity attribute on the item
                $part->quantity = $assembly->quantity;

                // Dynamically set the assembly ID attribute to the item
                $part->assembly_id = $assembly->id;

                // If recursive is true, we'll go through each assembly level
                if($recursive)
                {
                    if($part->is_assembly)
                    {
                        /*
                         * The part is an assembly, we'll create a new
                         * collection and store the part in it's own array key,
                         * as well as the assembly.
                         */
                        $nestedCollection = new Collection([
                            'part' => $part,
                            'assembly' => $part->getAssemblyItems(),
                        ]);

                        $items->add($nestedCollection);

                    } else
                    {
                        // The part isn't an assembly, we'll just add it to the list
                        $items->add($part);
                    }
                } else
                {
                    /*
                     * Looks like the dev only wants one level
                     * of items, we'll just add the part to the 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 this works, however if I add an item to the assembly of itself, it get an infinite loop. So here are my questions:

• How can I prevent infinite recursive queries?
• Am I doing this right?
• Is there an easier way?
• Would a BoM model be better suited towards a nested set design?

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

EDIT: Using a belongs to many relationship on the inventory model itself suggested by user3158900, I'm able to perform a recursive assembly query like so:

The Inventory model (modified from 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 a 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;

Answer 1

This gets a lot easier. This may not look like it, but it's actually belongs-to-many where Inventory belongs to many itself and inventory_assembly is a pivot table and actually doesn't even require a model to go along with it.

Here is the inventory model

class Inventory extends Eloquent {

    public function assemblies()
    {
        return $this->belongsToMany('Inventory', 'inventory_assemblies', 'inventory_id', 'part_id')
            ->withPivot(['quantity']);
    }

}

And here is how I got a collection of assemblies for a certain inventory item.

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

$assemblies = $item->assemblies;

Edit: Just realized you were using Laravel 4. Removed namespaces.

Another Edit: I wouldn't count this solved just yet. If for example legs is an assembly which requires a leg and hardware, hardware is an assembly which requires different nuts/bolts/ sets of tools, if sets of tools is an assembly which requires wrench a and screwdriver b, etc... This method will only get you as deep as the leg and will ignore everything else.

In this case, we are going about everything all wrong and ignore everything I've said. This is what's known as a nested set model. You can read more about it here http://en.wikipedia.org/wiki/Nested_set_model

In this case, there is also a Laravel package which should handle this relationship for you. It's saved me a few times from smacking my head against a wall. https://github.com/etrepat/baum#node-relations.

For nested relationships in Eloquent, this is also possible and you would no longer need your assemblyRecursive function. You can go as deep as you need to.

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

foreach($item->assemblies as $assembly) {
    if($assembly->is_assembly) {
        // Do things for parent items
        foreach($assembly->assemblies as $nested_assembly_a) {
            // Do things for 1 deep nests

            if($nested_assembly_a->is_assembly) {
                foreach($nested_assembly_a->assemblies as $nested_assembly_b) {
                    // Do things for 2 deep nests

                }
            } else {
                // Non-assembly 1 deep child
            }
        }
    } else {
        // Non-Assembly parent
    }
}