Hi,
I follow the handbook example which you have kindly shared. but the result is not what it's suppose to be. The problem is that in many situations one may need to use filters. But this module considers filters as Group By fields which corrupts the result. for example if i filter by promote to front page this field will be added to Group By statement even when it's not select in fields.
Another problem is i don't want title to be included as group by but i want to be available in result set. i don't know how to do that.
And the last one is grouped fields appear twice in group by statement. it does not causes any problem but just wanted notify you
Here is generated query :
SELECT COUNT(node.nid) AS nid,
node.title AS node_title,
term_data.name AS term_data_name,
vocabulary.name AS vocabulary_name,
node.sticky AS node_sticky,
node.created AS node_created
FROM common_node node
LEFT JOIN common_term_node term_node ON node.vid = term_node.vid
LEFT JOIN common_term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN common_vocabulary vocabulary ON term_data.vid = vocabulary.vid
WHERE (node.promote <> 0) AND (node.status <> 0) AND (node.language in ('***CURRENT_LANGUAGE***', '***NO_LANGUAGE***')) AND (term_data.vid in ('12'))
GROUP BY node_title, term_data_name, vocabulary_name, node_sticky, node_created, node_title, term_data_name, vocabulary_name
ORDER BY node_created ASC, node_sticky DESC, node_created DESC
Here is my views code : http://pastebin.com/m6981460d
PS : Thanks for sharing this useful module
Comments
Comment #1
sinasalek commentedIt seems to me a conflict, views uses group_by internally and add fields in filters before this module uses it. but i don't know why and where it calls it :(
Comment #2
irakli commentedI am afraid what you are describing, while valid for MySQL, is invalid query in more strict ANSI SQL specification, is incompatible with PostgreSQL and is not supported by Views, therefore can't be supported by views_groupby, either.
Comment #3
sinasalek commentedThanks for the response irakli,
So you mean that having every fields in WHERE statement included in Group By is ANSI ?
Comment #4
irakli commentedI was talking about: "Another problem is i don't want title to be included as group by but i want to be available in result set"
ANSI SQL states that any field selected must either be run through an aggregate function or be part of group_by.
Comment #5
sinasalek commentedAh i see, yes you're correct about that. that one was a wrong question.
What about the main issue "filters included as group by"? i think it's bug, but i'm not sure if it should be fixed group_by or views