Returning tuples for 1:n relation by making two outer joins

[schaetz]

I have two SQL tables artists and tags with a 1:n relation, that means each artist can have a set of tags from 0 to infinite. I have a third table assigned_tags that models that relation, it just contains pairs of artist IDs and tag IDs.

Now I want to query all artists with their sets of tags in a single query. For that, I implemented a StreamTransformer that transforms a stream of artist/tag tuples to a stream of a list of artist data objects (without duplicate artists).

So in Drift, I need to join assigned_tags and tags so that the result contains tuples of artists and tags while making sure that artists without tags are still considered. I tried it like this:

final query = select(artists).join([
  leftOuterJoin(assignedTags, assignedTags.artist.equalsExp(artists.id)),
  innerJoin(tags, assignedTags.tag.equalsExp(tags.id)),
])
  ..orderBy([OrderingTerm.asc(artists.name)]);
final typedResultStream = query.watch();

However, with the innerJoin of tags I only get the artists that have at least one tag. So I tried two outer joins:

.join([
  leftOuterJoin(assignedTags, assignedTags.artist.equalsExp(artists.id)),
  leftOuterJoin(tags, assignedTags.tag.equalsExp(tags.id)),
])

But that will cause the following exception:

[VERBOSE-2:dart_vm_initializer.cc(41)] Unhandled Exception: Invalid argument(s): Invalid table passed to readTable: tags. This row does not contain values for that table.
Please use readTableOrNull for outer joins.

I have neither found any documentation on readTableOrNull, nor do I see how I could use it here.

Is what I am trying to do even feasible with joins only, or will I have to make a nested query? Obviously, I care about performance, because this will be the main query for listing all artists.

[simolus3]

The problem is likely in what follows after final typedResultStream, in particular in how you read tables.

TypedResult.readTable is declared to return a non-nullable row instance, which doesn't work for outer joins. So you'd have to replace .readTable with .readTableOrNull there.

[darkomenx]

@schaetz can you share your Table class Drift dealing with this ManyToOne relation please ?
I'm little confused about this relation.