XPDO Queries, Unions to get max values from table.

160 Posts

insomnix Reply #1, 14 years, 10 months ago

So I'm still trying to learn the ins and outs of modx and xpdo. I'm pretty good with mysql queries, but I'm having some trouble migrating to the xpdo format. I have two tables. The first is a client list, the second is a progress notes table. There is a date field and a contact type. I need to display the last date for direct contact, and the last date of indirect contact. My query looks like:

SELECT ClientFirstName, (

SELECT MAX( ProgDate )
FROM progress
WHERE ClientNum = ClientDirNum
AND (
ProgType =1
)
) AS LastDirectContact, (

SELECT MAX( ProgDate )
FROM progress
WHERE ClientNum = ClientDirNum
AND (
ProgType =3
)
) AS LastInDirectContact
FROM clients
WHERE Employee =16
AND ClientFinished IS NULL
ORDER BY ClientFirstName

Works great, but I'm not sure how to turn this into a union. I looked through the documentation, but only found a brief mention of unions and there were no examples.

Any help would be appreciated.

MODX Staff
10,725 Posts

opengeek Reply #2, 14 years, 10 months ago

xPDO does nothing regarding unions. You can execute a prepared statement or direct SQL statement via PDO methods as you would any other query and work with the result set in a familiar way. xPDO adds tools to PDO for mapping tables and rows to model objects; queries like this generally do not represent a single object and as such are generally not appropriate to load as model objects.

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

160 Posts

insomnix Reply #3, 14 years, 10 months ago

Took a look at your link, then looked through the xpdo documentation again.

http://rtfm.modx.com/display/xPDO20/Retrieving+Objects

I came up with:

$Clients = $modx->getTableName('Clients');
$Progress = $modx->getTableName('Progress');

$query= new xPDOCriteria($modx,'
    SELECT *, 
	(SELECT MAX( ProgDate ) FROM '.$Progress.' WHERE ClientNum=ClientDirNum and (ProgType=1)) AS LastDirectContact, 
	(SELECT MAX( ProgDate ) FROM '.$Progress.' WHERE ClientNum=ClientDirNum and (ProgType=3)) AS LastInDirectContact
	FROM '.$Clients.'
    WHERE `Employee` = :Employee
	AND `ClientFinished` IS NULL
',array(
   ':Employee' => 16
));
$clientlist = $modx->getCollection('Clients',$query);

and everything works the way it's supposed too. Not sure why I couldn't get this last night, seems simple enough now. Hope it helps the next person that comes along.

Thanks