@edvin Will KDBC help with grouping results from joins into objects (especially one-to-many relationships). I looked into this recently and even JOOQ doesn't provide a satisfactory solution. For example, If I join a Customer table with the Orders table I'd like to be able to easily transform the resulting rows into a list of Customer objects which each contain a list of Order objects. This turns out to require a lot of boilerplate in practice and I was wondering if KDBC will help with this.

@teedee Are you creating a cartesian product to get your data?

Its a hypothetical scenario but the scenario would just be the Customer table that gets joined with the Order table on the customer id so you end up with rows that contain a customer and an order but of course the customer is repeated each time due to each customer having multiple orders so you want to group those results in a single Customer object with a list of Order objects

I guess that's a cartesian product? It would depend on the type of join used I think

@edvin you mean N+1 selects ?

I think that's the name of the problem yes

@carlw No, a cartesian product is an alternative to N+1 selects

N+1 selects is the easiest to code; denormalization is the other way i know (join customer to order)

For this use case it would mean that you select columns from both customer and orders, so if you have two orders for a single customer, you'd get two rows back where the customer data is duplicated.

cartesian is all customers joined to all orders, right?

This is a technique that works well for small result sets, but could be detrimental to performance for large datasets

Hang on, putting Alyssa to bed

@teedee Maybe you're looking for something like iBatis? That's an older project (defunct?) that lets you map SQL to objects

@teedee I used it in a reporting project mixed in with Hibernate. The reports were defined in terms of SQL and it was requiring too much juggling to get Hibernate to implement

@carlw MyBatis is the new iBATIS

@teedee Show me the sql and I'll show you how to create customers and lists of orders from this query 🙂

Doing it with N+1 is completely trivial and super elegant. Using cartsian product is also super easy and not much work at all.

Ideally I'd be able to tell KDBC to group parts of multiple rows together so I can pass that list to a constructor. For example, I'd like to have a method that gets all the rows with a specific customer id (and takes a receiver so I can transform each of those rows to an Order object) and returns the list of Orders

That's trivial. But KDBC doesn't come up with JOINS for you, you have to do it yourself.

In that regards, it's just like iBATIS/myBATIS, just type safe and sexy 🙂

Yes I'd do the joins myself, that's no problem. I just didn't see an example that demonstrates the grouping of multiple rows so I thought I'd ask

basically if I was able to write this in the get() method I'd be happy

What does filter do here?

Filter the rows with that customer id

and pass them to the receiver

You can do almost exactly that 🙂

@edvin you mean by cartesian that you join every customer to every order? ex edvin + edvin's order, edvin + carl's orders, etc?

But normally you don't go around mentioning all the fields like that. The query has aliases for each table you use, and you'd normally have a function that takes an ORDER table and converts it into an Order domain object. I have constructors in my domain objects that accepts a table instance.

Also the get method shouldn't be executed for each row anymore in this case. I think KDBC executes the get method for each returned row at the moment?