Answered [Solved] Cannot use a join in my processor

20 Posts

cdm014 Reply #1, 14 years, 9 months ago

Version: MODX Revolution 2.1.5-pl (traditional)

Am I missing some information about not being able to run joins in the processors?
I have one table of meeting rooms (class name mrRooms) and one of the resources that can be allocated to rooms (class mrResources). For my mrResources getlist processor, I want to take the query parameter and get records where the query string matches the name field of the resource, OR the name field of the room to which the resource is assigned.

I can do the code straight from a controller and it works fine (minus snazzy ajax functionality). But for some reason whenever I use a join statement in the processor, it returns 0 results.

controller code ( I'm using the array $tests to store checks and data):

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

if (!empty($query)){
	$qstring = '%'.$query.'%';
	$c->innerJoin('mrRooms','Rooms','room = Rooms.id');
	$c->where(array(
		'name:LIKE' => $qstring
		,'OR:Rooms.name:LIKE' => $qstring
		
	));
}

$tests['count'] = $count = $modx->getCount('mrResources', $c);
$resources = $modx->getIterator('mrResources',$c);
foreach ($resources as $resource) {
	$resourceArray = $resource->toArray();
	$resourceArray['room_name'] = $modx->getObject('mrRooms',$resourceArray['room'])->get('name');
	$tests[] = $resourceArray;
}

This is the output of the $tests array (there are a few additional checks before the section I pasted):

Array
(
    [mrRoomsGetListPath] => c:/inetpub/wwwroot/test_site/MeetingRooms/core/components/MeetingRooms/processors/mgr/mrRooms/getlist
    [rooms getlist] => 1
    [mrResourceGetListPath] => c:/inetpub/wwwroot/test_site/MeetingRooms/core/components/MeetingRooms/processors/mgr/mrResources/getlist.php
    [resources getlist] => 1
    [query] => one
    [qstring] => %one%
    [count] => 1
    [0] => Array
        (
            [id] => 1
            [name] => resource one
            [max_amount] => 1
            [room] => 1
            [room_name] => test1
        )

)

here is my getlist processor

<?php

//config data
$isLimit = !empty($scriptProperties['limit']);
$start = $modx->getOption('start',$scriptProperties,0);
$limit = $modx->getOption('limit',$scriptProperties,10);
$sort = $modx->getOption('sort',$scriptProperties,'name');
$dir = $modx->getOption('dir',$scriptProperties,'ASC');
$query = $modx->getOption('query',$scriptProperties,'');

$c = $modx->newQuery('mrResources');
$c->setClassAlias('mrResources');
$c->leftJoin('mrRooms','Rooms');
if (!empty($query)){
	$qstring = '%'.$query.'%';
	
	
	$c->where(array(
		'mrResources.name:LIKE' => $qstring
	));
}

$count = $modx->getCount('mrResources', $c);
$c->sortby($sort,$dir);
$resources = $modx->getIterator('mrResources',$c);

//iterate
$list = array();
foreach ($resources as $resource) {
	$resourceArray = $resource->toArray();
	$resourceArray['room_name'] = $modx->getObject('mrRooms',$resourceArray['room'])->get('name');
	$list[] = $resourceArray;
}

return $this->outputArray($list,$count);

I used chrome developer tools to make sure the connector actually loaded (I had a 500 error on a previous issue but it loaded and returned an empty set:

({"total":"0","results":[]})

I don't know if this is a bug, or something I'm not aware of but either way any light that can be spread on it would be appreciated.

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

[ed. note: cdm014 last edited this post 14 years, 9 months ago.]

discuss.answer

20 Posts

cdm014 Reply #2, 14 years, 9 months ago

I ended up having to rewrite the processor to use prepared statements to do the join and then "finesse" the data I could get that way back into a proper data store.

In case anyone else runs into my problem here is my processor rewritten with some comments.

<?php
$isLimit = !empty($scriptProperties['limit']);
$start = $modx->getOption('start',$scriptProperties,0);
$limit = $modx->getOption('limit',$scriptProperties,10);
$sort = $modx->getOption('sort',$scriptProperties,'name');
$dir = $modx->getOption('dir',$scriptProperties,'ASC');
$query = $modx->getOption('query',$scriptProperties,'');
$debug = array();
//query the db
$resTable = $modx->getTableName('mrResources');
$roomsTable = $modx->getTableName('mrRooms');
$qstring = '%'.$query.'%';
$debug['query'] = $qstring;
//build the query note I'm prefacing a lot of the fields with the table name
//I had to do this to get what I needed
$sql = "Select ".$resTable.".id, ".$resTable.".name, max_amount, room, Rooms.name as `room_name` from $resTable join $roomsTable as Rooms on room = Rooms.id where $resTable.name like '$qstring' or Rooms.name like '$qstring'";
$debug['sql'] = $sql;

//actually prepare the statement
$stmt = $modx->prepare($sql);
$stmtArray = array();
//execute the statement. No I'm not using variables here as I did that directly in the 
//sql query
if ($stmt && $stmt->execute()){
	$stmtArray[] = $stmt->fetchAll(PDO::FETCH_ASSOC);
	//$tests['stmtArray'] = $stmtArray;
}
$list = array();

//had to use element[0] to actually get my objects
foreach ($stmtArray[0] as $stmtRes)
{
	//some cleaning up and processing of the data
	$room = $modx->getObject('mrRooms',$stmtRes['room']);
	$stmtRes['room_name'] = $room->get('name');
	//actually add it to the list for output
	$list[] = $stmtRes;
}

//yes count only shows ones that match the search query
$count = count($list);

return $this->outputArray($list,$count);