How to get all fields from joined tables in one query() using leftJoin()

18 Posts

dev7apps Reply #1, 11 years, 9 months ago

When using $mod->newQuery(); and joining a table, how can I get the data returned to include the fields from the joined table?

Right now, I have to run the query, then based on the results, run getObject() to retrieve an object that was already used in the join. Is it possible to grab it all with one swoop?

The query is searching for customers based on orders imported. Thre tables, OrderImports, Skus, and Products. I don;t think the details of the data is too relevant for this question, but if it helps please let me know.

Example: Searching for a Customer LIKE 'Mike%' in $queryArray. I get the correct results, so the query works. I just want to avoid the extra step ***

This is my current query:

//BuIld Query to search customers
	$query = $modx->newQuery('OrderImports');
	$query->leftJoin('Skus', 'Skus', array(
		'OrderImports.aps_id' => 'Skus.aps_id',
	));
	$query->where($queryArray);
	$query->limit(50);
	//Search customers
	$customers = $modx->getCollection('OrderImports',$query);
	if($customers){
		//lopp thorugh each customer
		foreach($customers as $customer){
			//find the product id of the SKU relationship 
//***(trying to skip step step below since skus is already used in join)

			$prodSkus = $modx->getObject('Skus',array('aps_id'=>$customer->get('aps_id')));
            if(!$prodSkus){
				//if SKU is not found, go to next customer
                continue;
            }
//**trying to skip step above
			//grab the product based on product ID
			$prodObj = $modx->getObject('Products',array('ap_id' => $prodSkus->get('ap_id')));
			if($prodObj){
				//do stuff with customer and product object and return output to user
				
			}else{
				//move on to next
				continue;
			}
		}
	}

Using MySQL, Php 5.5, Apache

24,544 Posts

BobRay Reply #2, 11 years, 9 months ago

If things are set up correctly (i.e., the objects are related properly by aliases in the schema), you should be able to leave out the left join and do something like this:

/* Your query code here */

$customers = $modx->getCollectionGraph('OrderImports', '{"Skus":{}, "Products":{} }', $query);

foreach ($customers as $customer) {
    $name = $customer->get('username');
    $sku = $customer->Skus->get('sku');
    $productName = $customer->Products->get('name');

}

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

18 Posts

dev7apps Reply #3, 11 years, 9 months ago

Thanks BobRay!

Unfortunately, we don't have the relations set up, but seeing this code makes a heck of a lot more sense. Need to adjust the schemas and test this out. Will update as soon as this is in place.

128 Posts

Michael Engel Reply #4, 11 years, 9 months ago

You could also use

$modx->setOption(xPDO::OPT_HYDRATE_ADHOC_FIELDS, true);

$query = $modx->newQuery('OrderImports');
$query->select($modx->getSelectColumns('OrderImports','OrderImports',''));
$query->select($modx->getSelectColumns('Skus','Skus','Skus_'));
$query->leftJoin('Skus', 'Skus', array(
        'OrderImports.aps_id' => 'Skus.aps_id',
    ));
$query->where($queryArray);
$query->limit(50);

$customers = $modx->getIterator('OrderImports',$query);
foreach($customers as $customer){
   echo $customer->get('Skus_aps_id');
}

this will only do one Database Lookup instead of multiple, an good way if you don`t need any methods out of Products.

when having the relationship in the mappings you can also let the binding empty and just use

$query->leftJoin('Skus', 'Skus');

Another hint, when using selects and think of speed enhancements use $modx->getIterator() instead of $modx->getCollection(). This will load only one row after row into the RAM instead of all in an big array.