orm problem: one object, multiple rows

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

orm problem: one object, multiple rows

Post by koen.h »

I don't see how orm classes solve the following problem:

One post can be in multiple categories. A query for a post would be an inner join on the categories table. Querying for post#25 which is in 2 categories means I get 2 rows back. Yet I want 1 object as result. Or similar: querying for 10 posts means a lot more rows, but what I need to get back from the orm class is 10 postObjects (eg with $post->categories an array of categories the post is in).

Did I miss something in the manuals I read?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: orm problem: one object, multiple rows

Post by josh »

Not sure what ORM you're referring to but most allow you to query for a record set which is basically an iterable object / collection that contains domain models
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: orm problem: one object, multiple rows

Post by crazycoders »

I see what you mean, and i am getting this in my new system where i have a dynamic language system, something like:

SELECT DISTINCT datasources.*, LSDName.languageid AS language, LSDName.data AS name
FROM datasources
LEFT OUTER JOIN LanguageStrings AS LSName ON LSName.part1 = 'dynamic' AND LSName.part2 = 'datasources.name' AND LSName.part3 = datasources.id
LEFT OUTER JOIN LanguageStringsData AS LSDName ON LSName.id = LSDName.languagestringid
WHERE datasources.id = 25

Would normally result in more than one row althought this query performs a search for 1 specific result. The only way to do this would be to use the DISTINCT clause but make sure that the fields returned by the query are only for the ONE specific object you want it to return. If you LEFT JOIN on a table that may create dupes of the same item then you have to limit the number of information returned to create only the 1 object you expected.

In my case, my result provided me always with 2 rows because i have two languages in my database, so to make this work, my code specifically asks the database to also return me only the active language.

In your case, since the query could be from the user, the only way to actually solve your problem is to remove the category table fields from the return clause and then requery for the categories of the object later in your code. Or else, you will always receive more than 1 row... it's a problem related to SQL (not MySQL but SQL in general)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: orm problem: one object, multiple rows

Post by josh »

Upon re-reading the original post it actually sounds like you're talking about dataMapper. I have no idea what crazycoders just said.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: orm problem: one object, multiple rows

Post by allspiritseve »

koen.h wrote:I don't see how orm classes solve the following problem:

One post can be in multiple categories. A query for a post would be an inner join on the categories table. Querying for post#25 which is in 2 categories means I get 2 rows back. Yet I want 1 object as result. Or similar: querying for 10 posts means a lot more rows, but what I need to get back from the orm class is 10 postObjects (eg with $post->categories an array of categories the post is in).

Did I miss something in the manuals I read?
I don't know that much about ORMs, but they should be giving you back objects, not rows. The reason you're getting 2 rows back is because you're trying to do in one query what should be done in 2 (resulting in duplication of the "one" side of the one-to-many relation). That's not necessarily a problem, the ORM should recognize the duplication and still give you one post object and 2 category objects. My preference would be to make two queries, one for each side of the relation... but I don't use ORMs.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: orm problem: one object, multiple rows

Post by crazycoders »

all spirit just said the exact same thing as me, just differently, so i guess i'm not far behind too
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: orm problem: one object, multiple rows

Post by allspiritseve »

crazycoders wrote:all spirit just said the exact same thing as me, just differently, so i guess i'm not far behind too
Oh... sorry about that, guess I wasn't following what you were saying... :-/
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: orm problem: one object, multiple rows

Post by josh »

crazycoders wrote:all spirit just said the exact same thing as me, just differently,
No he didn't. you said you would try to craft a query to get everything back in one shot, he was describing a data source architectural pattern
crazycoders wrote:In your case, since the query could be from the user, the only way to actually solve your problem is to remove the category table fields from the return clause and then requery for the categories of the object later in your code. Or else, you will always receive more than 1 row... it's a problem related to SQL (not MySQL but SQL in general)
Simply an incorrect assumption, sorry.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: orm problem: one object, multiple rows

Post by Kieran Huggins »

I must say, I was confused both times I read this thread....

An ORM should be writing the SQL for you, and compiling each result into an array of objects (one per "row"). In the case where you have several categories for one post, that single post object should probably represent the categories as an array of category objects (with __toStrong methods for added sexiness!).
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: orm problem: one object, multiple rows

Post by josh »

Just posting this for reference:
http://martinfowler.com/eaaCatalog/dataMapper.html
When you build an object model with a lot of business logic it's valuable to use these mechanisms to better organize the data and the behavior that goes with it. Doing so leads to variant schemas; that is, the object schema and the relational schema don't match up.

You still need to transfer data between the two schemas, and this data transfer becomes a complexity in its own right
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: orm problem: one object, multiple rows

Post by koen.h »

Kieran Huggins wrote:I must say, I was confused both times I read this thread....

An ORM should be writing the SQL for you, and compiling each result into an array of objects (one per "row"). In the case where you have several categories for one post, that single post object should probably represent the categories as an array of category objects (with __toStrong methods for added sexiness!).
The 'should' is important here. Unless I missed things I haven't seen one doing it. It's probably because it's not that simple (with one query). Suppose you ask the ORM for the latest 10 post. The ORM probably will use a 'limit 10' in its SQL query. But suppose all posts in that query have two categories, the SQL query will return only 5 post objects (10 rows, 2 for each post and joined categories). So how can an ORM return exactly 10 post objects? If it can't it's no true ORM, if it can I'd like to see how.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: orm problem: one object, multiple rows

Post by allspiritseve »

koen.h wrote:The 'should' is important here. Unless I missed things I haven't seen one doing it. It's probably because it's not that simple (with one query). Suppose you ask the ORM for the latest 10 post. The ORM probably will use a 'limit 10' in its SQL query. But suppose all posts in that query have two categories, the SQL query will return only 5 post objects (10 rows, 2 for each post and joined categories). So how can an ORM return exactly 10 post objects? If it can't it's no true ORM, if it can I'd like to see how.
Two queries.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: orm problem: one object, multiple rows

Post by josh »

uhh or a subquery too. ... :D
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: orm problem: one object, multiple rows

Post by koen.h »

I see how it would be done with 2 queries (or one subquery). Or an extra query for every type of object the target object consists of.

http://framework.zend.com/manual/en/zen ... ships.html seems to require a single object which isn't very optimal.

I think the most interesting approach would be to fetch n objects and have methods to fetch related objects for all of those n objects (rowset), or only of the current selected one (row).
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: orm problem: one object, multiple rows

Post by allspiritseve »

koen.h wrote:I think the most interesting approach would be to fetch n objects and have methods to fetch related objects for all of those n objects (rowset), or only of the current selected one (row).
I've been looking into this a bit lately, and there are a couple of things you can do with object relational mapping: when you select an object, it can just load all the relations for you. This is called eager fetching. The problem with this is you could end up pulling a huge object graph when you only need one object. You could also inject a fake object that loads the relation on first access, which is called lazy loading. The problem with this is you can end up making n+1 queries if you iterate through the whole collection. Another alternative is batch lazy load, where you lazy load the collection, and the first object to access the collection loads the whole collection. Every subsequent access just loads from the collection instead of making a new query.

Edit: I guess there's probably lazy loading of individual properties too... that seems like it'd be really uncommon though. I guess if there's a serialized LOB or something that's really big, and you don't use it that often...
Last edited by allspiritseve on Thu Dec 04, 2008 1:28 am, edited 1 time in total.
Post Reply