trying to get a SUM of a field with floor.

[ferut]

I am tyring to get SUM of field, the problem is that if my DB doesn't have a field populated with Entrata or Uscita (value on is based logic of SUM) the SUM return null. I have tried to impelement COALESCE to get a 0 if SUM is null, but 0 is viewed as int.

I tried something like this:

[@DatabaseView('SELECT EventN.id, EventN.date, EventN.description AS eventDescription, EventN.category, EventN.subCategory, EventN.mdp, EventN.amount, EventN.type, Liquidity.description AS liquidityDescription '
    'FROM EventN LEFT OUTER JOIN Liquidity ON EventN.mdp = Liquidity.id')
class EventJoinLiquidity {
  final int? id;
  final DateTime date;
  final String eventDescription; // Alias for EventN.description
  final String category;
  final String subCategory;
  final int mdp;
  final double? amount;
  final String type;
  final String? liquidityDescription; // Alias for Liquidity.description

  EventJoinLiquidity({
    this.id,
    required this.date,
    required this.eventDescription,
    required this.category,
    required this.subCategory,
    required this.mdp,
    required this.amount,
    required this.type,
    this.liquidityDescription,
  });

}

@dao
abstract class EventJoinLiquidityDao {

  @Query('SELECT * FROM EventJoinLiquidity ORDER BY Date DESC')
  Future<List<EventJoinLiquidity>> getAllEventsJoinLiquidityAll();

  @Query('SELECT * FROM EventJoinLiquidity ORDER BY Date DESC')
  Stream<List<EventJoinLiquidity>> getAllEventsJoinLiquidityAllStream();

  @Query('SELECT * FROM EventJoinLiquidity WHERE strftime("%Y", Date) = strftime("%Y", "now") ORDER BY Date DESC')
  Stream<List<EventJoinLiquidity>> getAllEventsJoinLiquidityYear();

  @Query('SELECT * FROM EventJoinLiquidity WHERE strftime("%Y-%m", Date) = strftime("%Y-%m", CURRENT_TIMESTAMP) order by Date DESC')
  Stream<List<EventJoinLiquidity>> getAllEventsJoinLiquidityForCurrentMonth();

  @Query('SELECT * FROM EventJoinLiquidity WHERE strftime("%Y-%W", Date) = strftime("%Y-%W", CURRENT_TIMESTAMP) order by Date DESC')
  Stream<List<EventJoinLiquidity>> getAllEventsJoinLiquidityForCurrentWeek();

  @Query('SELECT * FROM EventJoinLiquidity WHERE strftime("%Y-%m-%d", Date) = strftime("%Y-%m-%d", :selectedDay) order by Date DESC')
  Stream<List<EventJoinLiquidity>> getAllEventsJoinLiquidityForSelectedDay(DateTime selectedDay);

  @Query('SELECT * FROM EventJoinLiquidity WHERE strftime("%Y-%m", date) = strftime("%Y-%m", :selectedMonth) ORDER BY date DESC')
  Stream<List<EventJoinLiquidity>> getAllEventsJoinLiquidityForSelectedMonth(DateTime selectedMonth);

  @Query('SELECT COALESCE(SUM(amount), CAST(0.0 AS DOUBLE)) AS totalEntrata '
      'FROM EventJoinLiquidity '
      'WHERE type = "Entrata"')
  Stream<double?> getTotalEntrataForAll() ;

  @Query('SELECT SUM(amount) AS totalUscita '
      'FROM EventJoinLiquidity '
      'WHERE type = "Uscita"')
  Stream<double?> getTotalUscitaForAll();

  @Query('SELECT COALESCE(SUM(amount), CAST(0.0 AS DOUBLE)) AS totalEntrata '
      'FROM EventJoinLiquidity '
      'WHERE type = "Entrata" AND strftime("%Y", Date) = strftime("%Y", "now")')
  Stream<double?> getTotalEntrataForYear();

  @Query('SELECT COALESCE(SUM(amount), CAST(0.0 AS DOUBLE)) AS totalEntrata '
      'FROM EventJoinLiquidity '
      'WHERE type = "Entrata" AND strftime("%Y-%m", Date) = strftime("%Y-%m", CURRENT_TIMESTAMP)')
  Stream<double?> getTotalEntrataForMonth();

  @Query('SELECT COALESCE(SUM(amount), CAST(0.0 AS DOUBLE)) AS totalEntrata '
      'FROM EventJoinLiquidity '
      'WHERE type = "Entrata" AND strftime("%Y-%W", Date) = strftime("%Y-%W", CURRENT_TIMESTAMP)')
  Stream<double?> getTotalEntrataForWeek();

  @Query('SELECT COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), 0) AS netAmount '
      'FROM EventJoinLiquidity ')
  Stream<double?> getNetAmountForAll();

  @Query('SELECT COALESCE(SUM(amount), CAST(0.0 AS DOUBLE)) AS totalUscita '
      'FROM EventJoinLiquidity '
      'WHERE type = "Uscita" AND strftime("%Y", Date) = strftime("%Y", "now")')
  Stream<double?> getTotalUscitaForYear();

  @Query('SELECT COALESCE(SUM(amount), CAST(0.0 AS DOUBLE)) AS totalUscita '
      'FROM EventJoinLiquidity '
      'WHERE type = "Uscita" AND strftime("%Y-%m", Date) = strftime("%Y-%m", CURRENT_TIMESTAMP)')
  Stream<double?> getTotalUscitaForMonth();

  @Query('SELECT COALESCE(SUM(amount), CAST(0.0 AS DOUBLE)) AS totalUscita '
      'FROM EventJoinLiquidity '
      'WHERE type = "Uscita" AND strftime("%Y-%W", Date) = strftime("%Y-%W", CURRENT_TIMESTAMP)')
  Stream<double?> getTotalUscitaForWeek();

  @Query('SELECT COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), 0) AS netAmount '
      'FROM EventJoinLiquidity '
      'WHERE strftime("%Y", Date) = strftime("%Y", "now")')
  Stream<double?> getNetAmountForYear();

  @Query('SELECT COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), CAST(0.0 AS DOUBLE)) AS netAmount '
      'FROM EventJoinLiquidity '
      'WHERE strftime("%Y-%m", Date) = strftime("%Y-%m", CURRENT_TIMESTAMP)')
  Stream<double?> getNetAmountForMonth();

  @Query('SELECT COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), CAST(0.0 AS DOUBLE)) AS netAmount '
      'FROM EventJoinLiquidity '
      'WHERE strftime("%Y-%W", Date) = strftime("%Y-%W", CURRENT_TIMESTAMP)')
  Stream<double?> getNetAmountForWeek();

I then tried to make a databaseView:

@DatabaseView('SELECT '
    'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE 0 END), 0) AS entrata, '
    'COALESCE(SUM(CASE WHEN type = "Uscita" THEN amount ELSE 0 END), 0) AS uscita, '
    'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), 0) AS netAmount, '
    'date'
    'FROM EventJoinLiquidity '
    'GROUP BY date')
class PeriodSummary {
  final double entrata;
  final double uscita;
  final double netAmount;

  PeriodSummary({
    required this.entrata,
    required this.uscita,
    required this.netAmount,
  });}

  @dao
  abstract class PeriodSummaryDao {

  @Query('SELECT '
      'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE 0 END), 0) AS entrata, '
      'COALESCE(SUM(CASE WHEN type = "Uscita" THEN amount ELSE 0 END), 0) AS uscita, '
      'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), 0) AS netAmount '
      'FROM EventJoinLiquidity '
      'WHERE strftime("%Y-%m", Date) = strftime("%Y-%m", CURRENT_TIMESTAMP)')
  Stream<PeriodSummary?>? getMonthSummary();

  @Query('SELECT '
      'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE 0 END), 0) AS entrata, '
      'COALESCE(SUM(CASE WHEN type = "Uscita" THEN amount ELSE 0 END), 0) AS uscita, '
      'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), 0) AS netAmount '
      'FROM EventJoinLiquidity '
      'WHERE strftime("%Y-%W", Date) = strftime("%Y-%W", CURRENT_TIMESTAMP)')
  Stream<PeriodSummary?>? getWeekSummary();

  @Query('SELECT '
      'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE 0 END), 0) AS entrata, '
      'COALESCE(SUM(CASE WHEN type = "Uscita" THEN amount ELSE 0 END), 0) AS uscita, '
      'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), 0) AS netAmount '
      'FROM EventJoinLiquidity '
      'WHERE strftime("%Y", Date) = strftime("%Y", "now")')
  Stream<PeriodSummary?>? getYearSummary();

  @Query('SELECT '
      'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE 0 END), 0) AS entrata, '
      'COALESCE(SUM(CASE WHEN type = "Uscita" THEN amount ELSE 0 END), 0) AS uscita, '
      'COALESCE(SUM(CASE WHEN type = "Entrata" THEN amount ELSE -amount END), 0) AS netAmount '
      'FROM EventJoinLiquidity')
  Stream<PeriodSummary?>? getAllSummary();

}

But in both of my try I have an issue: if there is noUscita, COALESCE SQL function correctly return 0 but 0 is read as int and I get this error:

E/flutter ( 4072): [ERROR:flutter/runtime/dart_vminitializer.cc(41)] Unhandled Exception: type 'int' is not a subtype of type 'double' in type cast E/flutter ( 4072): #0 $PeriodSummaryDao.getYearSummary. (package:expense_tracker/model/database/database.g.dart:689:35) E/flutter ( 4072): #1 QueryAdapter.query (package:floor/src/adapter/query_adapter.dart:36:18) E/flutter ( 4072): E/flutter ( 4072): #2 QueryAdapter.queryStream.executeQueryAndNotifyController (package:floor/src/adapter/query_adapter.dart:78:22)

What can I do without modifying the database.g generate file

[ferut]

Any update?