opengeek, how do you do inner joins with xpdo? i’ve seen i can use getCollection to return an array of objects, but surely there’s a better way to retrieve each row than by using get() inside a foreach loop?
once you have an object you can call object->getMany(’some object’)
that will return all of the related objects, i.e. an inner join
this is just what i was looking for, thanks.
Quote from: OpenGeek at Jan 22, 2007, 06:08 PM
There is not currently a built-in method to fetch the values from multiple tables into a multiple xPDO-managed objects directly, though I am working on a function to do just that.
Can you just confirm that there’s no issue in combining multiple tables into a single xPDO-managed object using a properly constructed inner join? eg I have a table of customers and a table of orders, and customers may not actually have ordered something, yet I wish to produce a list of customers ordered by their total order values (ie all the £0 would be at the end). Can I still do that with a single SQL statement?
@Paul, if you want to return back all customers even ones with out an order you would need to do a left/right (depending on how you order the tables) or a full join. If you do an inner join you will only get back results where a record exists in both tables.
Quote from: kylej at Jan 23, 2007, 08:35 AM
@Paul, if you want to return back all customers even ones with out an order you would need to do a left/right (depending on how you order the tables) or a full join. If you do an inner join you will only get back results where a record exists in both tables.
Oops, bad example! Yes, that would be a left join.
But to return to the question I was trying to make, does anything in xPDO stop me combining multiple tables into a single xPDO-managed object using just a properly constructed inner join (or left join), rather than writing lots of code to combine two objects? I’m not sure why redman needs to jump through so many hoops.
-
MODX Staff
- 10,725 Posts
What hoops are we talking about jumping through? This thing does not infer and auto-design tables for many-to-many relationships. And I refuse to take that approach with xPDO. If you want this, there are other O/RMs available, and both have lots code to automatically handle complex relationships, and limit you to their way of approaching them. As I said, their is no code in xPDO currently to automate many-to-many relationships, or auto-construct join queries, but I was planning on adding a helper function for this, as I mentioned.
And this is what a domain model is for. xPDO generates the basic scaffolding and metadata for each object; you identify the complex queries you need to have common functions for, and add those as supplemental functions in the classes as needed. In other words, you could create a getAllPhoneNumbers() function and have it issue what would basically be a single, custom getMany through two object relations.
In this way, we can optimize this kind of more complex functionality for the database platform being targeted. This is the difference between SQL optimization layer (as I call it) and a SQL abstraction layer