The schema of my Database has 4 tables Server, Sites, Groups and Messages. Every Server has many Sites and every Site has many Groups.
There is a POJO called GroupItem that holds the Group and the unreadMessages of the Group.
data class GroupItem(
@Embedded
val group: Group,
@ColumnInfo(name = "unreadCounter")
val unreadCounter: Int
)
The unReadCounter field can be found only with a join with the Messages table.
I also have a POJO that holds a Site with its GroupItems
data class SiteItem(
@Embedded
val site: Site,
@Relation(entity = Group::class, entityColumn = "groups_site_id", parentColumn = "sites_site_id")
val groupItem: List<GroupItem>
)
So in my UI i want to display a list of a Server with all its SiteItems. So i thought of using Room 2.4.2 by observing the query
@Query("""
Select * from sites
inner join servers on servers.server_id = sites.sites_server_id"""
)
fun getServerItems(): LiveData<Map<Server, List<SiteItem>>>
When i try to build the project i get the error
There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: unreadCounter)
private final java.util.List<com.example.model.entities.pojos.GroupItem> groupItem = null
I can understand the issue and why this is happening. Its because it cannot know how to fill up this field. I guess i have to do a double query or something in a transaction, but how can i do that since i want to return a LiveData of a Map to the UI? How can i solve this kind of a problem?
CodePudding user response：
As you have:-
@ColumnInfo(name = "unreadCounter")
val unreadCounter: Int
a) the @Columninfo annotation has no purpose so really you just have val unreadCounter: Int
b) there will be no such column, so it would need to be generated.
- e.g. something along the lines of
SELECT *,(SELECT count(read_status) FROM messages WHERE groups_group_id = g.group_id AND read_status) AS unreadCounter FROM groups AS g WHERE group_id=:group_id. - However, to incorporate this would be relatively complex.
- Note the column read_status has been made up and will probably not reflect what you have.
Assuming that, in addition to your description of the relationships, a Group has many messages which can be either read or unread the the following is an working example that gets the unread messages for a group and also for a site.
It uses a function rather than a query to ascertain the unread count.
First the @Entity annotated classes Server, Site, Group and Message for the 4 tables :-
@Entity(tableName = "servers")
data class Server(
@PrimaryKey
var server_id: Long?=null,
var server_name: String
)
@Entity(tableName ="sites")
data class Site(
@PrimaryKey
var site_id: Long?=null,
var sites_server_id: Long,
var site_name: String
)
@Entity(tableName = "groups")
data class Group(
@PrimaryKey
var group_id: Long?=null,
var groups_site_id: Long,
var group_name: String
)
@Entity(tableName = "messages")
data class Message(
@PrimaryKey
var message_id: Long?=null,
var groups_group_id: Long,
var message_name: String,
var read_status: Boolean = false
)
- Obviously these will probably not reflect you actual classes.
Now some POJO classes :-
First GroupWithMessages :-
data class GroupWithMessages(
@Embedded
var group: Group,
@Relation(entity = Message::class, parentColumn = "group_id", entityColumn = "groups_group_id")
var messages: List<Message>
) {
fun getUnreadCounter(): Int {
var rv: Int = 0
for(m in messages) {
if (!m.read_status) {
rv
}
}
return rv
}
}
- with a function to retrieve the unread messages.
and second an adaptation of your SiteItem POJO :-
data class SiteItem(
@Embedded
var site: Site,
@Relation(entity = Group::class, parentColumn = "site_id", entityColumn = "groups_site_id")
var groupList: List<GroupWithMessages>
) {
fun getSiteUnreadCount(): Int {
var rv: Int = 0
for (g in groupList) {
rv = rv g.getUnreadCounter()
}
return rv
}
}
- with an additional function that will retrieve the unread message count for all groups in the Site.
All of the dao functions in interface Alldao :-
@Dao
interface AllDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(server: Server): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(site: Site): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(group: Group): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(message: Message): Long
@Transaction
@Query("SELECT * FROM sites INNER JOIN servers on servers.server_id = sites.sites_server_id")
fun getServerItems(): Map<Server, List<SiteItem>>
}
- Note that for convenience and brevity LiveData has not been used
an @Database annotated class TheDatabase :-
@Database(entities = [Server::class,Site::class,Group::class,Message::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDao(): AllDao
companion object {
private var instance: TheDatabase? = null
fun getInstance(context: Context): TheDatabase {
if (instance==null) {
instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
.allowMainThreadQueries()
.build()
}
return instance as TheDatabase
}
}
}
Finally putting it all together in an activity (designed to run just the once) which inserts various data and then uses the getServerItems function to drive a loop that reports on the data:-
const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getAllDao()
val s1id = dao.insert(Server(server_name = "Server1"))
val s2id = dao.insert(Server(server_name = "Server2"))
val site1id = dao.insert(Site(sites_server_id = s1id, site_name = "Site1"))
val site2Id = dao.insert(Site(sites_server_id = s1id, site_name = "Site2"))
val site3Id = dao.insert(Site(sites_server_id = s2id, site_name = "Site3"))
val site4Id = dao.insert(Site(sites_server_id = s2id, site_name = "Site4"))
val g1id = dao.insert(Group(groups_site_id = site1id, group_name = "Group1"))
val g2id = dao.insert(Group(groups_site_id = site1id, group_name = "Group2"))
val g3id = dao.insert(Group(groups_site_id = site1id, group_name = "Group3"))
val g4Id = dao.insert(Group(groups_site_id = site2Id, group_name = "Group4"))
dao.insert(Message(groups_group_id = g1id, message_name = "M1"))
dao.insert(Message(groups_group_id = g1id, message_name = "M2", read_status = true))
dao.insert(Message(groups_group_id = g1id, message_name = "M3"))
dao.insert(Message(groups_group_id = g1id, message_name = "M4"))
for(si in dao.getServerItems()) {
Log.d(TAG,"Server is ${si.key.server_name} ID is ${si.key.server_id} it has ${si.value.size} sites.")
for (s in si.value) {
Log.d(TAG,"\tSite is ${s.site.site_name} there are ${s.groupList.size} Groups with ${s.getSiteUnreadCount()} unread messages.")
for(g in s.groupList) {
Log.d(TAG,"\t\tGroup is ${g.group.group_name} there are ${g.messages.size} messages, ${g.getUnreadCounter()} unread")
}
}
}
}
}
Running the above results in the log :-
D/DBINFO: Server is Server1 ID is 1 it has 2 sites.
D/DBINFO: Site is Site1 there are 3 Groups with 3 unread messages.
D/DBINFO: Group is Group1 there are 4 messages, 3 unread
D/DBINFO: Group is Group2 there are 0 messages, 0 unread
D/DBINFO: Group is Group3 there are 0 messages, 0 unread
D/DBINFO: Site is Site2 there are 1 Groups with 0 unread messages.
D/DBINFO: Group is Group4 there are 0 messages, 0 unread
D/DBINFO: Server is Server2 ID is 2 it has 2 sites.
D/DBINFO: Site is Site3 there are 0 Groups with 0 unread messages.
D/DBINFO: Site is Site4 there are 0 Groups with 0 unread messages.
i.e. of the 4 messages added (to Group1, which has Site1 as it's parent which has Server1 as it's parent) 3 are unread an 1 is read.