Sqldelight: SQLDelight + SQLBrite one-to-many relationship

[KChernenko]

Hi! I'm trying to understand how to implement one-to-many relationship using combination SQLDelight + SQLBrite.

I have two tables (SQLDelight): FlatsEntity and OwnersEntity. They are linked by the foreign key - one owner can have several flats (one-to-many).
And two models Flat and Owner (I'm using AutoValue-Parcel). Flat should contain owner's info (Flat's model contains Owner's model).

I understand how to insert data (gist link) from the models into the database. But I can't figure out how to map that data (gist link) back.

[AlecStrong]

Flat should contain owner's info (Flat's model contains Owner's model).

This is relational, which SQLDelight is not. When you query the DB for your data back the Owner and Flat objects will always be separate. I'd suggest having them contained in a separate object and mapping that way:

FlatWithOwner.sq:

select_all:
SELECT *
FROM flat
JOIN owner ON owner_id = flat._id;

FlatWithOwner.java:

@AutoValue
public abstract class FlatWithOwner implements FlatWithOwnerModel {
  public abstract Flat flat();
  public abstract Owner owner();

  public static FlatWithOwner map(Cursor cursor) {
    return new AutoValue_FlatWithOwner(Flat.MAPPER.map(cursor), Owner.MAPPER.map(cursor));
  }
}

public Observable<List<FlatWithOwner>> getFlats() {
  return database.createQuery(
      Arrays.asList(Flat.TABLE_NAME, Owner.TABLE_NAME), 
      FlatWithOwner.SELECT_ALL
    )
    .mapToList(new Func1<Cursor, FlatWithOwner>() {
      public FlatWithOwner call(Cursor cursor) {
        return FlatWithOwner.map(cursor);
      }
    });
}

In the future this will be done for you as mappers will be done on a per-query basis #36

[KChernenko]

Thanks for the answer!

[pkozlovskiy]

@AlecStrong sorry, but what about many-to-many relationship

Can I get model of Documetn with List with View or in another way?

[AlecStrong]

You can store a list on the actual table like this:

CREATE TABLE test_table (
  list_column BLOB AS List<Object>
);

if you want to model a many-to-many relationship you need to do it using joins: https://github.com/square/sqldelight#join-projections

All SQLDelight does is give you a POJO for each row of a query, if you want to do more complicated relational logic like mapping one table to a list of some other table than you will need to do that yourself in java.