Dealing w/ Sqlite Join results in a cursor

Question

I have a one-many relationship in my local Sqlite db. Pretty basic stuff. When I do my left outer join I get back results that look like this:

the resulting cursor has multiple rows that look like this:

A1.id | A1.column1 | A1.column2 | B1.a_id_fk | B1.column1 | B1.column2 

A1.id | A1.column1 | A1.column2 | B2.a_id_fk | B2.column1 | B2.column2 

and so on...

Is there a standard practice or method of dealing with results like this ? Clearly there is only A1, but it has many B-n relationships. I am coming close to using multiple queries instead of the "relational db way". Hopefully I am just not aware of the better way to do things.

I intend to expose this query via a content provider and I would hate for all of the consumers to have to write the same aggregation logic.

Answer 1

Joins have worked this way in every SQL database I've ever used -- it's kinda the definition of the concept.

Bear in mind that content providers involve remote procedure calls, which are expensive. That's one of the reason why Android denormalizes results along the same lines as your join for things like contacts, to minimize the round-trips between processes.

You could consider using a remote service instead of a content provider, and exposing a custom API. You could return the B's for a given A by a simple List<>. Or, you could serialize the whole thing in JSON format. Or any number of other possibilities, if the data duplication disturbs you.