Return null on scalar queries such as "Select count(*) from ..."

[wzhou-grid]

The following query always returns null:

@Query('SELECT COUNT(*) from Area')
  Future<int?> total();

I have tested other scalar queries, such as

@Query('SELECT MAX(Id) from Area')
  Future<int?> getMax();

still got null. very strange.

[evandrmb]

I have the same problem. The following query

@Query('SELECT MAX(createdAt), MAX(updatedAt) from people')
  Future<List<DateTime>> getOldestDates();

generates following code in database.g.dart

 await _queryAdapter
      .queryNoReturn('SELECT MAX(createdAt), MAX(updatedAt) from people');

which has a return type of void

[Sanshain]

What about removing ? from Future generic type?

@Query('SELECT COUNT(*) from Area')
  Future<int> total();

I suppose COUNT(*) always returns number and never returns null

[evandrmb]

What about removing ? from Future generic type?

@Query('SELECT COUNT(*) from Area')
  Future<int> total();

I suppose COUNT(*) always returns number and never returns null

He can't. If you do that and try to run flutter pub run build_runner build it will fail with the following warning:

Queries returning single elements might return null. Make the method return a Future of a nullable type e.g. Future<int?>`

[ayalma]

What about max (id) when db is empty?

[MrCsabaToth]

This is badly needed feature. I have several tables I'd like to know if they are empty or not. My workaround is to have raw queries in the AppDatabase class, something along the line of:

  Future<int> areaCount() async {
    final result = await database.rawQuery("SELECT COUNT(`id`) AS cnt FROM Area");

    return result.isEmpty ? 0 : result[0]['cnt'] as int? ?? 0;
  }

But this is sooo ugly and might not be robust! I think count is common enough to be supported along with CRUD.

[MrCsabaToth]

This feature will help #200

[MrCsabaToth]

What about max (id) when db is empty?

No matter how you spin it around the code generator portion needs to recognize and support what you want to do. And that's what is missing right now.

[Nathagamelle]

stranger still
with DAO
@query('SELECT COUNT(*) FROM MyItem')
Future<int?> getAllCount();
@query('SELECT * FROM MyItem')
Future<List> findAllItems();

int? count=await database.myItemDAO.getAllCount();
print('db-size' + count.toString());
==> returns null

List myItems= await database.myItemDAO.findAllItems();
print('db-size' + myItems.length.toString());
==> returns the actual count

[MrCsabaToth]

stranger still with DAO @query('SELECT COUNT(*) FROM MyItem') Future<int?> getAllCount(); @query('SELECT * FROM MyItem') Future findAllItems();

int? count=await database.myItemDAO.getAllCount(); print('db-size' + count.toString()); ==> returns null

List myItems= await database.myItemDAO.findAllItems(); print('db-size' + myItems.length.toString()); ==> returns the actual count

That length on the findAllItems result is very resource intensive especially with a larger table. First I would say just for this purpose you may want @query('SELECT id FROM MyItem') instead of @query('SELECT * FROM MyItem') just to decrease the bandwidth. But then you'd rather want to do what I advised and place a dedicated function for the purpose into your AppDatabase descendant class:

  Future<int> myItemCount() async {
    final result = await database.rawQuery("SELECT COUNT(`id`) AS cnt FROM MyItem");

    return result.isEmpty ? 0 : result[0]['cnt'] as int? ?? 0;
  }

[debbiefu]

The auto-generated code for SELECT COUNT(*) is _queryAdapter.queryNoReturn, so you won't get any result.

dao code:

@Query('SELECT COUNT(*) FROM notification WHERE groupUid = :groupUid AND createTime >= :timestamp AND hasRead IS FALSE')
 Future<int?> getUnReadNotificationCountByGroupId(String groupUid, int timestamp);

My solution:
Modify the auto-generated database.g.dart code:

@override
  Future<int?> getUnReadNotificationCountByGroupId(String groupUid, int timestamp) async {
    return _queryAdapter.query(
        'SELECT COUNT(*) FROM notification WHERE groupUid = ?1 AND createTime >= ?2 AND hasRead IS FALSE',
        mapper: (Map<String, Object?> row) => row['COUNT(*)'] as int,
        arguments: [groupUid, timestamp]);
  }

The raw sql result is a map: {'COUNT(*)': value}, so you just write the mapper to get the value is ok.