Combining multiple contexts into a single in memory resultset

[Aram Mirzadeh]

Hi, 

I'm wondering if there is easy way of combining multiple resultsets into a single object?  I know that I can manipulate the heck out of a MockResultSet but I figured it's worth checking to see if there is an easier way with Jooq.

I need to get a ResultSet that is a combination of multiple queries.   Sub-selects are a 1:many of the first SQL:

Q1: SELECT id,name,model,manufacturer from cars; 

Q2: .. for each car.id -> SELECT '',name,model,manufacturer,carId_fk from tires where tire.id=carId_fk; 

The final output would be:

1,Foo,Toyota,RAV4

 ,XXX,GoodYear,Eagle Sport,1

 ,X1Y,GoodYear,Snow,1

2,Bar,BMW,325i 

,YYY,BFGoodrich,AllTrain,2

,Y22,BFGoodrich,G-Force,2

,X2Y,GoodYear,SnowExtreme,2

etc.... 

I used do this as one main outer for loop and one inner ones that build the spreadsheet over time. 

The new system will accept a ResultSet argument, and a Type[] array (for each column), that is then taken and converted into a spreadsheet.

Thanks.

[Daniele Antonini]

Hi,

Why don't you use a join to retrieve all required columns?

[Aram Mirzadeh]

 Sub-selects are a 1:many of the first SQL

[Aram Mirzadeh]

Since there doesn't seem to be any easier way to manipulate the JOOQ Record or POJO objects.  Anyone write an implementation of ResultSet or JOOQ that they're willing to share?  I hate to re-invent the wheel.

[Lukas Eder]

2015-05-21 21:21 GMT+02:00 Aram Mirzadeh <a...@tranquilphotos.com>:

Hi, 

I'm wondering if there is easy way of combining multiple resultsets into a single object?  I know that I can manipulate the heck out of a MockResultSet but I figured it's worth checking to see if there is an easier way with Jooq.

I need to get a ResultSet that is a combination of multiple queries.   Sub-selects are a 1:many of the first SQL:

Q1: SELECT id,name,model,manufacturer from cars; 

Q2: .. for each car.id -> SELECT '',name,model,manufacturer,carId_fk from tires where tire.id=carId_fk; 

The final output would be:

1,Foo,Toyota,RAV4

 ,XXX,GoodYear,Eagle Sport,1

 ,X1Y,GoodYear,Snow,1

2,Bar,BMW,325i 

,YYY,BFGoodrich,AllTrain,2

,Y22,BFGoodrich,G-Force,2

,X2Y,GoodYear,SnowExtreme,2

etc.... 

I used do this as one main outer for loop and one inner ones that build the spreadsheet over time. 

Using jOOQ to simplify "N+1" queries

This would be commonly called "N+1" problem, where you would have execute Q1 first (1), and then for each row in Q1's result, execute Q2 again (N). If you're fine with that, jOOQ already has the UpdatableRecord.fetchChildren() method to simplify the task:

http://www.jooq.org/javadoc/latest/org/jooq/UpdatableRecord.html#fetchChildren-org.jooq.ForeignKey-

Fetching all child records in a single query

jOOQ allows you to fetch "child" results after having fetched the "parent" result via Result.fetchChildren:

http://www.jooq.org/javadoc/latest/org/jooq/Result.html#fetchChildren-org.jooq.ForeignKey-

This would help you select all the tires from the cars result in one go, although you would still have to manually combine them each.

The SQL way to nest result sets

The SQL way to resolve this problem would be by using the MULTISET operator. You would nest your Q2 in Q1 as follows:

SELECT 

    id, name, model, manufacturer,

    MULTISET(

        SELECT 

            name, model, manufacturer, carId_fk 

        FROM tires 

        WHERE tire.id = carId_fk

    ) tires

FROM cars

The MULTISET operator is supported by hardly any database (Oracle, Informix, CUBRID, and to some extent, you can emulate it with PostgreSQL), and we don't currently support it yet with jOOQ:

https://github.com/jOOQ/jOOQ/issues/3884

I believe that this is what you're really after, and we hope to be able to implement this in jOOQ 3.7, emulating it for simple use-cases in databases that don't support it, as we could probably issue two queries and combine results in memory.

I hope this helps as a starter. Or perhaps, you had a different, more concrete solution in mind?

[Aram Mirzadeh]

Hi Lukas, 

Yes, the end result is exactly what we're trying to do.  It's really a code-deduplication effort.  Right now the API takes a single ResultSet and converts and writes it out an Excel Sheet. 

We're on MySQL 5.6, moving to 5.7 and I don't believe MULTISETs are even on the roadmap so that's not a solution that we can use.   If I'm reading the javadoc right, using the Parent-FK-Child method, I can recreate the above ResultSet in memory. 

Thank you very much.

[Lukas Eder]

Hi Aram,

Indeed MULTISETs probably won't be in MySQL any time soon. There are more important feature additions, like common table expressions, window functions, or MERGE to be implemented first.

Glad that the parent-fk-child method is of help. If you find anything missing for your use-case, just let us know. I'm sure there's more room for improvement in this area.

Cheers,

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.