Android Room getting count of elements in an 'incoming foreign key list' in an 'embedded entity'?

Question

I have 2 Entities, Grade and Student, Student is having a foreign key towards the class room. Embedded entity ClassRoom looks like this,

data class ClassRoom(

    @Embedded
    val level: ClassLevel

    @Relation(
        parentColumn = "id",
        entityColumn = "ClassLevel"
    )
    val students: List<Student>,

    // val strength: Int ?

)

What I am trying to achieve is, using ClassRoomInstance.strength to get the count of incoming foreign keys. Than calculating the sizes of students with students.size

What is the way to achieve this?

Thanks

Can you please add all the `Entity` class files in this context. Also adding the `Dao` would be helpful. Thanks! — Karan Dhillon, Feb 03 '21 at 13:03

score 10 · Accepted Answer · answered Feb 12 '21 at 11:48

10

You can create a query that returns the student count.

Consider the following POJO's, a Movie and Trailers

@Entity
data class Movie(
    @PrimaryKey
    val id: Int,
    val title: String,
    val overview: String
)

@Entity
data class Trailer(
    @PrimaryKey
    val id: Int,
    val movieId: Int,
    val trailerPath: String
)

Imagine that I want to get the Movie with the trailers count

data class MoviesWithTrailerCount(
    @Embedded
    val movie: Movie,
    val trailerCount: Int
)

In the MovieDAO, I can create a query:

@Transaction
@Query("SELECT M.*, COUNT(T.id) AS trailerCount FROM Movie M INNER JOIN Trailer T ON T.movieId = M.id WHERE M.id = :id")
abstract suspend fun moviesAndTrailersCount(id: Int): MoviesWithTrailerCount

So, basically what you want to achieve can be done using SQL queries and @Embedded

answered Feb 12 '21 at 11:48

João Paulo Sena

665
4
11

thank you, what if i want a list of all movies with trailer count? – Feb 13 '21 at 06:50
You would change the return type of the moviesAndTrailersCount to a List, or Flow and remove the where cause – João Paulo Sena Feb 14 '21 at 12:04
2

i had to modify the query for me to make it return all movies and include the cases where count(*) returns 0, but yours was really helpfull, this is mine: SELECT M.*, COUNT(T.id) AS trailerCount FROM Movie M LEFT JOIN Trailer T ON T.movieId = M.id GROUP BY M.id – karique Aug 30 '22 at 04:15
I am pulling a list of items with count, so I removed the `WHERE` clause and switched to `LEFT JOIN`. But I also had to add `GROUP BY M.id` because its more than one record. Thanks – thefallen Jan 17 '23 at 13:04

score 0 · Answer 2 · answered Feb 12 '21 at 06:31

0

Try adding this query to your Dao, see google samples:

@Query("SELECT COUNT(column) FROM table")
int getDataCount();

answered Feb 12 '21 at 06:31

Akash Pal

1,055
8
15

Android Room getting count of elements in an 'incoming foreign key list' in an 'embedded entity'?

2 Answers2