Query with alias in where clause

24 Posts

egraaf Reply #1, 10 years, 7 months ago

I wonder if you can use an alias in a "where" statement.

For instance:

$query = $modx->newQuery('request');
$query->select(array(
    'id',
    'lastupdate' ,
    'DATEDIFF(NOW(),lastupdate) AS Age'
));
$query->where(array(
    'Age:>=' => 7
));

I tried this as well:

$query->where(array(
    'DATEDIFF(NOW(),lastupdate):>=' => 7
));

In both causes I get "an unknown column" error.

I hope someone can help me. Thanks in advance.

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

24,544 Posts

BobRay Reply #2, 10 years, 7 months ago

How about something like this:

$cutoff = strtotime(-1 week); /* 7 days ago */

$query->where(array(
     (string) $cutoff . ':>=' => 'lastupdate'
));

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

24 Posts

egraaf Reply #3, 10 years, 7 months ago

Quote from: BobRay at Jan 05, 2016, 02:21 PM

How about something like this:

$cutoff = strtotime(-1 week); /* 7 days ago */

$query->where(array(
     (string) $cutoff . ':>=' => 'lastupdate'
));

The problem is that de resulting query looks like this:
SELECT `id`, `lastupdate`, `state`, `file`, DATEDIFF(NOW(),lastupdate) AS Age, OCTET_LENGTH(file) AS FileSize FROM `modx_my_requests` AS `myRequest` WHERE ( `myRequest`.`state` = 6 AND `myRequest`.`1451466840` >= 'lastupdate' ) ORDER BY lastupdate ASC

The part "`myRequest`.`1451466840`" results in an error.

24,544 Posts

BobRay Reply #4, 10 years, 7 months ago

Try removing this line:

'DATEDIFF(NOW(),lastupdate) AS Age'

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

24 Posts

egraaf Reply #5, 10 years, 6 months ago

Quote from: BobRay at Jan 06, 2016, 12:29 PM

Try removing this line:
'DATEDIFF(NOW(),lastupdate) AS Age'

I think that will work but then I loose the column Age in the query output.

Is this what you mean?

$query = $modx->newQuery('request');
$query->select(array(
    'id',
    'lastupdate'
));
$query->where(array(
    'DATEDIFF(NOW(),lastupdate):>=' => 7
));

I need the column "Age" to process the output. I have written a snippet:

$requests = $modx->getCollection('request',$query);
foreach ($requests as $r)
{
  	$request=$r->toArray();

  	if ($request[Age]>7)
        ......

This is Ok for the moment. The collection contains too much elements, but that's all.
I was wondering if there was a more efficient way to get the records, but it seems to be a problem using an alias.

discuss.answer

5,888 Posts

Bruno17 Reply #6, 10 years, 6 months ago

try

$query = $modx->newQuery('request');
$query->select(array(
    'id',
    'lastupdate' ,
    'DATEDIFF(NOW(),lastupdate) AS Age'
));

$query->having('Age >= 7');

or

$query = $modx->newQuery('request');
$query->select(array(
    'id',
    'lastupdate' ,
    'DATEDIFF(NOW(),lastupdate) AS Age'
));

$query->where('DATEDIFF(NOW(),lastupdate)  >= 7');

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

Answered Query with alias in where clause