Recursive joins and lists - possible?

[Trellian]

Hi Sam,

I've tried to model a 1-Many relationship, where I query a parent table joined to a child table, and expect to get a list or array of the child table returned as an object in the parent table record, but SQLKing does not support that yet.

Similar to that, where I have a self-referential table implementing a recursive hierarchy (which obviously also needs to return a list or array of it's own table type as a child, in the parent object.

Any chance of adding that soon?

Thanks,
Adrian

[samkirton]

Hi Adrian,

Could you review the inner join test at:
https://github.com/memtrip/SQLKing/blob/master/client/src/tests/java/com/memtrip/sqlking/integration/JoinTest.java#L57

The example performs an INNER JOIN on User and Log, in the case of a one to many relationship the log rows are mapped as part of the parent object. For example:

User[] users = Select .getBuilder()
    .join(innerJoin(Log.class, on("User.logId", "Log.id")))
    .execute(User.class, getSQLProvider());

If the query matches the one User with many logs, lets say 4, the users array would have 4 items, the user data would be duplicated, but the child Log objects would be mapped to the INNER JOIN results.
assertTrue(users[0].getLog().getId() == 1)
assertTrue(users[1].getLog().getId() == 2)
assertTrue(users[2].getLog().getId() == 3)
assertTrue(users[3].getLog().getId() == 4)

In my opinion this structure is true to what SQLite actually returns in the case of a join.

Can you give a SQL example for your self-referential table requirement?

Cheers,
Sam