GROUP BY statement in databasejoin

[jfquestiaux]

I know the tooltip refers only to WHERE and ORDER, but I noticed this in the "Joins where and/or
order by statement (SQL)" of the databasejoin element:

This statement returns correct data:
WHERE actor_id = 9 GROUP BY user_id

But this one returns an SQL error (1064 - You have an error in your SQL syntax) :
GROUP BY user_id

So it seems that GROUP BY works only when associated with WHERE, because this:
ORDER BY full_name ASC GROUP BY user_id

Returns also an SQL error.

Unfortunately, it is this last statement I'd need. Is it possible ?

Tested is 3.1RC1 - GitHub 5

 

[rob]

HI
what are you trying to achieve? what sql error do you get?

 

[jfquestiaux]

As I said, I tried to have this statement in the"Joins where and/or order by statement (SQL)" box:
ORDER BY full_name ASC GROUP BY user_id

but this gives me this error:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY user_id' at line 3 SQL=SELECT DISTINCT(`directory_0`.`id`) AS value, `full_name` AS text FROM `directory` AS `directory_0` ORDER BY full_name ASC GROUP BY user_id

Anyway, I managed to solve my problem by using a WHERE statement before the GROUP BY. This works:
WHERE language_id = 'fr-FR' GROUP BY user_id

So, as far as I am concerned, my problem is solved, but there seems to be an issue there anyway.

 

[rob]

why not use ORDER BY user_id, full_name then?
Or inversing the order by and group by?
I think there are certain mySQL constraints on using the two together. Googling found several pages asking about it anyway
.