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.
