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
On Tuesday, February 2, 2016 at 11:40:59 AM UTC+1, Lukas Eder wrote:
>
> 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
>
> 2016-02-01 20:52 GMT+01:00 Marcel Overdijk <[email protected]
> <javascript:>>:
>
>> 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:
>>>
>>> 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
>>>
>>> 2016-02-01 15:02 GMT+01:00 Marcel Overdijk <[email protected]>:
>>>
>>>> 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?
>>>>
>>>>
>>>>
>>>> --
>>>> 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] <javascript:>.
>> 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.
