Select certain fields using pdoPage & migxLoopCollection with a join

22 Posts

kctechsoln Reply #1, 11 years, 2 months ago

Hi

I'm joining two tables using migxLoopCollection, and then paginating the results with pdoPage, but for speed & efficiency would like to only select certain fields form each table.

I know you can use &selectfields=`field1,field2.....`. I already use this in a simple lookup and it works fine but this breaks the output when there is a join clause. :

[[!pdoPage@pdoBootstrapPagination?
                &element=`migxLoopCollection`
                &packageName=`riversideresults`
                &classname=`RiversideResult`
                &joins=`[{"classname":"RiversideRace","alias":"rrRace","on":"rrRace.id=result_race"}]`
                &tpl=`resultDbRow.tpl`
                &selectfields=`result_runner,result_user_id,result_hour,event_heading,event_date`
                &where=`[[!searchFields? &encoding=`JSON`]]`
                &sortConfig=`[{"sortby":"id","sortdir":"DESC"}]`
                &limit=`25`
                &toPlaceholder=`results`
            ]]
            [[!+results:default=`<tr><td colspan="6">No results found</td></tr>`]]

I've tried appending the classname to the field, but this doesn't work either:

&selectfields=`RiversideResult.result_runner,RiversideResult.result_user_id,RiversideResult.result_hour,RiversideRace.event_heading,RiversideRace.event_date`

After looking at the pdoTools documentation I also tried the below, but to no avail:

&selectfields=`[{"RiversideResult":"id,result_runner,result_user_id,result_hour,result_min,result_sec,result_hundth,result_position,result_cat,result_position_overall,result_position_cat"},{"RiversideRace":"id,event_heading,event_date"}]`

Looking at the code for migxLoopCollection it doesn't work because its looking for the columns in every table when they don't exist? core/components/migx/model/migx/migx.class.php (line 175):

$c->select($xpdo->getSelectColumns($classname, $classname, '', $selectfields));

Any help is much appreciated?

Thanks
Kyle

MODX 2.3.5-pl, MIGX 2.9.4-pl, pdoTools 2.0.5-pl

5,888 Posts

Bruno17 Reply #2, 11 years, 2 months ago

could you show your xpdo-schema for that tables, please?

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

22 Posts

kctechsoln Reply #3, 11 years, 2 months ago

Hi Bruno

Sure, for the riversiderunners package:

<?xml version="1.0" encoding="UTF-8"?>
<model package="riversideresults" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="RiversideRace" table="riverside_races" extends="xPDOSimpleObject">
		<field key="id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
		<field key="event_date" dbtype="date" phptype="date" null="true" />
		<field key="event_location_address" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="event_location_town" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="event_location_county" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="event_time" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="event_distance" dbtype="varchar" precision="10" phptype="string" null="true" />
		<field key="event_distance_official" dbtype="varchar" precision="10" phptype="string" null="true" />
		<field key="event_gradient" dbtype="varchar" precision="10" phptype="string" null="true" />
		<field key="event_surface" dbtype="varchar" precision="10" phptype="string" null="true" />
		<field key="event_notes" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="event_heading" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="event_distance_km_mile" dbtype="varchar" precision="10" phptype="string" null="true" />
	</object>
	<object class="RiversideResult" table="riverside_results" extends="xPDOSimpleObject">
		<field key="result_user_id" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
		<field key="result_runner" dbtype="text" phptype="string" null="true" />
		<field key="result_date_added" dbtype="datetime" phptype="datetime" null="true" />
		<field key="result_hour" dbtype="int" precision="2" phptype="integer" null="true" />
		<field key="result_min" dbtype="int" precision="2" phptype="integer" null="true" />
		<field key="result_sec" dbtype="int" precision="2" phptype="integer" null="true" />
		<field key="result_hundth" dbtype="int" precision="2" phptype="integer" null="true" />
		<field key="result_position_overall" dbtype="varchar" precision="100" phptype="string" null="true" />
		<field key="result_cat" dbtype="varchar" precision="100" phptype="string" null="true" />
		<field key="result_position_cat" dbtype="varchar" precision="100" phptype="string" null="true" />
		<field key="result_pb" dbtype="varchar" precision="10" phptype="string" null="true" />
		<field key="result_notes" dbtype="text" phptype="string" null="true" />
		<field key="result_race" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="published" dbtype="int" precision="1" phptype="integer" null="false" default="1" />
		<field key="deleted" dbtype="int" precision="1" phptype="integer" null="false" default="0" />

		<aggregate alias="rrRace" class="RiversideRace" local="result_race" foreign="id" cardinality="one" owner="foreign" />

		<index alias="id" name="id" primary="true" unique="false" type="BTREE" >
			<column key="id" length="" collation="A" null="false" />
		</index>
	</object>
</model>

5,888 Posts

Bruno17 Reply #4, 11 years, 2 months ago

you define the selectfields of joins within the joins-property and since you have allready defined a aggregate in your schema, you don't need to add 'classname' and 'on' in the join again.

[[!migxLoopCollection?
    &packageName=`riversideresults`
    &classname=`RiversideResult`
    &joins=`[{"alias":"rrRace","selectfields":"event_heading,event_date"}]`
    &tpl=`resultDbRow.tpl`
    &selectfields=`result_runner,result_user_id,result_hour`
    &where=`[[!searchFields? &encoding=`JSON`]]`
    &sortConfig=`[{"sortby":"RiversideResult.id","sortdir":"DESC"}]`
    &limit=`25`
    &toPlaceholder=`results`
 ]]

you will also need to add the alias to the sortConfig, because both tables have the field 'id'

"sortby":"RiversideResult.id"

you can view the generated sql with &debug=`1`

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

5,888 Posts

Bruno17 Reply #5, 11 years, 2 months ago

you can also remove

        <field key="id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

and

        <index alias="id" name="id" primary="true" unique="false" type="BTREE" >
            <column key="id" length="" collation="A" null="false" />
        </index>

because this is handled automatically with xPDOSimpleObject

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

22 Posts

kctechsoln Reply #6, 11 years, 2 months ago

Thanks Bruno

Moving &selectfields=`` into the join clause makes perfect sense now - and works!

However I still need to define the "classname" and "on" for the join, as without these the join doesn't happen - despite the aggregate being defined in the schema - is there an error in the schema that I'm missing?

5,888 Posts

Bruno17 Reply #7, 11 years, 2 months ago

did you parse the schema again, after adding the aggregate?

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

22 Posts

kctechsoln Reply #8, 11 years, 2 months ago

I did, and checked the riversideresult.map.inc.php:

<?php
$xpdo_meta_map['RiversideResult']= array (
  'package' => 'riversideresults',
...
  'aggregates' => 
  array (
    'rrRace' => 
    array (
      'class' => 'RiversideRace',
      'local' => 'result_race',
      'foreign' => 'id',
      'cardinality' => 'one',
      'owner' => 'foreign',
    ),

5,888 Posts

Bruno17 Reply #9, 11 years, 2 months ago

when I parse your schema (without that unnecessary lines, I mentioned above)
and call it like above (without the where), I get:

SELECT `RiversideResult`.`result_runner`, `RiversideResult`.`result_user_id`, `RiversideResult`.`result_hour`, `rrRace`.`event_heading` AS `rrRace_event_heading`, `rrRace`.`event_date` AS `rrRace_event_date` FROM `modx_riverside_results` AS `RiversideResult` LEFT JOIN `modx_riverside_races` `rrRace` ON `RiversideResult`.`result_race` = `rrRace`.`id` ORDER BY RiversideResult.id DESC LIMIT 25

this looks good to me.

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!