2014-07-01 16:57 GMT+02:00 Garret Wilson <[email protected]>: > Hi, Lukas. I'm not sure you understood what I meant by "duplicate > information", >
I'm pretty sure I did: You're repeating "parent" (e.g. author) record data for each "child" (e.g. book) > and what I meant by "multiple queries... less efficient". > Hmm, what could you mean other than N+1 querying? :) Some explanations: http://stackoverflow.com/q/97197/521799 > I'm no SQL expert, so if I'm off base here let me know. But let me break > it down. > > Let's say I have a BookManager interface with method getBooks(). (Assume > also for the moment that authors are also objects, and that no book has the > same author---that more accurately describes my particular domain model.) > OK, although that shouldn't change too much for the issue at hand. > Conceptually that's straightforward: > > List<Book> books=new ArrayList<Book>(); > Cursor<BookRecord> bookCursor=dslContext.select().from(BOOK).fetchLazy; > while(bookCursor.hasNext()) { > BookRecord bookRecord=bookCursor.fetchOne() > Book book=new Book(bookRecord.getIsbn()); > book.setTitle(bookRecord.getTitle()); > ... > Cursor<AuthorRecord> > authorCursor=dslContext.select().from(AUTHOR).where(AUTHOR.BOOK).EQUALS(bookRecord.getKey()).fetchLazy(); > while(authorCursor.hasNext()) { > AuthorRecord authorRecord=authorCursor.fetchOne() > Author author=new Author(authorRecord.getURI()); > author.setFirstName(authorRecord.getFirstName()); > ... > book.add(author); > } > books.add(book); > } > > But look at all those queries. Wouldn't it be more efficient to do a join? > Yes, that's the N+1 problem right there, and that must be avoided. Possibly with a join. > Cursor<Record> > joinCursor=dslContext.select().from(AUTHOR).join(AUTHOR).onKey().fetchLazy(); > > But doesn't that mean that in the resulting "table" I'll have multiple > author rows for each book, with the book information repeated in each one? > I'll have to do something like this: > > Book book=null; > while(joinCursor.hasNext()) { > Record record=joinCursor.fetchOne(); > if(book==null || !book.getISBN().equals(record.getValue(AUTHOR.ISBN))) { > book=new Book(record.getValue(AUTHOR.ISBN) > book.setTitle(record.getValue(BOOK.TITLE) > ... > books.add(book); > } > Author author=new Author(record.getValue(AUTHOR.URI)); > author.setFirstName(authorRecord.getValue(AUTHOR.FIRST_NAME); > ... > book.add(author); > } > > And imagine if the instance hierarchy is three layers down---all that ugly > logic to see if we've moved to a new row for which we've changed the > current parent entity we're on for each level. Is there an easier way? > I think I gave you the answer in my previous E-Mail. You have these options from my point of view: 1. Use joins and implement that grouping yourself, but factor out common parts. Right now, you have a lot of redundant logic because you're explicitly tying stuff to the AUTHOR / BOOK relation. I'm sure there are elements that can be factored out generically. jOOQ provides you with foreign key meta information 2. Use joins and use jOOQ's existing Result.intoGroups() methods. It'll help doing the "grouping" (which you currently do manually, and iteratively) 3. Use several queries instead of joins, but don't fetch each author for each book. Fetch all books first, and then fetch *ALL* authors for *ALL* books Let me know if any of these concrete suggestions are unclear. 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
