search

memtrip / SQLKing

SQLKing is an Android SQLite ORM powered by an annotation preprocessor, tables are defined by Table annotations and CRUD classes expose an expressive api for executing SQLite queries. @memtrip
Other
21 stars 9 forks source link

Recursive joins and lists - possible? #15

Open Trellian opened 8 years ago

Trellian commented 8 years ago

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 commented 8 years ago

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