Thread

It seems that Query module and $DB global have problems with SQL aggregate functions such as MIN or MAX.

E.g. the code as this

{exp:query sql="SELECT email AS e_mail, MIN(comment_id) FROM exp_comments WHERE status = 'o' AND entry_id = '6' AND notify = 'y' AND email != '[email protected]' GROUP BY email ORDER BY comment_date DESC"}

{e_mail}, {comment_id}



{/exp:query}

will leave variable {comment_id} unparsed, although without MIN it works correctly.

Similar problem exists with $DB global. E.g. the code as this

global $DB

sql="SELECT email AS e_mail, MIN(comment_id) FROM exp_comments WHERE status = 'o' AND entry_id = '6' AND notify = 'y' AND email != '[email protected]' GROUP BY email ORDER BY comment_date DESC"

$query = $DB->query($sql);

print_r($query->result);

will output array with strange indexes such as these:

Array ( [0] => Array ( [email] => .(JavaScript must be enabled to view this email address) [MIN(comment_id)] => 18780 ) [1] => Array ( [email] => .(JavaScript must be enabled to view this email address) [MIN(comment_id)] => 18768 ) )

I use EE 1.6.9 build 20100805 .

It seems like a bug, isn’t it?

Hi Laisvunas,

Try

... MIN(comment_id) AS comment_id ...

Does that work?

Thanks Brandon.

Written this way the code works. It would be great if this feature (aggregate functions not working without aliases in Query module and $DB global) were documented in appropriate places of the docs.

Now this thread can be closed.

Glad Brandon was able to help. Feel free as always to start a new thread if you have any more questions.