Parameterized query in floor

Question

I am trying to get the Sessions where the particular day is true. This is the Dao code.

  @Query('SELECT * FROM Session WHERE :day IS 1') // TRUE WAS NOT WORKING FOR SOME REASON
  Stream<List<Session>> getSessionByDay(String day);

This is the entity

@entity
class Session {
  @primaryKey
  final int id;
  ...
  final bool sunday;
  ...

  Session(
      {required this.id,
      ...
      this.sunday = false,
      ...
      });
}

This is the call

 database.sessionDao.getSessionByDay('sunday'),

When I use something like this it works

@Query(SELECT * FROM Session WHERE sunday IS 1)

There are no errors that I get.

Answer 1

To achieve the dynamic selection of sessions based on a specific day, you cannot directly pass a column name as a parameter in your query like :day. This is because column names cannot be parameterized in SQL queries in the same way values can be. Instead, you need a different approach to dynamically construct the query based on the day.

    abstract class SessionDao {
  @Query('SELECT * FROM Session WHERE sunday = :isTrue')
  Stream<List<Session>> getSessionsOnSunday(bool isTrue);

  @Query('SELECT * FROM Session WHERE monday = :isTrue')
  Stream<List<Session>> getSessionsOnMonday(bool isTrue);

  // Add similar methods for other days of the week

  Stream<List<Session>> getSessionByDay(String day) {
    switch (day.toLowerCase()) {
      case 'sunday':
        return getSessionsOnSunday(true);
      case 'monday':
        return getSessionsOnMonday(true);
      // Add similar cases for other days of the week
      default:
        throw Exception('Invalid day');
    }
  }
}