How to use PARTITION and JOIN together

[hgraceb]

I want to first query the data from the download_entries table and partition it based on the source field, then perform an inner join with the chapter_entries table. This is my current code, which looks very bulky and difficult to maintain. Does anyone know how I can optimize it?

return database.customSelect(
  'SELECT'
  ' "d"."source" as "d.source", "d"."manga" as "d.manga", "d"."chapter" as "d.chapter", "d"."urls" as "d.urls", "d"."status" as "d.status", "d"."progress" as "d.progress", "d"."error" as "d.error",'
  ' "c"."id" as "c.id", "c"."manga" as "c.manga", "c"."index" as "c.index", "c"."title" as "c.title", "c"."url" as "c.url", "c"."date" as "c.date", "c"."is_public" as "c.is_public", "c"."is_read" as "c.is_read", "c"."progress" as "c.progress"'
  ' FROM (SELECT *'
  '     FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "source") AS "row_number"'
  '           FROM download_entries) AS partitioned'
  '     WHERE "partitioned"."row_number" = 1'
  '     LIMIT ?'
  ' ) AS d'
  ' INNER JOIN chapter_entries "c" ON "c"."id" = "d"."chapter";',
  variables: [Variable.withInt(limit)],
  readsFrom: {database.downloadEntries, database.chapterEntries},
).map((row) {
  return DownloadWithChapter(
    download: database.downloadEntries.map(row.data, tablePrefix: 'd'),
    chapter: database.chapterEntries.map(row.data, tablePrefix: 'c'),
  );
}).get();

[simolus3]

At the moment, there is no good way to write this in the Dart DSL - #612 is the issue to track. It's been stale for some time, but I have an idea on how it could be implemented and I'll give it a shot later this week.

This would require a bit more setup, but you could also import your existing Dart tables into a Drift file, include that on your database and then write the query like this:

import 'your_tables.dart';

downloadWithChapters: SELECT d.**, c.**
 FROM (SELECT *
     FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "source") AS "row_number"
           FROM download_entries) AS partitioned
     WHERE "partitioned"."row_number" = 1
     LIMIT ?
 ) AS d
 INNER JOIN chapter_entries "c" ON "c"."id" = "d"."chapter";

Drift will expand the .** syntax into something understood by table.map at compile time.

[hgraceb]

Thank you very much. However, I tried it myself and found that ** can only recognize the outermost table, but cannot find the table used as the basis for PARTITION in the generated code. To help you better reproduce the issue, I have added similar test code in the examples/app/lib/database/sql.drift file under the develop branch.

The test code is as follows:

-- .drift files can contain SQL that is analyzed at compile time.
-- First, let's import the Dart tables so that we can reference them here.
import 'tables.dart';

-- Create a text index of todo entries, see https://www.sqlite.org/fts5.html#external_content_tables

CREATE VIRTUAL TABLE text_entries USING fts5 (
    description,
    content=todo_entries,
    content_rowid=id
);

-- Triggers to keep todo entries and fts5 index in sync.
CREATE TRIGGER todos_insert AFTER INSERT ON todo_entries BEGIN
  INSERT INTO text_entries(rowid, description) VALUES (new.id, new.description);
END;

CREATE TRIGGER todos_delete AFTER DELETE ON todo_entries BEGIN
  INSERT INTO text_entries(text_entries, rowid, description) VALUES ('delete', old.id, old.description);
END;

CREATE TRIGGER todos_update AFTER UPDATE ON todo_entries BEGIN
  INSERT INTO text_entries(text_entries, rowid, description) VALUES ('delete', new.id, new.description);
  INSERT INTO text_entries(rowid, description) VALUES (new.id, new.description);
END;

-- Queries can also be defined here, they're then added as methods to the database.
_categoriesWithCount: SELECT c.*,
  (SELECT COUNT(*) FROM todo_entries WHERE category = c.id) AS amount
  FROM categories c
UNION ALL
  SELECT null, null, null, (SELECT COUNT(*) FROM todo_entries WHERE category IS NULL);

-- The `.**` syntax instructs drift to generate nested result set classes.
_search: SELECT todos.**, cat.** FROM text_entries
    INNER JOIN todo_entries todos ON todos.id = text_entries.rowid
    LEFT OUTER JOIN categories cat ON cat.id = todos.category
    WHERE text_entries MATCH :query
    ORDER BY rank;

downloadWithChapters: SELECT "d".**, "c".**
 FROM (SELECT *
     FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "id") AS "row_number"
           FROM todo_entries) AS partitioned
     WHERE "partitioned"."row_number" = 1
     LIMIT 5
 ) AS d
 INNER JOIN categories "c" ON "c"."id" = "d"."category";

The error message is as follows:

[WARNING] drift_dev on lib/database/database.dart:
line 41, column 30: Nested star columns must refer to a table directly. They can't refer to a table-valued function or another select statement.
   ╷
41 │ downloadWithChapters: SELECT "d".**, "c".**
   │                              ^^^^^^
   ╵

The generated code is as follows:

Selectable<DownloadWithChaptersResult> downloadWithChapters() {
  return customSelect(
      'SELECT d.**,"c"."id" AS "nested_0.id", "c"."name" AS "nested_0.name", "c"."color" AS "nested_0.color" FROM (SELECT * FROM (SELECT *, ROW_NUMBER()OVER (PARTITION BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) AS row_number FROM todo_entries) AS partitioned WHERE partitioned.row_number = 1 LIMIT 5) AS d INNER JOIN categories AS c ON c.id = d.category',
      variables: [],
      readsFrom: {
        todoEntries,
        categories,
      }).asyncMap((QueryRow row) async {
    return DownloadWithChaptersResult(
      c: await categories.mapFromRow(row, tablePrefix: 'nested_0'),
    );
  });
}

class DownloadWithChaptersResult {
  final Category c;
  DownloadWithChaptersResult({
    required this.c,
  });
}

Did I do something wrong? How can I retrieve data from two tables instead of one table in the result?

[simolus3]

Thanks for the repro. I forgot that we can't find the table with ** if it comes from nested queries (and probably due to row_number being selected as well).

This would improve in #2233, which I've started working on. Your query will make a good test case to ensure it works then :D In the meantime, I think the only way is to replace ** with a regular star * and manually map the data from DownloadWithChaptersResult into the one you need.

[hgraceb]

@simolus3 Hi. When I refactor my query using the table.** syntax today. I found that I couldn't easily convert table data from query results because there is no QueryRow I can read. The following are my current codes. Any ideas?

downloadsPartitionBySource: SELECT download.**, chapter.**, manga.**
    FROM (SELECT *
          FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "source") AS "row_number"
                FROM download_entries
                WHERE "error" IS NULL
          ) AS partitioned
          WHERE partitioned."row_number" = 1
          LIMIT ?
    ) AS download
    INNER JOIN chapter_entries chapter ON chapter."id" = download."chapter"
    INNER JOIN manga_entries manga ON manga."id" = download."manga";

extension DownloadsPartitionBySourceNestedColumn0Extension on DownloadsPartitionBySourceNestedColumn0 {
  DownloadEntry toDownloadEntry() {
    return DownloadEntry(source: source!, manga: manga!, chapter: chapter!, total: total!, progress: progress!, error: error);
  }
}

Future<List<DownloadWithExtra>> queryDownloadsPartitionBySource(int limit) {
  return database
      .downloadsPartitionBySource(limit)
      .map((row) => DownloadWithExtra(download: row.download.toDownloadEntry(), chapter: row.chapter, manga: row.manga))
      .get();
}

class DownloadsPartitionBySourceNestedColumn0 {
  final int? source;
  final int? manga;
  final int? chapter;
  final int? total;
  final int? progress;
  final String? error;
  final int? rowNumber;

  DownloadsPartitionBySourceNestedColumn0({this.source, this.manga, this.chapter, this.total, this.progress, this.error, this.rowNumber});
}

Selectable<DownloadsPartitionBySourceResult> downloadsPartitionBySource(int var1) {
  return customSelect('SELECT ......', variables: [Variable<int>(var1)], readsFrom: {downloadEntries, chapterEntries, mangaEntries})
      .asyncMap((QueryRow row) async => DownloadsPartitionBySourceResult(
            download: DownloadsPartitionBySourceNestedColumn0(
              source: row.readNullable<int>('nested_0.source'),
              manga: row.readNullable<int>('nested_0.manga'),
              chapter: row.readNullable<int>('nested_0.chapter'),
              total: row.readNullable<int>('nested_0.total'),
              progress: row.readNullable<int>('nested_0.progress'),
              error: row.readNullable<String>('nested_0.error'),
              rowNumber: row.readNullable<int>('nested_0.row_number'),
            ),
            chapter: await chapterEntries.mapFromRow(row, tablePrefix: 'nested_1'),
            manga: await mangaEntries.mapFromRow(row, tablePrefix: 'nested_2'),
          ));