We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 24719
    • 194 Posts
    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?
      • 15987
      • 786 Posts
      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
        • 22303 MODX Staff
        • 10,725 Posts
        Quote from: redman at Jan 22, 2007, 04:27 PM

        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?
        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. However, it’s very easy to do; consider this code used to get a list of Categories along with their descriptions in multiple languages (from an osCommerce database model reverse-engineered with xPDO):
        <?php
        $categories= array ();
        $catTbl= $xpdo->getTableName('Categories');
        $catDescTbl= $xpdo->getTableName('CategoriesDescription');
        $catColumns= $xpdo->getSelectColumns('Categories', 'cat', 'cat_');
        $descColumns= $xpdo->getSelectColumns('CategoriesDescription', 'catdesc', 'catdesc_');
        if ($stmt= $xpdo->prepare("SELECT {$catColumns},{$descColumns} FROM {$catTbl} cat JOIN {$catDescTbl} catdesc ON cat.categories_id = catdesc.categories_id AND catdesc.language_id = :language_id AND cat.parent_id = :parent_id")) {
            $stmt->bindParam(':language_id', $language_id, PDO_PARAM_INT);
            $stmt->bindParam(':parent_id', $parent_id, PDO_PARAM_INT);
            if ($stmt->execute()) {
                foreach ($stmt->fetchAll(PDO_FETCH_ASSOC) as $row) {
                    $category= $xpdo->newObject('Categories');
                    $category->fromArray($row, 'cat_', true);
                    $categoryDesc= $xpdo->newObject('CategoriesDescription');
                    $categoryDesc->fromArray($row, 'catdesc_', true);
                    $category->addMany($categoryDesc);
                    $categories[]= $category;
                }
            }
        }

        Does that answer your question, or did you have something else in mind?
          • 24719
          • 194 Posts
          this is just what i was looking for, thanks.
            • 24719
            • 194 Posts
            Argh, I’ve still not got the hang of it. Here’s the code i’m trying to use - just based on the sample xpdo classes. i’m trying to create a list showing the phone numbers for people. I’ve put some records in the database, but for some reason the getMany(’Phone’) call isn’t returning an array of related Phone objects.

            Where am I going wrong?

            $people = array ();
            $personTbl = $xpdo->getTableName('Person');
            $phoneTbl= $xpdo->getTableName('Phone');
            $ppTbl = $xpdo->getTableName('PersonPhone');
            $personColumns= $xpdo->getSelectColumns('Person', 'p');
            $phoneColumns= $xpdo->getSelectColumns('Phone', 'ph');
            
            if ($stmt= $xpdo->prepare("SELECT $personColumns,$phoneColumns FROM $personTbl AS p, $phoneTbl AS ph, $ppTbl AS pp WHERE p.id=pp.person AND ph.id=pp.phone")) {
                if ($stmt->execute()) {
                    foreach ($stmt->fetchAll(PDO_FETCH_ASSOC) as $row) {
                        $person= $xpdo->newObject('Person');
                        $person->fromArray($row, '', true);
                        $phone= $xpdo->newObject('Phone');
                        $phone->fromArray($row, '', true);
                        $person->addMany($phone);
                        $people[]= $person;
                    }
                }
            }
            
            $table = new MakeTable($xpdo);
            $tableData = false;
            
            foreach($people as $person)
            {
            	$numbers = $person->getMany('Phone');
            
            	foreach ($numbers as $number)
            	{
            		$tableData[] = array('name' => $person->get('first_name') . ' ' . $person->get('last_name'),
                                        'dob' => $person->get('dob'), 'number' => $number->get('number'));
            	}
            }
            
            return $table->create($tableData);
            
              • 22815
              • 1,097 Posts
              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?
                No, I don&#39;t know what OpenGeek&#39;s saying half the time either.
                MODx Documentation: The Wiki | My Wiki contributions | Main MODx Documentation
                Forum: Where to post threads about add-ons | Forum Rules
                Like MODx? donate (and/or share your resources)
                Like me? See my Amazon wishlist
                MODx "Most Promising CMS" - so appropriate!
                • 15987
                • 786 Posts
                @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.
                  • 22303 MODX Staff
                  • 10,725 Posts
                  Quote from: redman at Jan 23, 2007, 07:23 AM

                  Where am I going wrong?
                  $numbers = $person->getMany('Phone');

                  There is no direct relationship between Phone and Person because it’s a many-to-many situation. In other words, you would
                  $personPhone= $person->getMany('PersonPhone');
                  and then
                  $personPhone->getOne('Phone');
                  ...
                    • 22815
                    • 1,097 Posts
                    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.
                      No, I don&#39;t know what OpenGeek&#39;s saying half the time either.
                      MODx Documentation: The Wiki | My Wiki contributions | Main MODx Documentation
                      Forum: Where to post threads about add-ons | Forum Rules
                      Like MODx? donate (and/or share your resources)
                      Like me? See my Amazon wishlist
                      MODx "Most Promising CMS" - so appropriate!
                      • 22303 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