How to use synthetic column alias in WHERE/HAVING

6 Posts

kevinyank Reply #1, 14 years, 4 months ago

I’m using xPDO to perform a SELECT query that generates a couple of “synthetic” columns (i.e. fields calculated dynamically by the query). This works fine, except when it comes to referring to these columns in a WHERE or HAVING clause.

Here’s an example:

$q = $modx->newQuery('Show');
$q->innerJoin('ShowDate', 'ShowDate');
$q->select();
$q->select(array(
	'startDate' => 'MIN(ShowDate.moment)',
	'endDate' => 'MAX(ShowDate.moment)',
));
$q->groupby('Show.id');
$q->having(array(
	// starts over 1 week from now
	'startDate:>=' => date('Y-m-d 00:00:00', time() + 604800)
));
$q->sortby('startDate');
$shows = $modx->getCollection('Show', $q);

See the call to having? That causes an error, as xPDO converts the unqualified column name startDate to `Show`.`startDate`, which doesn’t exist.

How do I tell xPDO that the startDate column I’m referring to doesn’t belong to a particular table in my query, and therefore doesn’t need to be qualified with a table name?

6 Posts

kevinyank Reply #2, 14 years, 4 months ago

After examining the code of xpdoquery.class.php, I figured out I could achieve what I wanted by passing a conditional clause to having, instead of an associative array:

$q->having(
    // starts over 1 week from now
    'startDate >= "' . date('Y-m-d 00:00:00', time() + 604800) . '"'
);

Is there a better way?

MODX Staff
10,725 Posts

opengeek Reply #3, 14 years, 4 months ago

That is the best way atm; the associative array conditions are meant for columns of the table. Did you try escaping startDate in the condition key, e.g. '`startDate`:>=' => ... ?

BTW, this query would only work in MySQL (and SQLite though the sort would be different). In most RDBMS's, you can not select columns that are not included in a group by (see GROUP BY and HAVING with Hidden Columns for more information). IOW, not including the calculated columns, you need the same columns in the group by as you do in the select, especially when hydrating objects (i.e. getCollection) from such result sets.

If you are not concerned about making your queries generic enough to work on other database engines, then you can ignore this warning.

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