Extending a base query to get count and a actual page result

[Marcel Overdijk]

Imagine I have a query like:

String someName = ".."

String sql = jooq
        .select(
                field("first_name"),
                field("last_name"))
        .from(
                table("driver"))
        .where(
                field("last_name").equal(someName))
        .orderBy(
                field("first_name").asc())
        .getSQL(ParamType.INLINED);

I want to basically reuse this base query to actually:

1) select the count for the given base from/where.
2) extend the query to limit the result set to given page size/offset.

Would it be possible to create some base part query and then change the select cause (count vs. actual columns) and the where (add pagesize/offset) and perform the 2 queries?

[Lukas Eder]

Hi Marcel,

There are different ways to tackle this problem. The most robust and straight-forward solution would involve putting your query in a derived table, and operating on that to get the count:

Select<?> select = jooq.select(...) // Your existing SELECT

int count = jooq.fetchCount(select);

Result<?> page = jooq.selectFrom(table(select)).limit(...).offset(...).fetch();

This will work for (almost) any type of query. Unfortunately, some databases (specifically MySQL) are known to be bad at SQL transformation, so you probably want to avoid unnecessary derived tables in MySQL.

Another option is to see queries not as static objects, but as functions in your application. Specifically:

public Select<?> drivers(Integer limit, Integer offset, Field<?>... selectColumns) {

    return jooq

    .select(selectColumns)

    .from(

            table("driver"))

    .where(

            field("last_name").equal(someName))

    .orderBy(

            field("first_name").asc())

    .limit(limit == null ? Integer.MAX_VALUE : limit)

    .offset(offset == null ? 0 : offset);

}

There are of course other ways to achieve the same. The interesting lesson here, however, is to see jOOQ statements as composable, re-usable elements that can be nested into each other, or created using functions / methods.

I hope this helps,

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marcel Overdijk]

Thanks Lukas,

You are there a ways to do this like the example 'function' method.

The first example looks more clean imo but unfortunately I'm using MySQL so probably not smart to use that approach.

Regarding composability and the re-usable element it would make sense to re-use this portion:

from =

    .from(

            table("driver"))

    .where(

            field("last_name").equal(someName))

    .orderBy(

            field("first_name").asc())

String countSql = jooq.selectCount().from(from).getSQL(ParamType.INLINED);

String pageSql = jooq.select(field("a"), field("b")).from(from).limit(limit).offset(offset).getSQL(ParamType.INLINED);

But I assume this is not possible and should better go for the approach in your 2nd example?

[Marcel Overdijk]

Another reason I'm asking about re-using something like:

from =

    .from(

            table("driver"))

    .where(

            field("last_name").equal(someName))

    .orderBy(

            field("first_name").asc())

is that we currently have many sql queries that have such a part (albeit more complex then the above).

And based on the query we have different columns we need to retrieve, sometime a join to add, and additional where conditions.

Typically such a base from/where would be solved using a database view but unfortunately that's not an option (external db for which we cannot create db objects). 

That's why re-using such a from/where.

We are currently not using jOOQ but I'm experimenting and trying to see how far we can push it to help us in our situation.

Thanks,

Marcel

On Monday, February 1, 2016 at 5:11:07 PM UTC+1, Lukas Eder wrote:

[Lukas Eder]

Hi Marcel,

Thanks for your additional explanations.

There are several levels of dynamic query. To a certain extent, creating a query via a simple function as I had suggested might be sufficient. I.e. it was a sufficient suggestion for the example you provided (dynamic SELECT and LIMIT .. OFFSET clauses).

As dynamic querying becomes more and more complex, a more sophisticated approach might be more reasonable. Just some days ago on this list, Max Kremer had explained an approach where tables and their foreign key relationships are modelled as a graph (tables = vertices, relationships = edges):

https://groups.google.com/d/msg/jooq-user/tIjEH8LK9nA/bxd3e1vvDAAJ

That way, dynamic joins can be added to a FROM clause via a topological sort.

The important thing, however, is not to think of individual or "compound" SQL clauses as reusable things. I.e. "FROM driver WHERE last_name = someName ORDER BY first_name ASC" is not the reusable part of your query. SQL is not a very composable language, but the individual clauses contain expressions which are well composable.

We're currently investigating how jOOQ could add additional help for the ideas that Max Kremer pointed out, but we don't have an out-of-the-box solution yet. It can certainly be done though.

In other words: You'll need to experiment a bit more :) And I'd love to learn more about your experience. I'm sure your work can help us find new potential feature requests.

Best Regards,

Lukas

[Marcel Overdijk]

Hi Lukas,

That was an interesting post link.

After experimenting a bit more I found this quite useful:

Table<?> table = table("driver")
        .join(table("country"))
        .on(field("driver.country_id").equal(field("country.id")))
        .join(table("continent"))
        .on(field("country.continent_id").equal(field("continent.id")));

Condition where = field("driver.last_name").equal("Verstappen")
        .and(field("country.id").equal("NL"));

SortField<?> orderBy[] = new SortField<?>[]{
        field("driver.last_name").asc(),
        field("driver.first_name").asc()
};

Select<?> count = jooq.selectCount()
        .from(table)
        .where(where);

Select<?> paged = jooq.select(
        field("driver.first_name"),
        field("driver.last_name"),
        field("driver.full_name"),
        field("country.id"),
        field("country.name"),
        field("continent.id"),
        field("continent.name"))
        .from(table)
        .where(where)
        .orderBy(orderBy)
        .limit(10)
        .offset(0);

However I have some questions:

1. I'm using the static table(..) and field(..) methods. Is this ok as it does not go through my DSLContext instance?
2. Is there an easier way to construct the order by?
   Instead of creating a SortField<?>[] something like field(..).asc().and().field(..).asc() would be useful. Just like building the condition.
   Maybe this can be done already with current api?
3. If you have other remarks let me know.

Cheers,

Marcel

[Lukas Eder]

Hi Marcel,

2016-02-03 12:50 GMT+01:00 Marcel Overdijk <mar...@overdijk.me>:

1. I'm using the static table(..) and field(..) methods. Is this ok as it does not go through my DSLContext instance?

 

Sure. As a general rule of thumb: DSLContext.xxx() methods create DSL objects in the context of a Configuration, which essentially contains ConnectionProvider, Settings, SQLDialect, and other SPIs. This is useful for creating executable statements, not for Field / Table / etc. expressions.

2. Is there an easier way to construct the order by? Instead of creating a SortField<?>[] something like field(..).asc().and().field(..).asc() would be useful. Just like building the condition. Maybe this can be done already with current api?

Hmm, no... But the artificial keyword appears a bit strange, no? In SQL, a comma-separated list is used instead.

Of course, you can supply a Collection<? extends SortField<?>> instead of the array, if that better suits your needs... Most methods that accept arrays/varargs also accept an equivalent collection argument.

[Marcel Overdijk]

OK thanks!

I'm quite happy with the result of the experiment btw.

Cheers,

Marcel

[Lukas Eder]

I'm glad to hear that! :)

--

[Marcel Overdijk]

One quick question though.

Is it possible to create an empty condition or something similar?

Dependent on field values I want to dynamically construct an condition.

The condition can be null (empty), 1 condition or 2 conditions.

Condition where = some empty condition;

if (StringUtils.isNotBlank(country)) {
    where = where.and(field("country.id").equal(country));
}
if (StringUtils.isNotBlank(name)) {
    where = where.and(field("driver.full_name").likeIgnoreCase(name));
}

The only thing I'm thinking of is to create the condition with something like 1=1.

Is that the best approach and can I create something like?

Condition where = field("1").equal(1);

(don't know if above syntax will work. Should I use field("1")?

Cheers,

Marcel

[Lukas Eder]

2016-02-03 16:54 GMT+01:00 Marcel Overdijk <mar...@overdijk.me>:

One quick question though.

Is it possible to create an empty condition or something similar?

Use DSL.trueCondition() for connecting an "empty" condition with AND, or DSL.falseCondition() for connecting an "empty" condition with OR.

Alternatively, you can use DSL.condition(Operator, Condition...) or DSL.condition(Operator, Collection<? extends Condition>). The resulting condition will also generate trueCondition() or falseCondition(), if empty.

Of course, you can always create your own plain SQL "empty" condition via DSL.condition("1 = 1") or DSL.condition("1 = 0").

Hope this helps,

Lukas

[Marcel Overdijk]

Thanks! TrueCondition was exactly what I was looking for.