New Community Forums are coming. Watch this space for news.
Subscribe: RSS
  • if want to build it by yourself.
    The code would look like that:

    $classname = 'Data1';
    
    $c = $modx->newQuery($classname);
    $c->select($modx->getSelectColumns($classname, $classname));
    
    $joinclass = 'Data2';
    $jalias = 'Data2';
    $on = 'Data2.model_number=Data1.model_number';
    
    $c->leftjoin($joinclass, $jalias, $on);
    $c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));
    
    $joinclass = 'Data3';
    $jalias = 'Data3';
    $on = 'Data3.model_number=Data1.model_number';
    
    $c->leftjoin($joinclass, $jalias, $on);
    $c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));
    
    
    if ($collection = $modx->getCollection($classname, $c)) {
        foreach ($collection as $object) {
            $row = $object->toArray();
            $output .= $modx->getChunk($tpl, $row);
        }
    }
    


    or a little bit faster and less memory-usage:

    $classname = 'Data1';
    
    $c = $modx->newQuery($classname);
    $c->select($modx->getSelectColumns($classname, $classname));
    
    $joinclass = 'Data2';
    $jalias = 'Data2';
    $on = 'Data2.model_number=Data1.model_number';
    
    $c->leftjoin($joinclass, $jalias, $on);
    $c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));
    
    $joinclass = 'Data3';
    $jalias = 'Data3';
    $on = 'Data3.model_number=Data1.model_number';
    
    $c->leftjoin($joinclass, $jalias, $on);
    $c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));
    
    $c->prepare();
    $rows = array();
    if ($c->stmt->execute()) {
        if (!$rows = $c->stmt->fetchAll(PDO::FETCH_ASSOC)) {
            $rows = array();
        }
    }
    
    foreach ($rows as $row) {
        $output .= $modx->getChunk($tpl, $row);
    }
    
    
    [ed. note: Bruno17 last edited this post 3 years, 2 months ago.]
      -------------------------------

      you can buy me a beer, if you like MIGX

      http://webcmsolutions.de/migx.html

      Thanks!
    • Awesome! Thanks for your help guys. I'm gonna have to try that. I'll report back once I have had a chance to write my own.
      • Quote from: ambaxter at Dec 14, 2015, 06:17 AM
        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">
        	</field></field></field></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">
        	</field></field></field></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">
        	</field></field></field></object>
        </model>

        In the example provided you first need to define the relationships between the tables: https://rtfm.modx.com/xpdo/2.x/getting-started/creating-a-model-with-xpdo/defining-a-schema/defining-relationships
        • Yes, if you want to use getObjectGraph(), you'll need some aliases in your schema to relate the objects to each other. You can look at the MODX schema, especially the modUser object, for examples:

          core\model\schema\modx.mysql.schema.xml
            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
          • Quote from: BobRay at Dec 14, 2015, 10:54 PM
            Yes, if you want to use getObjectGraph(), you'll need some aliases in your schema to relate the objects to each other. You can look at the MODX schema, especially the modUser object, for examples:

            core\model\schema\modx.mysql.schema.xml

            I see, thanks for the extra information. I'll get a grasp of these concepts soon enough.
            • Quote from: Bruno17 at Dec 14, 2015, 01:20 AM
              if want to build it by yourself.
              The code would look like that:

              $classname = 'Data1';
              
              $c = $modx->newQuery($classname);
              $c->select($modx->getSelectColumns($classname, $classname));
              
              $joinclass = 'Data2';
              $jalias = 'Data2';
              $on = 'Data2.model_number=Data1.model_number';
              
              $c->leftjoin($joinclass, $jalias, $on);
              $c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));
              
              $joinclass = 'Data3';
              $jalias = 'Data3';
              $on = 'Data3.model_number=Data1.model_number';
              
              $c->leftjoin($joinclass, $jalias, $on);
              $c->select($modx->getSelectColumns($joinclass, $jalias, $jalias . '_'));
              
              
              if ($collection = $modx->getCollection($classname, $c)) {
                  foreach ($collection as $object) {
                      $row = $object->toArray();
                      $output .= $modx->getChunk($tpl, $row);
                  }
              }
              



              Is it possible to do the same thing as this but join the second table to the third one?
              In the above example you're joining Data1 to Data2 and then joining Data1 to Data3.
              In xPDO can you join Data1 to Data2 and then join Data2 to Data3?

              Im trying to do a join across a many-to-many relationship and failing so far.

              Here's a good example of what I'm trying to do: https://lornajane.net/posts/2011/inner-vs-outer-joins-on-a-many-to-many-relationship
              Plain SQL is simple but can't quite work it out with xPDO.
                I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
                Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com
              • To give a real world example, here's my SQL:

                SELECT s.id AS subject_id, s.subject_name_en, ls.is_active, l.location_name_en, tg.group_name
                FROM cw_subject s 
                JOIN cw_location_subject ls ON (s.id = ls.subject_id)
                JOIN cw_location l ON (ls.location_id = l.id)
                JOIN cw_term_group tg ON (l.id = tg.locationsubject_id);
                



                It works perfectly. I'm joining 4 tables together.
                cw_subject ---- cw_location_subject ---- cw_location
                                       |
                                       |
                                 cw_term_group
                


                The class/alias in the xPDO schema are as follows:
                cw_subject          = Subject/Subject
                cw_location_subject = LocationSubject/LocationSubject
                cw_location         = Location/Location
                cw_term_group       = TermGroup/TermGroup
                


                Would anyone know how to represent the same query in xPDO?


                Update:

                OK. Here's my xPDO code for the first three tables:
                $query = $foreignDB->newQuery('Subject');
                $query->select('Subject.id,subject_name_en');
                $query->innerJoin('LocationSubject','LocationSubject');
                $query->select($foreignDB->getSelectColumns('Location','Location','',array('Location.id','location_name_en')));
                $query->leftJoin('Location','Location','LocationSubject.location_id = Location.id');
                
                $query->prepare();
                print $query->toSQL();
                


                This prints out the SQL code and if I paste it into PHPMyAdmin, it works as expected. After all this hair-pulling it turns out it's the getCollection() function that doesn't like the above.
                The rest of my code is:
                $subjects = $foreignDB->getCollection('Subject',$query);
                foreach($subjects as $subject) {
                    $subjectArray = $subject->toArray('',false,true); // must set third arg to true due to toArray() lazy load.
                    echo '<pre>';
                    print_r($subjectArray);
                    echo '</pre>';
                }
                

                This only returns columns from the Subject table and ignores the rest.


                I found another post here with what looks to be the same problem
                https://forums.modx.com/thread/32455/xpdo-retrieving-joined-rows-out-of-many-to-many-tables#dis-post-177416

                It's not a lot to go on but it appears what @opengeek is saying is that I need to use PDO instead?

                This works but obviously isn't xPDO:
                $results = $foreignDB->query("
                SELECT s.id AS subject_id, s.subject_name_en, ls.is_active, l.location_name_en 
                FROM cw_subject s 
                JOIN cw_location_subject ls ON (s.id = ls.subject_id) 
                JOIN cw_location l ON (ls.location_id = l.id)");
                echo '<pre>';
                while ($r = $results->fetch(PDO::FETCH_ASSOC)) {
                    print_r($r);
                }
                echo '</pre>';
                


                [ed. note: muzzstick last edited this post 1 year, 11 months ago.]
                  I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
                  Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com
                • you would just change this line to

                  $on = 'Data3.model_number=Data2.model_number';


                  doesn't this work?

                  could you post your db-schema?
                    -------------------------------

                    you can buy me a beer, if you like MIGX

                    http://webcmsolutions.de/migx.html

                    Thanks!
                  • Hi Bruno, thanks.

                    I can get the query itself to work but not with getCollection().

                    Here's is a simplified version of the schema.

                    <?xml version="1.0" encoding="UTF-8"?>
                    <model package="cwregister" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
                    
                        <object class="Location" table="location" extends="xPDOSimpleObject">
                            <field key="location_name_en" dbtype="varchar" precision="20" phptype="string" null="true" />
                            <composite alias="LocationSubject" class="LocationSubject" local="id" foreign="location_id" cardinality="many" owner="local" />
                        </object>
                    
                        <object class="Subject" table="subject" extends="xPDOSimpleObject">
                            <field key="subject_name_en" dbtype="varchar" precision="100" phptype="string" null="true" />
                            <composite alias="LocationSubject" class="LocationSubject" local="id" foreign="subject_id" cardinality="many" owner="local" />
                        </object>
                    
                        <!-- Many to Many. Assigns a subject to a location -->
                        <object class="LocationSubject" table="location_subject" extends="xPDOSimpleObject">
                            <field key="location_id" dbtype="int" precision="10" phptype="integer" null="false" />
                            <field key="subject_id" dbtype="int" precision="10" phptype="integer" null="false" />
                            <aggregate alias="Location" class="Location" local="location_id" foreign="id" cardinality="one" owner="foreign" />
                            <aggregate alias="Subject" class="Subject" local="subject_id" foreign="id" cardinality="one" owner="foreign" />
                        </object>
                        
                    </model>
                    
                      I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
                      Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com
                    • Quote from: ambaxter at Dec 13, 2015, 07:29 PM

                      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.
                      Has anyone done something like this.

                      All the time.

                      Here are some examples:

                      The schema relation is:
                      Invoice->Items (Many)
                      Item->Invoice (One)
                      	public function getInvoiceGraph($key = 0, $useReferralCode = false) {
                      		$out = false;
                      		/* Block the database hit for a fake company user */
                      		if (is_numeric ( $key ) && ($key > 0)) {
                      			
                      			/* Use the remote key or the primary key */
                      			$criteria = ($useReferralCode) ? array (
                      					'referralCode' => $key 
                      			) : array (
                      					'id' => $key 
                      			);
                      			
                      			/*
                      			 * Retrieve objectGraph of an Invoice, its items, and the sku details
                      			 */
                      			$out = $this->modx->getObjectGraph ( 'xxInvoice', '{"Items":{}}', $criteria );
                      		}
                      		return $out;
                      	}
                      


                      Here is one you can base off of the MODX schema.
                      As you will find:
                      The [one] modUserGroup {note actual class name and not alias}
                      has GroupsMembers {note alias used}
                      who are User(s)

                      	
                      	/**
                      	 * Retrieves all the users found in the modUserGroup RR
                      	 *
                      	 * @return array Collection of modUserGroupMember
                      	 */
                      	private function getRRUsers() {
                      		$criteria = array (
                      				'name' => 'RR',
                      				'User.active' => true 
                      		);
                      		
                      		/*
                      		 * Retrieve objectGraph of a modUserGroup and any active modUserGroupMember attached to the group
                      		 */
                      		return $this->modx->getObjectGraph ( 'modUserGroup', '{"UserGroupMembers":{"User":{}}}', $criteria, false );
                      	}
                      


                      getCollectionGraph works the same way.
                      Also, note this WILL NOT work if the tables are not hydrated. You have the MODX connection, so ignore that aspect, the concept is explained here: https://www.shawnwilkerson.com/profession/xpdo/establishing-database-connections.html
                      Hope this helps.
                        Get your copy of MODX Revolution Building the Web Your Way http://www.sanitypress.com/books/modx-revolution-building-the-web-your-way.html

                        Check out my MODX || xPDO resources here: http://www.shawnwilkerson.com