Hello, There's a pending feature request to allow for removing elements from a SelectQuery again, once they have been added: https://github.com/jOOQ/jOOQ/issues/7588
The current approach is to employ a more functional programming style, where you pass a function to a utility, which constructs the query "body", whereas that utility then decides whether to project all the columns or only the count(*) expression. More info about that coding style here: https://blog.jooq.org/2017/01/16/a-functional-programming-approach-to-dynamic-sql-with-jooq You've done that using the Supplier approach. I wouldn't worry too much about the efficiency of this approach. In all benchmarks I've performed in the past, the creation of the expression tree is orders of magnitude faster than the generation of the SQL string using the internal StringBuilder. There are other options, of course. Including using table(Select) to wrap the select statement in a table, and then using Table.as(String, String...) to alias all the column names using generated aliases, to prevent the ambiguous column name issue. In fact, this issue should not be a problem at all. jOOQ should rename all the ambiguous columns when using DSLContext.fetchCount(Select) and similar methods. I've created an issue for this: https://github.com/jOOQ/jOOQ/issues/7867 Do note that in my opinion, the best option if you absolutely need the exact count(*) value (most often you really don't - this can be avoided through requirements engineering), then a count(*) over() window function is a much more suitable approach. Just this week, both SQLite and H2 have added window functions (H2's implementation has not been released yet). MySQL 8 has added them as well, and so has MariaDB. I hope this helps, Lukas On Fri, Sep 14, 2018 at 9:17 PM Beldrew <[email protected]> wrote: > Is there any way to remove the select portion of a query? IE the > from/joins/where are stable but I want to change between select count(*) > and select (fields). I'm avoiding fetchCount and using selectCount because > fetchCount doesn't handle duplicated field names, and I don't want to do > 'as' for every id field. > > I've tried using the SelectQuery<T> but there doesn't seem to be a way to > 'deep' copy it. It's always points back to the same parameter. So once > you've added something (like addSelect) there's no way to remove it and add > a different select. > > I've managed to get around this using a Supplier<T> but then it re-creates > the statement each time, which seems inefficient. > > Thanks! > > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
