orm problem: one object, multiple rows
Moderator: General Moderators
orm problem: one object, multiple rows
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?
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
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
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)
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
Upon re-reading the original post it actually sounds like you're talking about dataMapper. I have no idea what crazycoders just said.
- 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
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.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?
-
crazycoders
- Forum Contributor
- Posts: 260
- Joined: Tue Oct 28, 2008 7:48 am
- Location: Montreal, Qc, Canada
Re: orm problem: one object, multiple rows
all spirit just said the exact same thing as me, just differently, so i guess i'm not far behind too
- 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
Oh... sorry about that, guess I wasn't following what you were saying... :-/crazycoders wrote: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
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 patterncrazycoders wrote:all spirit just said the exact same thing as me, just differently,
Simply an incorrect assumption, sorry.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)
- 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
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!).
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
Just posting this for reference:
http://martinfowler.com/eaaCatalog/dataMapper.html
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
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.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!).
- 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
Two queries.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.
Re: orm problem: one object, multiple rows
uhh or a subquery too. ... 
Re: orm problem: one object, multiple rows
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).
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).
- 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
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.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).
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.