Query/Join Multiple Tables with xPDO

129 Posts

ambaxter Reply #1, 8 years, 4 months ago

I've been searching the forums for what I need and none of the postings that talks about multiple tables fit my situation exactly.

I have 3 separate tables (yes, the need to be separate tables) that all have different types of data for the same list of products.

I need to be able to create a page for a product and display all of these cells of data using simple placeholders.

My Package is all set up and ready to go and I know how to do this with a single table using getCollection. But I can't seem to figure out how I would join these so that ALL of the attributes will be available to me for use in TPLs.

Has anyone done something like this.

This question has been answered by imjamespeter. See the first response.

5,888 Posts

Bruno17 Reply #2, 8 years, 4 months ago

whats your xpdo-schema?

I think, you can do, what you want, for example, with migxLoopCollection

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #3, 8 years, 4 months ago

If (and it's a big if) you've properly created the tables as related objects of each other, you can use getCollectionGraph() to get them all in a single query. You can also use $query->leftJoin() with getCollection().

Here's getCollectionGraph() example for users that includes the user profile and custom data. In the example, Profile and Data are aliases of the two user-related objects. The result is an array of $userObjects, each with its own $user->Profile and $user->Data object containing the fields for that user from the other tables:

$c = $modx->newQuery('modUser');
$c->sortby('fullname', 'ASC');
$c->where(
   array('Data.last_name' => 'Johnson'),
);
$users = $modx->getCollectionGraph('modUser', '{"Profile":{},"Data":{}}', $c);

In the second argument to getCollectionGraph(), you can have as many related objects as you need in the JSON section.

Remove the $c->where() part of you want all of them.

To process the results you can do something like this (off the top of my head, so possibly not correct):

foreach ($users as $user) {
   $pFields = $user->Profile->toArray();
   $dFields = $user->Data->toArray();
   $ufields = $user->toArray();

   $allFields = array_merge($pFields, $dFields, $uFields);

   $output .= $modx->getChunk('MyTpl', $allFields);
}

Be sure to put the $user fields as the last argument in the array_merge() so that the user ID will be correct.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

129 Posts

ambaxter Reply #4, 8 years, 4 months ago

Thanks all,

Bob: I had gotten getCollectionGraph set up the way you describe, but it only comes back with data from the main table in the query. In the case of your example, I'm getting an error on Profile->toArray and Data->toArray.

"Call to a member function toArray() on a non-object"

I can't post my schema, but I can say that I have a common column in all 3 tables that contains the product ID. It's not the primary key, but the id/model of each product is present in all 3 tables and that's what I have in my where clause.

129 Posts

ambaxter Reply #5, 8 years, 4 months ago

here is a representation of my schema

<?xml version="1.0" encoding="UTF-8"?>
<model package="Catalog" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Data1" table="data1" extends="xPDOSimpleObject">
		<field key="model_number" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="number_of_corners" dbtype="decimal" precision="3,1" phptype="float" null="false" />
		<field key="depth_of_groove" dbtype="decimal" precision="4,2" phptype="float" null="false" />
	</object>
	<object class="Data2" table="data2" extends="xPDOSimpleObject">
		<field key="model_number" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="fabric" dbtype="decimal" precision="7,5" phptype="float" null="false" />
		<field key="coating" dbtype="varchar" precision="255" phptype="string" null="true" />
	</object>
	<object class="Data3" table="data3" extends="xPDOSimpleObject">
		<field key="model_number" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="height" dbtype="decimal" precision="6,2" phptype="float" null="false" />
		<field key="width" dbtype="decimal" precision="6,2" phptype="float" null="false" />
	</object>
</model>

5,888 Posts

Bruno17 Reply #6, 8 years, 4 months ago

example with migxLoopCollection

[[migxLoopCollection?
&classname=`modUser`
&joins=`[{"alias":"Profile"}]`
&tpl=`yourTplChunk`
]]

in this case, all the profile-fields have a prefix of Profile_

so you can get them, for example with

[[+Profile_street]]

for your custom-package, you will need &packageName=`yourpackage`
can't say more, without knowing your schema.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

5,888 Posts

Bruno17 Reply #7, 8 years, 4 months ago

Not sure, if it is good, to have the packageName starting with uppercase.
I think, its better, to have everthing lowercase.
In this case, you can try

[[migxLoopCollection?
&packageName=`Catalog`
&classname=`Data1`
&joins=`[
{"alias":"Data2","classname":"Data2","on":"Data2.model_number=Data1.model_number"},
{"alias":"Data3","classname":"Data3","on":"Data3.model_number=Data1.model_number"}
]`
&tpl=`yourTplChunk`
&where=`{"Data1.model_number":"themodelnumber"}`
]]

you will have all the Data1-fields without prefix and the other fields with prefix Data1_ and Data2_

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

129 Posts

ambaxter Reply #8, 8 years, 4 months ago

Both of these solutions look good. I ultimately want to know how to write my joins. But I'm gonna give migxLoopCollection a try.

If it's best practice, I'll go ahead and rename my package. This was my first time writing it that way.

129 Posts

ambaxter Reply #9, 8 years, 4 months ago

Bruno: I tried using the migxLoopCollection call you proposed and it returns nothing. When I add &debug=`1`, I see the query data and it all looks like it's assuming the db prefix of "modx_" which is not correct.

129 Posts

ambaxter Reply #10, 8 years, 4 months ago

Nevermind, I took another guess and added &prefix=`` and now it's working as expected!

Answered Query/Join Multiple Tables with xPDO