Using XPDO in own Project: select joined rows

116 Posts

Wanze Reply #1, 15 years ago

Hi there!

I have to build a mini-cms (multilingual) and decided to use xpdo
as ORM, because it's awesome!! :-D

I have set up my xml schema with relations and created the class and maps from it.
Here's the schema:

	<object class="Resources" table="resources" extends="xPDOSimpleObject">
		<field key="template_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="language_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="l18n_parent" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
		<field key="menuindex" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="parent" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
		<field key="pagetitle" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="menutitle" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="alias" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="content" dbtype="text" phptype="string" null="false" />
		<field key="deleted" dbtype="tinyint" precision="1" phptype="integer" null="false" default="0" />
		<aggregate alias="Languages" class="Languages" local="language_id" foreign="id" cardinality="one" owner="foreign" />		
		<aggregate alias="Templates" class="Templates" local="template_id" foreign="id" cardinality="one" owner="foreign" />	
		<aggregate alias="Parent" class="Resources" local="parent" foreign="id" cardinality="one" owner="foreign" />
	    <aggregate alias="Children" class="Resources" local="id" foreign="parent" cardinality="many" owner="local" />
	</object>

	<object class="Languages" table="languages" extends="xPDOSimpleObject">
		<field key="title_short" dbtype="varchar" precision="2" phptype="string" null="false" />
		<field key="title" dbtype="varchar" precision="200" phptype="string" null="false" />
		<aggregate alias="Resources" class="Resources" local="id" foreign="language_id" cardinality="many" owner="local" />		
	</object>

<object class="Templates" table="templates" extends="xPDOSimpleObject">
		<field key="title" dbtype="varchar" precision="200" phptype="string" null="false" />
		<aggregate alias="Resources" class="Resources" local="id" foreign="template_id" cardinality="many" owner="local" />
		
	</object>

I can basicly query my model but I cannot get to work this one right:

$c = $this->newQuery('Resources');
$c->select('Resources.*, Templates.title AS template');
//$c->select('Resources.*, Templates.*, Languages.*');					
$c->leftJoin('Languages','Languages');
$c->leftJoin('Templates','Templates');
$c->where(array('Resources.alias' => $this->alias, 'Languages.title_short' => $this->language));
$res = $this->getObject('Resources',$c);

If I have only the fields from the "Resources" class in my $res Object and don't get the template field
of the joined Templates table.
I have searched in the forums and i know that i have to include every Primary Key of all the objects in the select call, it doesn't work either. The joins itself seem to work correctly (the where clause works fine).

Using the generated Query with ->toSQL(); gets the template-field in phpmyadmin. But it seems that XPDO is firing an additional query afterwards. Why?

Getting the title from the related Template object works too, but i really would like to get it in one query, also without using a Graph:

$template = $res->getOne('Templates');
$name = $template->get('title'); //works

Can somone help me?
Thank you!
[ed. note: Wanze last edited this post 15 years ago.]

MODX Staff
10,725 Posts

opengeek Reply #2, 15 years ago

The graphs do exactly what you want. Is there a reason you don't want to use the graph? The extra query is due to lazy loading. If you do not include the field in the select columns of the query and then you try to access that field of the object, it will fire another query to retrieve that value.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

116 Posts

Wanze Reply #3, 15 years ago

Quote from: opengeek at Oct 11, 2011, 01:23 PM

The graphs do exactly what you want. Is there a reason you don't want to use the graph? The extra query is due to lazy loading. If you do not include the field in the select columns of the query and then you try to access that field of the object, it will fire another query to retrieve that value.

I didn't want to use a graph, because i don't have to select all the columns... also if i understood right it should be possible to obtain joined columns like this if there's no relationship defined in the xml-schema? (If I add the join-relationship as a third parameter to leftJoin())

Will have a look into the graphs... but could you please tell me how I have to
select the columns?

$c = $this->newQuery('Resources');
$c->select('Resources.*, Templates.title, Templates.title AS template');
$c->leftJoin('Languages','Languages');
$c->leftJoin('Templates','Templates');
$c->where(array('Resources.alias' => $this->alias, 'Languages.title_short' => $this->language));
$res = $this->getObject('Resources',$c);

I have included the field in the select method but it's still not available with $res->get('template')

116 Posts

Wanze Reply #4, 15 years ago

Ok I used a Graph now =)

$this->resource = $this->getObjectGraph('Resources', 
array('Template' => array(), 'Language' => array()),
array('Language.title_short' => $this->request['language'], 
      'Resources.alias' => $this->request['alias'])	
);

Still I could not figure out how to select and get a column from a joined table with leftJoin().
This should also be possible without defining relationships in the schema?