subquery issue

[heshaShawky]

Describe the bug

The code below should return the top outcome categories based on the transactions total for each one ( category )

I get all 0 ( total ) if I uncomment the line on the subquery below which is not 0!

So now I am getting the total for all not based on category so the results currently cat1 - 100, cat2 - 100, cat 3 - 100 ...etc

Stream<List<Map<double, CategoryData>>> watchTopOutcomeCategories() async* {
    final categories = alias(categoriesTable, 'categories');
    final transactions = alias(transactionsTable, 'transactions');
    final settings = alias(settingsTable, 'settings');

    final query = select(categories).join([
      innerJoin(settings, settings.id.equals(1)),
    ]);

    final total = transactions.details.jsonExtract<double>(r'$.value').total();

    final subquery = subqueryExpression<double>(
      selectOnly(transactions)
        ..addColumns([total])
        ..where(transactions.walletId.equalsExp(settings.walletId))
        // ..where(transactions.categoryId.equalsExp(categories.id)),
    );

    query
      ..addColumns([subquery])
      ..limit(10)
      ..where(
          categories.details.jsonExtract<String>(r'$.type').equals('outcome'))
      ..orderBy([OrderingTerm(expression: subquery)]);

    final data = query.watch().map(
          (results) => [
            for (final res in results)
              {
                res.read(subquery)!: res.readTable(categories),
              }
          ],
        );

    yield* data;
  }

I want to do something like this using drift

SELECT *, ( 
  SELECt total(json_extract(details, '$.value')) 
  FROM transactions t
  WHERE t.wallet_id = 1
  ANd t.category_id = c.id

) as total 
From categories c
INNER join settings on settings.id = 1;

[simolus3]

The query with the uncommented filter looks correct to me. I couldn't reproduce the issue like this:

Code printing correct results

```dart import 'dart:convert'; import 'package:drift/drift.dart'; import 'package:drift/extensions/json1.dart'; import 'package:drift/native.dart'; part 'repro.g.dart'; class Settings extends Table { late final IntColumn id = integer().autoIncrement()(); late final IntColumn walletId = integer()(); } class Categories extends Table { late final IntColumn id = integer().autoIncrement()(); late final TextColumn name = text()(); } class Transactions extends Table { late final IntColumn id = integer().autoIncrement()(); late final IntColumn category = integer().references(Categories, #id)(); late final TextColumn details = text()(); late final IntColumn walletId = integer()(); } @DriftDatabase(tables: [Settings, Categories, Transactions]) class Database extends _$Database { Database(super.e); @override int get schemaVersion => 1; Stream>> watchTopOutcomeCategories() async* { final categories = alias(this.categories, 'categories'); final transactions = alias(this.transactions, 'transactions'); final settings = alias(this.settings, 'settings'); final query = select(categories).join([ innerJoin(settings, settings.id.equals(1)), ]); final total = transactions.details.jsonExtract(r'$.value').total(); final subquery = subqueryExpression(selectOnly(transactions) ..addColumns([total]) ..where(transactions.walletId.equalsExp(settings.walletId)) ..where(transactions.category.equalsExp(categories.id)) // ..where(transactions.categoryId.equalsExp(categories.id)), ); query ..addColumns([subquery]) ..limit(10) ..orderBy([OrderingTerm(expression: subquery)]); final data = query.watch().map( (results) => [ for (final res in results) { res.read(subquery)!: res.readTable(categories), } ], ); yield* data; } } void main() async { final db = Database(NativeDatabase.memory(logStatements: true)); await db.settings.insertOne(SettingsCompanion.insert(walletId: 1)); await db.categories.insertOne(CategoriesCompanion.insert(name: 'first')); await db.categories.insertOne(CategoriesCompanion.insert(name: 'second')); await db.transactions.insertAll([ TransactionsCompanion.insert( category: 1, details: json.encode({'value': 10}), walletId: 1, ), TransactionsCompanion.insert( category: 1, details: json.encode({'value': 5}), walletId: 1, ), TransactionsCompanion.insert( category: 2, details: json.encode({'value': 313}), walletId: 1, ), ]); print(await db.watchTopOutcomeCategories().first); } ```

Could you check the involved tables and data and try to come up with a minimal example based on the code I've posted where the query doesn't return the correct information?

[heshaShawky]

Thanks for the reply. It's my bad. It did not save the category ID ( Null ) because of something I did previously and didn't notice.