fluttersqlitedartdart-pubflutter-moor

Flutter SQLite get value between 2 dates, one is dynamic ( flutter Moor )


I am building a finance app, created a table called wallets, the wallets table has a column called startDate. using ( flutter_moor ) package for SQLite

Now I need to only retrieve the latest created wallet and only if is in the range of 30 days since it been created using the value of the above field startDate, which means on the new month I can not get it

My Code:

Stream<Wallet> watchLatestCreatedWallet() {
  final Stream<Wallet> wallet = (
    select(wallets)
    ..orderBy([
      (wallet) => OrderingTerm(
        expression: wallet.startDate,
        mode: OrderingMode.desc
      ) 
    ])
    // somthing wrong I did, obviously always will return true
    // ..where((tbl) => tbl.startDate.isBetween(tbl.startDate, tbl.startDate + Duration(days: 30) ) )
    ..limit(1)
  )
  .watchSingle();

  return wallet;
}

Solution

  • Fixed this by

    Stream<Wallet> watchLatestCreatedWallet() {
      final endRange = DateTime.now();
      final startRange = endRange.subtract(Duration(days: 30));
      final Stream<Wallet> wallet = (
        select(wallets)
        ..orderBy([
          (wallet) => OrderingTerm(
            expression: wallet.startDate,
            mode: OrderingMode.desc
          ) 
        ])
        ..where((tbl) => tbl.startDate.isBetween(startRange, endRange ) )
        ..limit(1)
      )
      .watchSingle();
    
      return wallet;
    }