Thread

Hello, when I have a GROUP BY in my sql, the pagination do not get generated by EE
Is this a known limitation of the Query module ?
Is there any workaround ?

This works:

{exp:query limit="30" paginate="both" sql="
SELECT t.entry_id as supplier_id, t.title as supplier_name
FROM exp_weblog_titles as t
WHERE t.weblog_id = '8' 
AND t.title REGEXP 'A'
ORDER BY t.title ASC
"}

This does not work:

{exp:query limit="30" paginate="both" sql="
SELECT t.entry_id as supplier_id, t.title as supplier_name, MIN(p.cat_id) as cat_id
FROM exp_weblog_titles as t, exp_category_posts as p
WHERE t.weblog_id = '8' 
AND t.title REGEXP 'A'
AND p.entry_id = t.entry_id
GROUP BY t.title ASC
"}

Thanks

I’ve encountered this before and I believe it’s because of the way the query module does its pagination… to find the number of results (in order to know whether to paginate and how many links to generate) it appears to run your query again except with “count(*)” as the SELECT condition.  When you GROUP BY something then the count(*) doesn’t return a single number, and I believe it just freaks the pagination out.

http://ellislab.com/forums/viewthread/106198/

one thing I did notice a couple weeks after that thread was over and done with was that I could have gotten around the error by not relying on the alias I gave the expression in the SELECT condition (that gets removed by EE’s second count(*) query) in my ORDER BY clause, but the pagination issue most likely remains the same.

Thanks for the assist, ender.

CimexMedia, does the thread Ty mentioned help?

fwiw I still think this is a silly issue, and that the query module should use php to determine the number of results :p redundant queries are bad 😉  granted I haven’t looked at the code to see for myself, but all signs seem to point at that being the culprit.

Ty, please consider adding a feature request for this.

sort of, I understand I cannot use the Query module for non trivial SQL queries (which is fine by me) but how do I get the pagination without it ?

Do I have to forget about it ? Or code it myself ?

Thanks

you could try using segment variables and/or php to set limits in your query I suppose.  I ultimately decided that for my situation pagination was a nice bonus but ultimately not necessary, so I left it off.  javascript pagination is another option, depending on your situation and content.

by segment variables you mean {segment_x} ?

how can I set up a pagination with these ?

thanks

it would have to be a custom solution using php to dynamically calculate and populate the LIMIT X, Y clause in your query, and of course some way of building the page links.  wouldn’t be easy, but it could probably be done if you really had to.  honestly it might be easier to just hack the query module’s pagination code to do it right… but I haven’t looked at it myself so I can’t really help there.

anyway I basically gave up on pursuing a solution to this issue so that’s about all the help I can give you on it.  good luck 😊

thanks but I don’t really have the time, the client will have to do without pagination.

You can close the thread.

Thanks for letting us know. If you want to revisit this issue at some point in the future, simply start a new thread, please.