Robert Cesaric Sun Aug 15 14:53:57 -0400 2010

Subject: Returning nested sets of data

I'm playing around with the "Orders" example provided in the download.

After running the example successfully, I've been trying to return a data set with all "people" and all of their associated "orders" nested in the result set with no luck. Is this possible? I've tried numerous variations of the "select, from, join" options and can't seem to get it.

I'm curious because this gives a result:
$result = Person::all(array('select' => 'p.*', 'from'=>'people p' ));

But this errors:
$result = Person::all(array('select' => 'p.*', 'from'=>'people p', 'joins' => array('orders') ));

Fatal error: Uncaught exception 'ActiveRecord\DatabaseException' with message '42S22, 1054, Unknown column 'people.id' in 'on clause'' in /Users/robert/Workspace/zagweb/PHP Active Record/src_php/lib/Connection.php on line 27

Not sure of the syntax to alias the join table so that I can return "p.*" and "o.*" (orders o).

Any help would be appreciated. Thank you!


Michael Smith Sun Aug 15 18:48:58 -0400 2010

Hi Robert,

So far as I can tell, so long as you have the associations set up properly then you would do something like:

$people = Person:all();
foreach ($people as $person) {
print_r($person->orders);
}

Does that help at all?

Robert Cesaric Sun Aug 15 20:13:12 -0400 2010

Hi Michael. The associations seem to be setup correctly. I just ran your code snippet. It does output each "order" entity but doesn't output any "person" entities.

What I'm really looking for is having a single data graph of all the people and their associated orders. The reason being is I want to setup PHP Active Record as a service for other technologies like Flash. In those cases, I don't want to make multiple calls just to iterate through the nested data and I don't want to necessarily do the same in looping in PHP.

If I could do this in a single ActiveRecord query like this:
$result = Person::all(array('select' => 'p.*, o.*', 'from'=>'people p', 'joins' => array('orders o') ));

I would be in heaven.

Michael Smith Tue Aug 17 06:34:59 -0400 2010

In that case, have you investigated something like:

$people = Person::all();
foreach ($people as $person) {
echo $person->to_json();
}

Don't know if that will get you the associated orders as well but might be worth a shot. also with it just being JSON data it will be easily portable to Flash, JavaScript, or any other associated technology as its a nice light weight data exchange format. If it doesn't then you might just need

There is also a to_xml method which might be helpful to you.

For more info about to_json and others, take a look here: http://www.phpactiverecord.org/docs/ActiveRecord/Model#methodto_json

Robert Cesaric Tue Aug 17 08:45:21 -0400 2010

Thanks Michael. I ran your script but no go. I think fundamentally, this needs to be done in an ActiveRecord query as I noted in my last comment.

If you're familiar with an ORM like Doctrine, it's similar to running a multi-join DQL statement that returns a nested object data graph. No looping is necessary. I can then just return the result set back to the calling technology.

Kien La Sun Aug 22 11:45:05 -0400 2010

I think you want the include option. Try:

Person::all(array('include' => 'orders'));

I know this isn't documented anywhere at the moment. Sorry about that. I'll try to get that documented.

Robert Cesaric Thu Aug 26 14:08:17 -0400 2010

That worked. Thanks Kien!

(1-6/6)