Converting Join statement to NotORM

[Brett Powell]

I am rewriting some old code to make it more efficient, opting to use the NotORM database library in place of MySQL. I have never mastered writing queries but can usually make them work as needed. I wrote the following JOIN query statement which I am not trying to replace using the syntax from NotORM.

I am not sure what I am doing wrong when trying to convert this. I have followed the documentation as closely as possible with no luck. Can anyone perhaps shed some light on what it is that is wrong?

Original:

$query = "SELECT devices_inventory.inventory_id, inventory_type.id, inventory_type.sort_order FROM devices_inventory
            JOIN inventory
                ON inventory.id = devices_inventory.inventory_id
            JOIN inventory_type
                ON inventory_type.id = inventory.type
            WHERE devices_inventory.device_id = " . $device_id . " ORDER BY inventory_type.sort_order ASC";

NotORM:

$inventory = $GLOBALS['db']->devices_inventory()->select("devices_inventory.inventory_id, inventory_type.id, inventory_type.sort_order")->where("devices_inventory.device_id", $id)->order("inventory_type.sort_order ASC");

foreach ($inventory as $item)
{
    echo $item->devices_inventory['inventory_id'] . "</br>" . $item->inventory_type['id'] . "</br>" . $item->inventory_type['sort_order'] . "</br>";
}

[Allysson David]

This will get you what you want:

echo $db->inventory()->select('devices_inventory:inventory_id, inventory_type.id, inventory_type.sort_order')->where('devices_inventory.device_id', $device_id)->order('inventory_type.sort_order ASC');

Note that you don't need to select anything else but `devices_inventory`:`inventory_id` for this to work, it's not needed to select the column to order using it, you only need to call ->order() appropriately.

The only problem you'll have here is with your naming pattern for the foreign_key `inventory`.`type`.

Since that column is referencing the table `inventory_type` it should have been called `inventory_type_id`.

If it isn't too troublesome, please rename it to accommodate NotORM.

If it is too troublesome, please read more below:

The naming can be worked around by customizing NotORM's Structure like this:

class myStructure extends NotORM_Structure_Convention {
    function getReferencedColumn($name, $table) {
        if($name == 'inventory_type' && $table == 'inventory')
            return 'type';
        return parent::getReferencedColumn($name, $table);
    }
}

Remember to build your NotORM object using the custom Structure:

$db = new NotORM($pdo, new myStructure());