How does intersect in Drift for Flutter work?

iam a working on a flutter project using dirft for the database. I have a song and a tag table and a songTag table that consists of |songId|tagId| to manage the m to n reltionship between songs and tags.

This function should return the songs, that are tagged with "Acoustic" and "Melancholy" by getting the songs of each tag and using the intersection. But it does not work me.

  Future<List<Song>> complicatedQuery2([bool benchmark = false]) async {
    Stopwatch sw1 = Stopwatch()..start();
    // Query to get songs tagged with tag Acoustic
    final query1 = database.select(database.songs).join([
      innerJoin(
        database.songTags,
        database.songTags.songId.equalsExp(database.songs.id),
      ),
      innerJoin(
        database.tags,
        database.tags.id.equalsExp(database.songTags.tagId),
      ),
    ])..where(database.tags.title.equals("Acoustic"));

    // Query to get songs tagged with tag Melancholy
    final query2 = database.select(database.songs).join([
      innerJoin(
        database.songTags,
        database.songTags.songId.equalsExp(database.songs.id),
      ),
      innerJoin(
        database.tags,
        database.tags.id.equalsExp(database.songTags.tagId),
      ),
    ])..where(database.tags.title.equals("Melancholy"));

    // Intersect the two queries
    query1.intersect(query2);
    final result =
        await query1.map((row) => row.readTable(database.songs)).get();

    print(
      "Fetched ${result.length} results in ${sw1.elapsed}. ", //#First ten results:  ${result.sublist(0, (result.length > 10) ? 10 : result.length)}",
    );

    return result;
  }

I verified that I have songs, that are tagged with both tags and query1 and query2 both work individualy and both their results contain the song tagged with both. But the intersection returns an empty result. Why?

Example autput of individual query:

[Song(id: 260001, title: ChanceFearForeverMemories, uri: /storage/emulated/0/music/), Song(id: 260091, title: FadingTwoStreetOne, uri: /storage/emulated/0/music/), Song(id: 260151, title: WindTouchSecretsAcoustic, uri: /storage/emulated/0/music/), ... ]

In the documentation it also says that the statement to intersect with, is not allowed to contain compound parts on its own. But I could not find out, what makes something a compound part.

Solution

You're getting an empty result because Drift doesn’t support .intersect() when the queries involve JOINs, and both of your queries use joins. That’s why, even though each individual query works, the intersection fails.

A better approach is to perform the intersection in Dart code. Here's what I did:

I fetched the list of song IDs for each tag separately.
Then I used Dart’s Set.intersection() method to find the common IDs.
Finally, I queried the songs table using those intersected IDs.

Here’s a sample function:

Future<List<Song>> getSongsWithTags(List<String> tags) async {
  final sets = await Future.wait(tags.map((tag) async {
    final query = database.select(database.songTags).join([
      innerJoin(database.tags, database.tags.id.equalsExp(database.songTags.tagId)),
    ])..where(database.tags.title.equals(tag));

    final rows = await query.get();
    return rows.map((row) => row.readTable(database.songTags).songId).toSet();
  }));

  final commonSongIds = sets.reduce((a, b) => a.intersection(b));
  if (commonSongIds.isEmpty) return [];

  return await (database.select(database.songs)
    ..where((song) => song.id.isIn(commonSongIds.toList()))).get();
}