Need clarification about nested models

Question

I need an example on how to operate on nested models.

Let's assume, we have some order management application and models: Order, Position, Item.

Order can contain Positions and Positions can contain Items.

The question is: how to handle data selection?

Some use cases that is needed:

• Get list of Orders with Positions and Items (like in my example code)
• Get only list of Orders (without Positions and Items)
• Get some statistics, for example, total amount of all Items in single Position

I can do something like that:

class Order_Mapper
{
    public function getOrders($criteria)
    {
        $orders = array(); // Get order list from DB

        foreach($orders as $order)
        {
            $positions = Position_Mapper::getPositionsByOrderId($order->id);

            foreach($positions as $position)
            {
                $order->addPosition($position);
            }
        }

        return $orders;
    }
}

class Position_Mapper
{
    public function getPositionsByOrderId($order_id)
    {
        $positions = array(); // Get position list from DB

        foreach($positions as $position)
        {
            $items = Item_Mapper::getItemsByPositionId($position->id);

            foreach($items as $item)
            {
                $position->addItem($item);
            }
        }

        return $positions;
    }
}

... but this is inefficient because there is many SQL queries needed, one for order select, then one for each Order to get Positions and then one for each Position to get Items.

I'm pretty inexperienced in MVC so any help is welcome!

---

In response about Doctrine - I really didn't want to learn new ORM suite, nor I don't mind writing SQL queries by hand. I just need the idea how to accomplish aforementioned tasks.

Here is my second try to do all of the use cases mentioned above without overhead. Please, take a look and tell me your thoughts. Code is very simple.

Answer 1

In a case like this I would use an advanced object relational mapper like Doctrine. Then you can avoid the n+1 problem and use complex queries with JOINs in order to retrieve related data at once instead of selecting all orders and then in a loop select all positions for each order etc. You are freed from writing the mapping code yourself which maps the database structure to objects. Instead you just use Repositories that expose methods like getAllOrders etc. specify your queries there and let Doctrine do the heavy lifting.

When you need a more row-like approach for report-style queries for statistics etc. Doctrine afaik also supports native queries where you go directly to the database and use custom SQL.

Some more information on that topic:

• Wikipedia article on object relational mapping: http://en.wikipedia.org/wiki/Object-relational_mapping
• Martin Fowlers article on the repository pattern: http://martinfowler.com/eaaCatalog/repository.html

There are quite a few questions on these topics on stackoverflow, too. These are just a few hints to get you started. As you don´t seem to have much experience on these topics, be warned: there is a learning curve, but it´s really worth it as you can tame even complex scenarios with these technologies and patterns.