Thanks, makes sense. This just came up because we have a generic utility for pagination that always results in calling Dataset#count, but I can just handle this case separately and use the subquery approach.
On Fri, Jul 19, 2019 at 9:49 AM Jeremy Evans <[email protected]> wrote: > On Thursday, July 18, 2019 at 6:48:28 PM UTC-7, Stephen Weil wrote: >> >> Hello, >> >> I ran into this issue with Sequel and MariaDB/MySQL, with a query that >> uses Dataset#select_append to get some calculated values and then uses >> Dataset#having to filter by those calculated fields. This produces a MySQL >> syntax error, as Dataset#count ensures that COUNT is the only thing in the >> select clause, and thus makes the HAVING clause fail. The error can be seen >> here: https://gist.github.com/sjweil9/4b327714ea2900a5348985d3c044bc58. >> >> I suspect that there is probably a better way to go about this (from an >> SQL perspective), but for MySQL at least I believe it is technically valid >> to use HAVING without a GROUP BY to allow referencing the SELECT alias >> field. It may just be fair that the Dataset#count is properly optimized to >> remove other fields from the SELECT clause and thus should not be used with >> a having clause, and that I should resort to something like the alternative >> in the gist (using the subquery in a FROM clause and counting that). Is >> this the expected behavior or is there a better way for me to approach this? >> >> Thank you for your help as always! >> >> Best, >> >> Stephen >> > > > Most databases can handle HAVING without GROUP BY (IIRC SQLite is an > exception). Dataset#count is expected to modify the select list, so you > should use a subquery for what you want (try Dataset#from_self). > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sequel-talk/5af09c04-7447-456b-9192-ae0ae7ed088f%40googlegroups.com > <https://groups.google.com/d/msgid/sequel-talk/5af09c04-7447-456b-9192-ae0ae7ed088f%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/CANba2HO7-Hc%3DPgC58ZkHviVv5onrdEZEyfBGcWRduy6U%3DePbsw%40mail.gmail.com.
