Android studio Room database - derived fields

And than android studio complains that the count is nog a field in object A.

When you @Ignore a field then you are say that Room should not consider that field as being stored and retrieved, as you have found.

You have various options.

• You can have a POJO that embeds object and then has an additional variable into which the count can be placed by have the query return the POJO
  • Note that effectively the @Ignore'd field serves little pupose.
  • You might as a well have the POJO as the real A object and the @Entity annotated a sub object.
• If you are only interested in the count but not the objects you can have a query that returns the single value (Long/Int), in which case the column name is irrelevant.
• If you query via a relationship and are returning the parent along with the children of the parent then you can use the list's size.

Demo

The A object with the @Ignored field :-

@Entity
data class A(
    @PrimaryKey
    var a_id: Long?=null,
    var a_name: String,
    @Ignore
    var a_children_count: Long=0
) {
    constructor(): this(a_id=null,a_name= "")
    constructor(id: Long, name: String): this(a_id = id, a_name = name,a_children_count = 0)
}

The B object, which will be a child to an A object :-

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = A::class,
            parentColumns = ["a_id"],
            childColumns = ["b_map_to_a"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class B(
    @PrimaryKey
    var b_id: Long?=null,
    var b_name: String,
    @ColumnInfo(index = true)
    var b_map_to_a: Long?
)

POJO for getting an A object along with the list of B objects that are A's children (for the third method i.e. number of children in the list):-

data class AWithRelatedB(
    @Embedded
    var a: A,
    @Relation(
        entity = B::class,
        parentColumn = "a_id",
        entityColumn = "b_map_to_a"

    )
    var listOfB: List<B>
)

POJO for getting an A object with the count of the number of B objects :-

data class AWithNumberOfRelatedB(
    @Embedded
    var TheA: A,
    var countOfRelatedB: Long
)

An @Dao annotated class:-

@Dao
interface TheDAOs {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(a: A): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(b: B): Long
    
    /* Just get the number of B's in an A */
    @Query("SELECT count(*) FROM A JOIN B ON a_id = b_map_to_a WHERE a_id=:aId")
    fun getNumberOfBsRelatedToAnA(aId: Long): Long
    /* Get an A object along with the count of the B's i.e. return a AWithNumberOfRelatedB */
    @Query("SELECT a.*, (SELECT count(*) FROM B WHERE b_map_to_a = a.a_id) AS countOfRelatedB FROM a WHERE a_id=:aId")
    fun getAWithTheNumberOfRelatedBs(aId: Long): AWithNumberOfRelatedB
    /* Get the A with the list of B's, the size of the list is the number of B's */
    @Transaction
    @Query("SELECT * FROM a")
    fun getEveryAWithItsBChildren(): List<AWithRelatedB>
}

Activity Code to demonstrate :-

class MainActivity : AppCompatActivity() {

    lateinit var demoDb: DemoDatabase
    lateinit var demoDao: TheDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        val TAG = "DBINFO"

        demoDb = DemoDatabase.getInstance(this)
        demoDao = demoDb.getTheDAO()

        demoDao.insert(A(10,"A1"))
        demoDao.insert(A(20,"A2"))
        demoDao.insert(A(30,"A3"))

        demoDao.insert(B(b_name = "B1 child of A1", b_map_to_a = 10))
        demoDao.insert(B(b_name = "B2 child of A1", b_map_to_a = 10))
        demoDao.insert(B(b_name = "B3 child of A1", b_map_to_a = 10))

        demoDao.insert(B(b_name = "B4 child of A2", b_map_to_a = 20))
        demoDao.insert(B(b_name = "B5 child of A2", b_map_to_a = 20))
        demoDao.insert(B(b_name = "B6 child of A2", b_map_to_a = 20))
        demoDao.insert(B(b_name = "B7 child of A2", b_map_to_a = 20))

        Log.d(TAG,"Number of B's in A1 is ${demoDao.getNumberOfBsRelatedToAnA(10)}")
        val aPlusBCount= demoDao.getAWithTheNumberOfRelatedBs(10)
        Log.d(TAG,"A's Name is ${aPlusBCount.TheA.a_name} Number of B's is ${aPlusBCount.countOfRelatedB}")
        for(awrc in demoDao.getEveryAWithItsBChildren()) {
            Log.d(TAG,"This A's name is ${awrc.a.a_name} the number of children is ${awrc.listOfB.size} ")
        }
    }
}

The Result included in the Log (blank lines added to split the output into the 3) :-

2022-09-04 06:58:49.122 D/DBINFO: Number of B's in A1 is 3

2022-09-04 06:58:49.125 D/DBINFO: A's Name is A1 Number of B's is 3

2022-09-04 06:58:49.130 D/DBINFO: This A's name is A1 the number of children is 3 
2022-09-04 06:58:49.130 D/DBINFO: This A's name is A2 the number of children is 4 
2022-09-04 06:58:49.130 D/DBINFO: This A's name is A3 the number of children is 0 

If you wanted multiple rows from getAWithTheNumberOfRelatedBs then you would change the WHERE clause accordingly (for all then no WHERE clause) and return a List<AWithNumberOfRelatedB>

e.g. (for all)

@Query("SELECT *, (SELECT count(*) FROM B WHERE b_map_to_a = a.a_id) AS countOfRelatedB FROM a")
fun (SELECT count(*) FROM B WHERE b_map_to_a = a.a_id)(): List<AWithNumberOfRelatedB>

Note that using a JOIN such as :-

@Query("SELECT *, count(*) AS countOfRelatedB FROM A JOIN B ON a.a_id=b_map_to_a GROUP BY a_id")
fun notOk(): List<AWithNumberOfRelatedB>

Then, using the data loaded as above, as A3 has no related B's A3 with a count of 0 would not be extracted as there is no join between A3 and anything. Whilst the subquery, as in (SELECT count(*) FROM B WHERE b_map_to_a = a.a_id) will return A's with no related B's.