We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 1331
    • 129 Posts
    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.

      • 4172
      • 5,888 Posts
      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!
        • 3749
        • 24,544 Posts
        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
          • 1331
          • 129 Posts
          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.
            • 1331
            • 129 Posts
            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>
              • 4172
              • 5,888 Posts
              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!
                • 4172
                • 5,888 Posts
                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!
                  • 1331
                  • 129 Posts
                  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.
                    • 1331
                    • 129 Posts
                    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.
                      • 1331
                      • 129 Posts
                      Nevermind, I took another guess and added &prefix=`` and now it's working as expected!