Table Join Question

21 Posts

jgr Reply #1, 15 years, 9 months ago

Hello - this is my first question on these forums, so I hope I’m in the right section. I’m teaching myself about modx revolution and PHP using the docs and forums but I’m stuck on what is probably something very simple problem.

I am trying to build a CRUD application for managing database content. What I have so far can be seen at http://www.rdoigbuilders.co.uk. I need to be able to see the category name in the items table instead of the category_id. I think I need a table join but I’m not sure how to do it. I hope someone can point me in the right direction

Here is my ShowItems snippet.

<?php
$path = MODX_CORE_PATH . 'components/items/';

$modx->addPackage('items',$path . 'model/','brox_');

$tpl = $modx->getOption('tpl',$scriptProperties,'itemrows');

$items = $modx->getCollection('Item'); 
	
foreach($items as $item) {

   $itemArray = $item->toArray();
   $output .= $modx->getChunk($tpl,$itemArray);
}

return $output;

21 Posts

jgr Reply #2, 15 years, 9 months ago

Hi Again - I’ll try to provide some more info. I want to do a table join between my category and item table using xpdo. The reason is so I can use the placeholder for the category from my category table in my chunk for displaying the item table rows.

I have tried various combinations (just guessing really) but either end up with no data in the table or I get data in the table but errors appearing in the error log in modx that I cant make sense of.

I think I’m trying to do something like this but my syntax is probably not right. Can anyone help?

<?php
$path = MODX_CORE_PATH . 'components/items/';

$modx->addPackage('items',$path . 'model/','brox_');

$tpl = $modx->getOption('tpl',$scriptProperties,'itemrows');

$c = $modx->newQuery('Item');
$c->select('item.*,category.*');
$c->innerJoin('Category');

$items = $modx->getCollection('Item',$c); 
	
foreach($items as $item) {

   $itemArray = $item->toArray();
   $output .= $modx->getChunk($tpl,$itemArray);
}

return $output;

☆ A M B ☆
872 Posts

lossendae Reply #3, 15 years, 9 months ago

Before your getCollection() line try this:

<?php
$c->prepare();
return $c->toSQL();

It will allow you to see the query that will be executed and test its return result.

21 Posts

jgr Reply #4, 15 years, 9 months ago

That is very helpful. Thanks for taking the time to reply, it’s much appreciated

21 Posts

jgr Reply #5, 15 years, 9 months ago

hello again!

After a bit more research in various posts in the forums and the docs, I have ended up with the code below. It works without error and I know the output from both tables is available to me. However, I’m still stuck on knowing how to get my category name instead of the id to display in my item table. All of the fields in the item table are accessible as placeholders in my chunk but the category field from the related table does not seem to be available. Can anyone help me?

<?php
$path = MODX_CORE_PATH . 'components/items/';
$modx->setOption(xPDO::OPT_HYDRATE_RELATED_OBJECTS, true);
$modx->addPackage('items',$path . 'model/','brox_');

$tpl = $modx->getOption('tpl',$scriptProperties,'Itemrows');

$c = $modx->newQuery('Item');

$items = $modx->getCollectionGraph('Item', '{"Category":{}}', $c );

foreach($items as $item) {

   $itemArray = $item->toArray();
   $output .= $modx->getChunk($tpl,$itemArray);

}

return $output;

21 Posts

jgr Reply #6, 15 years, 9 months ago

OK, I’ve managed to get this now so here is my code in case it helps anyone else

<?php
$path = MODX_CORE_PATH . 'components/items/';
$modx->setOption(xPDO::OPT_HYDRATE_RELATED_OBJECTS, true);
$modx->addPackage('items',$path . 'model/','brox_');

$tpl = $modx->getOption('tpl',$scriptProperties,'Itemrows');

$c = $modx->newQuery('Item');

$items = $modx->getCollectionGraph('Item', '{"Category":{}}', $c );

foreach($items as $item) {

   $itemArray = $item->toArray();   
   $categoryArray = $item->Category->toArray();
   $resultArray = array_merge((array)$itemArray,(array)$categoryArray);
   $output .= $modx->getChunk($tpl,$resultArray);

}

return $output;