You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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_vm_initializer.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
The text was updated successfully, but these errors were encountered:
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:
I then tried to make a databaseView:
E/flutter ( 4072): [ERROR:flutter/runtime/dart_vm_initializer.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)
The text was updated successfully, but these errors were encountered: