Improve count query handling

[daddz]

The Count() query does not properly strip any Preload...() (and ThenLoad...()) mods from the query.

This is especially cumbersome for queries where you need to filter the count based on a related table (to-one relation) with the auto-generated code. You have to build 2 different queries, one with SelectJoins for filtering and counting and another one with Preload...(psql.PreloadAs(...)) to again filter and get the actual results (All()).

The fact that Count() also modifies the original query makes re-using queries harder than necessary. (related discussion: https://github.com/stephenafamo/bob/discussions/177)

My initial solution was to simply modify the Count() method to also strip the PreloadColumns and Load fields of the base query which makes it at least possible to use it Count() together with Preload...() and ThenLoad...() but that still wasn't as nice.

Now I:

• added SetPreloadSelect to override the preload columns
• added SetOrderBy to override ordering
• added SetMapperMods to override the mapping mods
• added SetLoaders to override the loaders
• added asCountQuery
  • which clones the original query so we can freely modify it for the count query without affecting the original one
  • modified the Count method on View to pass the cloned and modified query

I guess there's an even nicer way to do it but I'm not that familiar with bob's code-base (yet) to come up with something better. :smile:

@stephenafamo: Let me know if this is a good approach and I'll add the asCountQuery variations for mysql and sqlite as well.

[stephenafamo]

This is a very good approach. I like it! This solves most of the issues around Count()

Although it would be less performant since it clones the whole query, I think it is worth it since it makes Count() work as expected.

One other thing that should be stripped is GroupBy().

[daddz]

@stephenafamo I added the GroupBy() stripping, the mysql and sqlite variants as well as a tip in the readme and changelog notes

[daddz]

I guess the asCountQuery could be reworked in the future to live only once outside the dialect/* but I couldn't find a satisfying solution so far. This might be a start at least.

[stephenafamo]

I guess the asCountQuery could be reworked in the future to live only once outside the dialect/*

I think it can be in internal

This way it can be reused, but it is not part of the public API.