How to OrderBy in a Many to Many Relationship

98 Posts

jimsheppard Reply #1, 13 years, 8 months ago

Many to Many Relationship with Order By

Slowly I'm beginning to grasp the complexity of xPDO (I think).

We have a laboratory database in with a Many to Many relationship between the Labs and the products tested (Scope) as well as the purpose for testing (Category) and the type of test used (Special)

I have a snippet that will walk the database and collect the data

$labpost = $modx->getObject('Labs', array('lab_ID' => 1 ) );
    $output .= $labpost ->get('lab_name')."<br/>";
$lab_scope = $labpost->getMany('JoinScope');
foreach ( $lab_scope as $ls )
{
    $cat = $ls->getOne('Category');
    $output .= $cat->get('CategoryName')."<br/>";

    $spc = $ls->getOne('Special');
    $output .= $spc->get('SpecialName')."<br/>";

    $sco = $ls->getOne('LabScope');
    $output .= $sco->get('Scope')."<br/>";
}

presenting the data in an unordered format:

MyLab
       Category A
           Specialisation A
                Scope
       Category A
           Specialisation B
                Scope
       Category B
           Specialisation A
                Scope
       Category B
           Specialisation B
                Scope

We would like to have the data ordered by Category and Specialisation similar to this:

MyLab
       Category A
           Specialisation A
                Scope
       Category A
           Specialisation B
                Scope
       Category B
           Specialisation A
                Scope
       Category B
           Specialisation B
                Scope

Any assistance or suggestions on how to loop through the data to do this would be greatly appreciated.

This question has been answered by Bruno17. See the first response.

Give a man the answer, and he’ll only have a temporary solution. Teach him the principles that led you to that answer, and he will be able to create his own solutions in the future.

5,888 Posts

Bruno17 Reply #2, 13 years, 8 months ago

do you want to show us your xml-schema?

some left-joins and sortbys should do it.

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

98 Posts

jimsheppard Reply #3, 13 years, 8 months ago

Quote from: Bruno17 at Feb 01, 2013, 01:37 AM

do you want to show us your xml-schema?

some left-joins and sortbys should do it.

Sorry about that here is the schema

<?xml version="1.0" encoding="UTF-8"?>
<model package="csaac" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Analysts" table="analysts" extends="xPDOObject">
		<field key="sa_ID" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="Surname" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="MemberName" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="Status" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="Seal" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="FK_LabID" dbtype="int" precision="11" phptype="integer" null="true" index="fk" />
		<field key="Email" dbtype="varchar" precision="255" phptype="string" null="true" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="sa_ID" length="" collation="A" null="false" />
		</index>  
		 <composite alias="Labs" class="Labs" local="FK_LabID" foreign="lab_ID" cardinality="many" owner="local" /> 
	</object>
	
	<object class="Category" table="category" extends="xPDOObject">
		<field key="CatID" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="CategoryName" dbtype="text" phptype="string" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="CatID" length="" collation="A" null="false" />
		</index>
		<composite alias="JoinScope" class="JoinScope" local="lab_jsID" foreign="FK_CatID" cardinality="many" owner="local" />
	</object>
	
	<object class="JoinScope" table="join_scope" extends="xPDOObject">
		<field key="jsID" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="FK_LabID" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="FK_CatID" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="FK_SpID" dbtype="int" precision="11" phptype="integer" null="true" />
		<field key="FK_ScopeID" dbtype="int" precision="11" phptype="integer" null="true" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="jsID" length="" collation="A" null="false" />
		</index>
		<aggregate alias="Labs" class="Labs" local="FK_LabID" foreign="lab_ID" cardinality="one" owner="foreign" />
		<aggregate alias="LabScope" class="LabScope" local="FK_ScopeID" foreign="scope_ID" cardinality="one" owner="foreign" />
		<aggregate alias="Category" class="Category" local="FK_CatID" foreign="CatID" cardinality="one" owner="foreign" />
		<aggregate alias="Special" class="Special" local="FK_SpID" foreign="Special_ID" cardinality="one" owner="foreign" />
	</object>
	
	<object class="LabScope" table="lab_scope" extends="xPDOObject">
		<field key="scope_ID" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="Scope" dbtype="varchar" precision="255" phptype="string" null="true" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="scope_ID" length="" collation="A" null="false" />
		</index>
		<composite alias="JoinScope" class="JoinScope" local="scope_ID" foreign="FK_ScopeID" cardinality="many" owner="local" />
	</object>
	
	<object class="Labs" table="labs" extends="xPDOObject">
		<field key="lab_ID" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="lab_name" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="lab_address" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="lab_city" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="lab_prov" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="lab_pc" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="lab_ctry" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="lab_phone" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="lab_fax" dbtype="varchar" precision="255" phptype="string" null="true" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="lab_ID" length="" collation="A" null="false" />
		</index>
		<composite alias="JoinScope" class="JoinScope" local="lab_ID" foreign="FK_LabID" cardinality="many" owner="local" />
		<aggregate alias="Analysts" class="Analysts" local="lab_ID" foreign="FK_LabID" cardinality="one" owner="foreign" />		
	</object>
	
	<object class="Special" table="special" extends="xPDOObject">
		<field key="Special_ID" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="SpecialName" dbtype="text" phptype="string" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="Special_ID" length="" collation="A" null="false" />
		</index>
		<composite alias="JoinScope" class="JoinScope" local="jsID" foreign="FK_SpID" cardinality="many" owner="local" />
	</object>
</model>

Give a man the answer, and he’ll only have a temporary solution. Teach him the principles that led you to that answer, and he will be able to create his own solutions in the future.

discuss.answer

5,888 Posts

Bruno17 Reply #4, 13 years, 8 months ago

something like that could work:

<?php

$labpost = $modx->getObject('Labs', array('lab_ID' => 1));
$output .= $labpost->get('lab_name') . "<br/>";


$c = $modx->newQuery('JoinScope');
$c->leftJoin('Category', 'Category');
$c->leftJoin('Special', 'Special');
$c->leftJoin('LabScope', 'LabScope');
$c->select($modx->getSelectColumns('JoinScope', 'JoinScope'));
$c->select($modx->getSelectColumns('Category', 'Category', 'Category_'));
$c->select($modx->getSelectColumns('Special', 'Special', 'Special_'));
$c->select($modx->getSelectColumns('LabScope', 'LabScope', 'LabScope_'));
$c->sortby('Category.CategoryName');
$c->sortby('Special.SpecialName');

$lab_scope = $labpost->getMany('JoinScope', $c);

foreach ($lab_scope as $ls) {
    $fields = $ls->toArray();
    $output .= $modx->getChunk($tpl, $fields);
}

return $output;

[[!yoursnippet? &tpl=`yourChunk`]]

yourChunk:

[[+Category_CategoryName]]<br />
[[+Special_SpecialName]]<br />
[[+LabScope_Scope]]<br />

[ed. note: Bruno17 last edited this post 13 years, 8 months ago.]

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

98 Posts

jimsheppard Reply #5, 13 years, 8 months ago

Thanks Bruno17

Almost, the last line in the foreach loop should read...

$output .= $modx->getChunk($tpl, $fields);

Give a man the answer, and he’ll only have a temporary solution. Teach him the principles that led you to that answer, and he will be able to create his own solutions in the future.

5,888 Posts

Bruno17 Reply #6, 13 years, 8 months ago

sorry, changed that in my first post.

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

Answered How to OrderBy in a Many to Many Relationship