base query with separate count + limit queries

[Marcel Overdijk]

Hi,

I'm using this now:

SelectJoinStep baseQuery = jooq
        .select(SEASON.YEAR, SEASON.WIKIPEDIA_URL)
        .from(SEASON);

Query countQuery = jooq
        .selectCount()
        .from(baseQuery);

Query pagedQuery = baseQuery
        .orderBy(toOrderBy(pageable))
        .limit(pageable.getPageSize())
        .offset(pageable.getOffset());

System.out.println("countQuery => " + countQuery.getSQL());
System.out.println("pagedQuery => " + pagedQuery.getSQL());

My base query uses a SelectJointStep type which looks a little bit ugly compared to the Query or Select types.

But I think this is best wat to do it or is there an alternative?

PS: would it be a good idea to start a jooq gitter room for discussions?

Best regards,

M

[Marcel Overdijk]

Maybe even taking it a little bit further.

Would it be possible if I have a org.jooq.Query that I can construct a new Query object from it with an added orderBy, offset and limit?

[Lukas Eder]

Hi Marcel,

Thank you very much for your questions

2016-06-06 15:57 GMT+02:00 Marcel Overdijk <mar...@overdijk.me>:

Maybe even taking it a little bit further.

Would it be possible if I have a org.jooq.Query that I can construct a new Query object from it with an added orderBy, offset and limit?

One of the types you might want to look into is the SelectQuery (construct it via DSLContext.selectQuery()). jOOQ currently has very distinct DSL APIs (feel like SQL) and model APIs (help constructing dynamic SQL). Details here:

http://www.jooq.org/doc/latest/manual/sql-building/sql-statements/dsl-and-non-dsl

The two APIs currently don't work together very well, you'll have to decide between one of them. We're planning on improving this for jOOQ 4.0

As it stands now, your solution is probably as good as it gets - although:

• You could factor out creating the base query in a method / function and implementing the count / pagination in a generic utility
  
• Beware that currently, both DSL and model APIs are mutable to some extent. If you're adding LIMIT / OFFSET, you will *modify* your base query (also for the other usages). This is best remedied by creating new queries for every use-case.
  

Note that if you're using a databse that supports window functions, you can calculate the COUNT value also using COUNT(*) OVER() in a single query

On Monday, June 6, 2016 at 3:01:50 PM UTC+2, Marcel Overdijk wrote:

PS: would it be a good idea to start a jooq gitter room for discussions?

Sure, but inertia and the fact that a lot of users are following this group is prohibiting "quick moves" towards new media. Can you pitch gitter to me?

Advantages of a mailing list:

- They're "offline", so no one expects an immediate reply. This is better for reflected feedback and for keeping our support work low.

- They're archived and easily searchable, also on third party websites (e.g. gmane: http://blog.gmane.org/gmane.comp.java.jooq.user)

- They're easy to moderate

- They're well understood for support use-cases

[Marcel Overdijk]

Thanks a lot for the answers Lukas!

I prefer the DSL API over the model API as it is close to SQL itself.

That being said I think need to stick to the SelectJoinStep type as from that point the order by, limit etc are supported.

I found out about the mutability of the baseQuery already :-) and indeed I'm using a method now to create a new baseQuery object every time.

Also good point regarding the window function, I will have a look at that.

Best regards,

Marcel

[Marcel Overdijk]

Regarding the mutability of the Query would it be possible to clone a (base) Query?

I'm creating some utility methods like:

protected <T> Page<T> queryForPage(SelectJoinStep baseQuery, Pageable pageable,
        RowMapper<T> rowMapper) {

From that baseQuery I want to create a count query and paged query (note windowing functions is not supported in my database).

When I first construct the count query like:

jooq
        .selectCount()
        .from(baseQuery);

and then paged query like:

baseQuery
        .orderBy(toOrderBy(pageable))
        .limit(pageable.getPageSize())
        .offset(pageable.getOffset());

I think it should be ok as I believe the selectCount() will not alter the baseQuery.

But in case I create the paged query before the count query the baseQuery was already altered which might be error prone.

Best regards,

Marcel

On Tuesday, June 7, 2016 at 7:46:39 AM UTC+2, Lukas Eder wrote:

[Lukas Eder]

Hi Marcel,

2016-06-07 10:16 GMT+02:00 Marcel Overdijk <mar...@overdijk.me>:

Regarding the mutability of the Query would it be possible to clone a (base) Query?

Right now, no. jOOQ query parts are not cloneable.

 

I'm creating some utility methods like:

protected <T> Page<T> queryForPage(SelectJoinStep baseQuery, Pageable pageable,
        RowMapper<T> rowMapper) {

From that baseQuery I want to create a count query and paged query (note windowing functions is not supported in my database).

When I first construct the count query like:

jooq
        .selectCount()
        .from(baseQuery);

and then paged query like:

baseQuery
        .orderBy(toOrderBy(pageable))
        .limit(pageable.getPageSize())
        .offset(pageable.getOffset());

I think it should be ok as I believe the selectCount() will not alter the baseQuery.

That's correct, but you have to make sure you execute the first query before you modify the base query for the latter case.

 

But in case I create the paged query before the count query the baseQuery was already altered which might be error prone.

Yes.

I don't think you're saving a lot of CPU cycles this way. Personally, I'd just rather not recycle these objects.

Lukas

[Marcel Overdijk]

Hi Lukas,

I'm not worried about saving the CPU cycles.

The reason I would like to recycle these objects as I would be able to use some generic utility methods like:

protected <T> Page<T> queryForPage(SelectJoinStep baseQuery, Pageable pageable,
        RowMapper<T> rowMapper) {

I would only need to provide a baseQuery in this case with a pageable object.

The utility method then simply executes 2 queries after tweaking the baseQuery and constructs the Page to be returned.

That way I could do something like in my repositories:

public Page<Season> findAll(Pageable pageable) {
    SelectJoinStep baseQuery = jooq
            .select(
                    SEASON.YEAR,
                    SEASON.WIKIPEDIA_URL)
            .from(SEASON);
    return queryForPage(baseQuery, pageable, seasonRowMapper);
}

 which is very powerful imo.

Currently by executing the selectCount().from(baseQuery) does not affect the baseQuery so I just have to make sure I run that first :-)

Marcel

[Lukas Eder]

2016-06-08 9:58 GMT+03:00 Marcel Overdijk <mar...@overdijk.me>:

Hi Lukas,

I'm not worried about saving the CPU cycles.

The reason I would like to recycle these objects as I would be able to use some generic utility methods like:

Oh, I see! Then, inverse responsibilities and go functional! How about:

 

protected <T> Page<T> queryForPage(Supplier<SelectJoinStep> baseQuery, Pageable pageable,
        RowMapper<T> rowMapper) {

Now you can pass functions to the methods that construct your base query and in your queryForPage method, you will simply call that function twice. How does that sound?

Example:

queryForPage( () -> select(TABLE.A, TABLE.B).from(TABLE), pageable, mapper );

Lukas

[Marcel Overdijk]

Yes thx Lukas, I think this is the best option at the moment.