Get Related field in custom table with xPDO

833 Posts

treigh Reply #1, 13 years, 8 months ago

Hi all,

As intimidating as it looks to us dummies, I'm determined to play with XPDO and hopefully learn a thing or two in the process.

I have 2 custom databse tables (bible_livres, bible_versets) that I imported into my MODX db and thanks to this great walkthrough in the official docs: Reverse Engineer xPDO Classes from Existing Database Table , I was able to generate the appropriate XML schema and PHP files for my tables. I also defined my foreign key relationship in PHPMyAdmin with bible_livres as the parent table using the field livre_id.

My tables structure:

bible_livres

---------------------------------------
| livre_id | livre | chap | testament |
---------------------------------------

bible_versets

-----------------------------------------------------------------------------------------
| id | livre_id | ch | vst | LSG | darby | ostervald | martin | is_promesse | testament |
-----------------------------------------------------------------------------------------

My plan is to generate a random bible verse from the tables and print it in the frontend. For a start I managed to do some copy-pasting to retrieve the fields that I need. My first problem is that I don't know how to add the value of a related field in the output.

The livre field of the bible_livres table contains the label for the livre_id field. How could I include the field in my output given that I'm querying the bible_versets table

My snippet-to-be

<?php
$path = MODX_CORE_PATH . 'components/VirtualBible/';
$result = $modx->addPackage('VirtualBible',$path . 'model/','bible_');
if (! $result) {
  return 'failed to add package';
} else {
  $output = '<h1>Added Package</h1><hr>';
}


//Get one bible verse
$query = $modx->newQuery('Versets');
$query->limit(1);
$versets = $modx->getCollection('Versets',$query);


foreach($versets as $verset) {
    //Idealy i would like to replace the livre_id field in next line with the livre field from the bible_livres table since it's more meaningful than the livre_id
    $output .= '<p>Livre: ' . $verset->get('livre_id');
	$output .= '<br />Chap: ' . $verset->get('ch');
    $output .= ': ' . $verset->get('vst');
    $output .= '<br />' . $verset->get('LSG') . '<br /></p>';
}

return $output;

My XML Schema file

<?xml version="1.0" encoding="UTF-8"?>
<model package="VirtualBible" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Livres" table="livres" extends="xPDOObject">
		<field key="livre_id" dbtype="int" precision="2" attributes="unsigned" phptype="integer" null="false" index="pk" />
		<field key="livre" dbtype="char" precision="60" phptype="string" null="false" default="" />
		<field key="chap" dbtype="int" precision="3" phptype="integer" null="false" default="0" />
		<field key="testament" dbtype="char" precision="1" phptype="string" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="livre_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="Versets" table="versets" extends="xPDOSimpleObject">
		<field key="livre_id" dbtype="int" precision="2" attributes="unsigned" phptype="integer" null="false" index="index" />
		<field key="ch" dbtype="mediumint" precision="3" phptype="integer" null="false" />
		<field key="vst" dbtype="mediumint" precision="3" phptype="integer" null="false" />
		<field key="LSG" dbtype="mediumtext" phptype="string" null="true" />
		<field key="darby" dbtype="mediumtext" phptype="string" null="true" />
		<field key="ostervald" dbtype="mediumtext" phptype="string" null="true" />
		<field key="martin" dbtype="mediumtext" phptype="string" null="true" />
		<field key="is_promesse" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="testament" dbtype="char" precision="1" phptype="string" null="false" />

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

thanks for your help. [ed. note: treigh last edited this post 13 years, 8 months ago.]

A MODx Fanatic

- 24,544 Posts
BobRay Reply #2, 13 years, 8 months ago
It looks like you have the livre_id, so in your foreach loop you can just do this:
```
$lid = $verset->get('livre_id');
$livre = $modx->getObject('Livres', $lid);
if ($livre) {
   $chap = $livre->get('chap');
   $testament = $livre->get('testament');
   // etc.
}
```
Assuming that there is only one possible livre, if you want to get a little deeper into xPDO, you can put an aggregate alias in your verset object schema that points to the related livre object. That way you can replace the first two lines above with this:
```
$verset->getOne('Livre');
```
Something like this:
```
<aggregate alias="Verset" class="Versets" local="livre_id" foreign="livre_id" cardinality="one" owner="foreign" />
    </object>
```
You could also do something similar in the livres class (with a cardinality of many) to get its related versets with getMany('Versets'); (assuming I'm right that a livre has multiple versets).

Remember to regenerate the class and map files whenever the schema changes.

BTW, there is a utility snippet here that does the reverse engineering for you if the tables already exist. http://bobsguides.com/custom-db-tables.html. With it, you can be fairly sure that the schema is correct. You would still need to add any aggregate or composite aliases to the schema yourself, then rerun it to generate the class and map files. [ed. note: BobRay last edited this post 13 years, 8 months ago.]
Did I help you? Buy me a beer
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
- 833 Posts
treigh Reply #3, 13 years, 8 months ago

Thanks Bob for the follow-up. I just finished adding the aggregate alias in my schema and I'll test the results. Thanks for the insight.
A MODx Fanatic