DELETE with LIMIT and ORDER BY - possible with Drift?

[tomekit]

I've got a table where I keep log entries. I try to keep table under certain amount of rows (e.g. 50,000).
If table exceeds that value, e.g. count() returns 51,233, I would like to remove 1233 entries ordered by DateTimeColumn.

Documentation demonstrates simple delete: https://drift.simonbinder.eu/dart_api/writes/#updates-and-deletes

(delete(todos)..where((t) => t.id.isSmallerThanValue(10))).go();

but that's just a delete with where condition.

SQLite supports DELETE with LIMIT and ORDER BY: https://www.sqlite.org/lang_delete.html#optional_limit_and_order_by_clauses

Is it possible to achieve similar in Drift?

[simolus3]

SQLite supports DELETE with LIMIT and ORDER BY

Only with a compile-time option that is not commonly enabled (and also not enabled by sqlite3_flutter_libs).

But you can select the oldest todo items (and apply a limit there) and then use a subquery as a condition to only delete those:

Future<void> deleteOld(int itemsToDelete) async {
  final oldItems = selectOnly(todoItems)
    ..addColumns([todoItems.id])
    ..orderBy([OrderingTerm.asc(todoItems.creationDate)])
    ..limit(itemsToDelete);

  final stmt = todoItems.delete()..where((t) => t.id.isInQuery(oldItems));
  await stmt.go();
}

[tomekit]

Only with a compile-time option that is not commonly enabled (and also not enabled by sqlite3_flutter_libs).

Thanks, I wasn't aware that's not a default for SQLite.

Thanks for the: deleteOld solution.

My other solution I've came up with is along the lines:

Future<void> deleteOld(int maxAllowedLogCount) async {
    final logsCount = await _db.managers.logs.count();
    if (logsCount > maxAllowedLogCount) {
      
      final maxIdExpr = _db.logs.id.max();
      final query = _db.selectOnly(_db.logs)
        ..addColumns([maxIdExpr]);

      final maxId = await query.map((row) => row.read(maxIdExpr)).getSingle();
      final idToDelete = maxId! - maxAllowedLogCount;
      await (_db.delete(_db.logs)..where((t) => t.id.isSmallerThanValue(idToDelete))).go();
    }
  }

It abuses auto-increment property on the ID, gets the max(ID) and works out which IDs to delete using range query.
I wouldn't use this approach for anything important, but for logs it's probably fine even if there is some "rounding error" (technically shouldn't be as logs likely wouldn't have gaps in the IDs).