On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • Hi,

    It’s me again. I’ve started working with xPDO and I like it very much.
    I have a problem with retrieving records after leftjoin - actually with outputting them.

    I have to tables and one reference table.
    One is for firms other for regions. One firm could be in many regions.
    So in my reference table i put: nazwa (reference to firmname in firms table) and region (reference to regions table)
    My XML Schema looks like that:
    <?xml version="1.0" encoding="UTF-8"?>
    <model package="klimatyzacja" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM">
    	<object class="klimaFirma" table="klima_firmy" extends="xPDOSimpleObject">
    	  <composite alias="FirmaRegion" class="klimaFirmaRegion" local="id" foreign="firma" cardinality="many" owner="local" />
    		<field key="nazwa" dbtype="varchar" precision="250" phptype="string" null="false" default="" index="index" />
    		<field key="dzialalnosc" dbtype="text" phptype="string" null="false" default="" />
    		<field key="dzialalnosc_en" dbtype="text" phptype="string" null="false" default="" />
    		<field key="www" dbtype="varchar" precision="250" phptype="string" null="false" default="" />
    		<field key="email" dbtype="varchar" precision="250" phptype="string" null="false" default="" />
    		<field key="tel" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
    		<field key="fax" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
    		<field key="adres" dbtype="varchar" precision="100" phptype="string" null="false" default="" />
    		<field key="miasto" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
    		<field key="kod" dbtype="varchar" precision="10" phptype="string" null="false" default="" />
    		<field key="wpis" dbtype="enum" precision="'2','1','0'" phptype="string" null="false" default="0" />
    		<field key="logo" dbtype="varchar" precision="250" phptype="string" null="false" default="" />
    		<field key="logo_a" dbtype="enum" precision="'0','1'" phptype="string" null="false" default="0" />
    		<field key="rozsz" dbtype="varchar" precision="100" phptype="string" null="false" default="" />
    		<field key="o_kont" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
    		<field key="t_kont" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
    		<field key="em_kont" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
    		<field key="nazwa2" dbtype="varchar" precision="250" phptype="string" null="false" default="" index="index" />
    		<field key="adres2" dbtype="varchar" precision="100" phptype="string" null="false" default="" />
    		<field key="kod2" dbtype="varchar" precision="10" phptype="string" null="false" default="" />
    		<field key="miasto2" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
    		<field key="nip" dbtype="varchar" precision="30" phptype="string" null="false" default="" />
    		<field key="kolejnosc" dbtype="int" precision="11" phptype="integer" attributes="unsigned" null="false" default="0" />
    		<field key="aktywacja" dbtype="enum" precision="'0','1'" phptype="string" null="false" default="0" />
    		<field key="data" dbtype="date" precision="11" phptype="integer" attributes="unsigned" null="false" default="" />
    		<field key="koniec" dbtype="date" precision="11" phptype="string" attributes="unsigned" null="false" default="" />
    		<field key="polecamy" dbtype="enum" precision="'0','1'" phptype="string" null="false" default="0"/>
    	</object>
    	<object class="klimaRegion" table="klima_regiony" extends="xPDOSimpleObject">
    	  <composite alias="FirmaRegion" class="klimaFirmaRegion" local="id" foreign="region" cardinality="many" owner="local" />
    		<field key="pid" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
    		<field key="region" dbtype="varchar" precision="100" phptype="string" null="false" default="" index="index" />	  
    	</object>
    	<object class="klimaFirmaRegion" table="klima_firma_region" extends="xPDOSimpleObject">
    	  <aggregate alias="Firma" class="klimaFirma" local="firma" foreign="id" cardinality="one" owner="foreign" />
    	  <aggregate alias="Region" class="klimaRegion" local="region" foreign="id" cardinality="one" owner="foreign" />
    		<fields key="firma" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />
    		<fields key="region" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />		
    	</object>
    </model>


    I’d like to print firm name and all regions that are connected with it. Normally after JOIN I would have $row array with fields from one and the other table.
    For now I don’t want to set up any criteria (besides firm name) I just want to get all firms with %name% and all regions connected to it.
    How one should do it in xPDO?

    My snippet looks like that:

    $search = $_POST['search'];
    
    $query = $modx->newQuery('klimaFirma');
    $query->leftJoin('klimaFirmaRegion','FirmaRegion');
    
    $query->where(array(
        'klimaFirma.nazwa:LIKE' => '%'.$search.'%'
    ));
    
    $query->sortby('klimaFirma.nazwa','ASC');
    //$query->limit(4);
    
    $firmy = $modx->getCollection('klimaFirma', $query);
    echo 'Wszystkie firmy: '.count($firmy);
    foreach ($firmy as $firma) {
    	print('<br/>'.$firma->nazwa.' '.$firma->region);
    }


    --
    Regards
    MM
    • Well, for one, in your klimaFirmaRegion xml schema definition, you have ’fields’ instead of ’field’.
        shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
      • Your query can look like this:

        $query = $modx->newQuery('klimaFirma');
        $query->select('klimaFirma.*, Region.region AS region');
        $query->leftJoin('klimaFirmaRegion','FirmaRegion');
        $query->leftJoin('klimaRegion','Region','FirmaRegion.region = Region.id');
        
        $firmy = $modx->getCollection('klimaFirma', $query);
        foreach ($firmy as $firma) {
        	echo '<br />'.$firma->get('nazwa').' '.$firma->get('region');
        }
        
          shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

        • Though I still don’t understand this line:
          $query->leftJoin(’klimaRegion’,’Region’,’FirmaRegion.region = Region.id’);

          You’re just specifying the criteria manually for the JOIN. You’re saying, only JOIN if FirmaRegion’s region field is equal to the JOINed table’s ID field.


          In return: Id like to report a mistake in xPDO documentation:
          http://svn.modxcms.com/docs/display/XPDO10/xPDOQuery
          In example -> $c->sortby - should be $query->sortby (and following lines)
          Am I right?
          You are; it’s been corrected. Thanks!
          • Now I have such ’complex’ (?) query as:

            SELECT GROUP_CONCAT( modx_klima_regiony.region
            ORDER BY modx_klima_regiony.region ASC 
            SEPARATOR  ';;;' ) regiony, modx_klima_firmy. * 
            FROM modx_klima_firmy
            LEFT JOIN modx_klima_firma_region ON ( modx_klima_firmy.id = modx_klima_firma_region.firma ) 
            LEFT JOIN modx_klima_regiony ON ( modx_klima_regiony.id = modx_klima_firma_region.region ) 
            WHERE modx_klima_firmy.nazwa LIKE '%ma%'
            GROUP BY modx_klima_firmy.id
            ORDER BY modx_klima_firmy.nazwa ASC 


            I’m not sure how to translate it into xPDO

            --
            Regards
            MM
            • Quote from: maciej.m at Oct 08, 2009, 11:55 AM

              Now I have such ’complex’ (?) query as:

              SELECT GROUP_CONCAT( modx_klima_regiony.region
              ORDER BY modx_klima_regiony.region ASC 
              SEPARATOR  ';;;' ) regiony, modx_klima_firmy. * 
              FROM modx_klima_firmy
              LEFT JOIN modx_klima_firma_region ON ( modx_klima_firmy.id = modx_klima_firma_region.firma ) 
              LEFT JOIN modx_klima_regiony ON ( modx_klima_regiony.id = modx_klima_firma_region.region ) 
              WHERE modx_klima_firmy.nazwa LIKE '%ma%'
              GROUP BY modx_klima_firmy.id
              ORDER BY modx_klima_firmy.nazwa ASC 


              $c = $modx->newQuery('klimaFirmy');
              $c->select('
                   klimaFirmy.*,
                   (SELECT GROUP_CONCAT(region) FROM '.$modx->getTableName('klimaRegion').' ORDER BY region ASC SEPARATOR  ";;;" ) AS regiony
              ');
              $c->leftJoin('klimaFirmaRegion','FirmaRegion');
              $c->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');
              $c->where(array(
                  'klimaFirma.nazwa:LIKE' => '%ma%',
              ));
              $c->sortby('klimaFirmy.id','ASC');
              $c->groupby('klimaFirmy.id');
              $collection = $modx->getCollection('klimaFirmy',$c);
              


              or something like that.

              I strongly recommend reading thoroughly the xPDO docs and the API docs.
                shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
              • There was a problem with SEPARATOR ’;;;’. After I removed it returned but with all records from modx_klima_regiony table in new ’regiony’ field.

                DEBUG:
                SELECT klimaFirma.*, (SELECT GROUP_CONCAT(region) FROM `modx_klima_regiony` ORDER BY region ASC SEPARATOR ";;;" ) AS regiony FROM `modx_klima_firmy` AS `klimaFirma` LEFT JOIN `modx_klima_firma_region` `FirmaRegion` ON `klimaFirma`.`id` = `FirmaRegion`.`firma` LEFT JOIN `modx_klima_regiony` `Region` ON Region.id = FirmaRegion.region WHERE `klimaFirma`.`nazwa` LIKE '%ma%' GROUP BY klimaFirma.id ORDER BY klimaFirma.id ASC


                I’ve also noticed that two of the following are not equivalent (at least they don’t return similar results)

                SELECT * FROM `modx_klima_firmy` 
                LEFT JOIN `modx_klima_firma_region` 
                ON (modx_klima_firmy.id = modx_klima_firma_region.firma) 
                LEFT JOIN `modx_klima_regiony` 
                ON (modx_klima_regiony.id = modx_klima_firma_region.region);


                AND

                $query = $modx->newQuery('klimaFirma');
                $query->select('klimaFirma.*, Region.region AS region, Region.id AS reg_id');
                $query->leftJoin('klimaFirmaRegion','FirmaRegion', 'klimaFirma.id = FirmaRegion.firma');
                $query->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');


                The first returns duplicated data (for each Region Firma has)
                The second doesn’t.
                • Ok, finally I got query which should work, but it doesn’t (code below).
                  I use toSQL() function to debug. Then I cut-n-paste SQL query to PhpMyAdmin.
                  This returns results in PhpMyAdmin but not on a webpage.

                  $c = $modx->newQuery('klimaFirma');
                  $c->select('
                  	 GROUP_CONCAT 
                  	 ( Region.region ORDER BY Region.region ASC )
                  	 regiony, klimaFirma.*
                  ');
                  $c->leftJoin('klimaFirmaRegion','FirmaRegion');
                  $c->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');
                  $c->where(array(
                  	'klimaFirma.nazwa:LIKE' => '%ma%',
                  ));
                  $c->sortby('klimaFirma.id','ASC');
                  $c->groupby('klimaFirma.id');
                  
                  
                  $c->prepare();
                  $sql = $c->toSQL();
                  
                  echo $sql; // Printing SQL statement which I then try in PhpMyAdmin
                  
                  $firmy = $modx->getCollection('klimaFirma', $c);
                  
                  foreach ($firmy as $firma) {
                  	print('<br/>'.$firma->get('nazwa').' '.$firma->get('regiony'));
                  }
                  
                  echo 'Wszystkie firmy: '.count($firmy)
                  ;
                  • You can’t return data like that as a collection of objects with getCollection. Just use the PDO statement you are creating, execute it, and fetch the results as you would with any PDO statement. If you need to create objects out of the fetched rows, just use newObject() and fromArray() to manually hydrate the objects.
                    • Quote from: OpenGeek at Oct 08, 2009, 04:57 PM

                      You can’t return data like that as a collection of objects with getCollection.

                      I don’t actually understand what does it mean ’data like that’. What is the difference between code Shaun’s given and which returned results:
                      $c = $modx->newQuery('klimaFirmy');
                      $c->select('
                           klimaFirmy.*,
                           (SELECT GROUP_CONCAT(region) FROM '.$modx->getTableName('klimaRegion').' ORDER BY region ASC ) AS regiony
                      ');
                      $c->leftJoin('klimaFirmaRegion','FirmaRegion');
                      $c->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');
                      $c->where(array(
                          'klimaFirma.nazwa:LIKE' => '%ma%',
                      ));
                      $c->sortby('klimaFirmy.id','ASC');
                      $c->groupby('klimaFirmy.id');
                      $collection = $modx->getCollection('klimaFirmy',$c);


                      and my code:

                      $c = $modx->newQuery('klimaFirma');
                      $c->select('
                      	 GROUP_CONCAT 
                      	 ( Region.region ORDER BY Region.region ASC )
                      	 regiony, klimaFirma.*
                      ');
                      $c->leftJoin('klimaFirmaRegion','FirmaRegion');
                      $c->leftJoin('klimaRegion','Region','Region.id = FirmaRegion.region');
                      $c->where(array(
                      	'klimaFirma.nazwa:LIKE' => '%ma%',
                      ));
                      $c->sortby('klimaFirma.id','ASC');
                      $c->groupby('klimaFirma.id');
                      
                      
                      $firmy = $modx->getCollection('klimaFirma', $c);
                      
                      foreach ($firmy as $firma) {
                      	print('<br/>'.$firma->get('nazwa').' '.$firma->get('regiony'));
                      }
                      
                      echo 'Wszystkie firmy: '.count($firmy);