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. On Monday, May 25, 2015 at 7:14:09 AM UTC-4, Lukas Eder wrote: > > > > 2015-05-21 21:21 GMT+02:00 Aram Mirzadeh <[email protected] > <javascript:>>: > >> 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? > > -- 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.
