2015-05-21 21:21 GMT+02:00 Aram Mirzadeh <[email protected]>: > 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.
