DataMapper deep relationships, distinct results and looping

[eluser]Unknown[/eluser]
Hello. Been using the very interesting DataMapper ORM for CI for a few days now and I'm kind of stuck with making this work:
I have Products that stay in Shelves, that are inside Displays, that are inside Stores.

So I think the relationships are:
Product -> has_many -> Shelf
Shelf -> has_one ->Display
Shelf -> has_many -> Product
Display -> has_one -> Store
Display -> has_many -> Shelf
Store -> has_many -> Display

My classes look like this:

Product:

Code:

var $has_many = array(
'shelf' => array(
  'class' => 'shelf',
  'other_field' => 'product'
)
);

Shelf:

Code:

var $has_one = array(
'display'
);
var $has_many = array(
'product' => array(
  'class' => 'product',
  'other_field' => 'shelf'
)
);

Display:

Code:

var $has_one = array(
'store',
);
var $has_many = array(
'shelf' => array(
  'class' => 'shelf',
  'other_field' => 'display'
)
);

Store:

Code:

var $has_many = array(
'display',
);

In my view, I'm trying to build a list of Products that includes product details (id, name), "number of Shelves the Product is on" and "number of Stores" the Product is in".

I'm not sure how to build the query.
I did try:

Code:

p->include_related_count('shelf')
->include_related('shelf')
->distinct()
->get();

Then I looped through the Products trying to get Shelves related to Product, then Displays related to the resulting Shelves, then Stores related to those resulting Displays. But it looks like a big mess and I can't shake the feeling I'm doing something wrong and there's a much better way to do this...

Could I use $p->include_related('shelf/display/store'), maybe?

Can anyone help? A way to chain DM methods to achieve more with less queries? A way to avoid so much looping and "sub"-looping? A way to avoid duplicate results with Products?

Thanks a lot!

[eluser]WanWizard[/eluser]
A join over so many tables is bound to have duplicate info, that is the nature of a join.

Datamapper doesn't instantiate all the objects that would be the result of such a query, like more modern ORM's do, it has a design from the PHP4 era, where instantiating an object was a very expensive thing to do.

Bottom line: it's quite suitable for standard CRUD activities and single relationship handling, but when it gets more complicated, DM will show its age...

[eluser]Unknown[/eluser]
Thanks for the reply, was hoping you'd do that.
The biggest performance problems (I think) might only arise in the admin dashboard area of my app, as it tries to pull a lot of info and counts from all objects in the database.
So, what's your take on this? Should I ditch the ORM, should I try another ORM for CI, should I just loop over results with DM even if that creates a ton of queries?

Products sit on Shelves that are part of Displays that are inside Stores. In how many Stores is this Product present?
It just feels like it shouldn't be that complicated...

[eluser]WanWizard[/eluser]
If you have to stick with CI, and you are familiar with DM, I'd say you stick with it.

The few queries that you can't easily construct you can create using the standard CI query builder. You can even have the result of that converted back to DM objects if you want, that works as long as the result contains the models primary key.

[eluser]dragonfly41[/eluser]
Here's another approach for you to consider ...

Currently for my application (navigation through large tree structures) I'm integrating eXist-db as a backend XML database into CI.

http://www.exist-db.org/exist/apps/homepage/index.html

Exist-db runs on a dedicated jetty server and offers either RESTful API or SOAP API.

In y Ubuntu server I have it installed in ~/eXist-db/ and to start I run ~/eXist-db/bin/startup.sh

Change setup port and username/password in config.

If you use REST look through RestXQ tutorials.

My personal approach from the above options is to use phExist as a PHP SOAP client held in /libraries/
.
I've adapted this SOAPclient class to run in a CI model.

http://query-exist.sourceforge.net/

You can then submit complex XQuery through the interface.

Your collection (db) name might be "Stores" Documents might be "Products".

It may be that your requirement is better suited to REST API which is the preferred option in eXist-db and is well documented.