Page 1 of 2
orm problem: one object, multiple rows
Posted: Sat Nov 22, 2008 5:33 am
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?
Re: orm problem: one object, multiple rows
Posted: Sat Nov 22, 2008 6:10 pm
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
Re: orm problem: one object, multiple rows
Posted: Thu Nov 27, 2008 3:54 pm
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)
Re: orm problem: one object, multiple rows
Posted: Thu Nov 27, 2008 4:50 pm
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.
Re: orm problem: one object, multiple rows
Posted: Thu Nov 27, 2008 5:53 pm
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.
Re: orm problem: one object, multiple rows
Posted: Thu Nov 27, 2008 6:01 pm
by crazycoders
all spirit just said the exact same thing as me, just differently, so i guess i'm not far behind too
Re: orm problem: one object, multiple rows
Posted: Thu Nov 27, 2008 6:14 pm
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... :-/
Re: orm problem: one object, multiple rows
Posted: Fri Nov 28, 2008 7:00 am
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.
Re: orm problem: one object, multiple rows
Posted: Sat Nov 29, 2008 5:22 am
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!).
Re: orm problem: one object, multiple rows
Posted: Sat Nov 29, 2008 9:38 am
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
Re: orm problem: one object, multiple rows
Posted: Sat Nov 29, 2008 11:24 am
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.
Re: orm problem: one object, multiple rows
Posted: Sat Nov 29, 2008 12:40 pm
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.
Re: orm problem: one object, multiple rows
Posted: Sat Nov 29, 2008 1:51 pm
by josh
uhh or a subquery too. ...

Re: orm problem: one object, multiple rows
Posted: Sun Nov 30, 2008 9:26 am
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).
Re: orm problem: one object, multiple rows
Posted: Sun Nov 30, 2008 9:46 am
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...