Android Room: How to use embedded with a custom query

Question

I have a PostDAO, that looks like this.

@Dao
public interface PostDAO extends DAOTemplate<Post> {
    @Query("SELECT * FROM posts order by time DESC")
    LiveData<List<Post>> getPosts();
}

And the Post Pojo being.

@Keep
@Entity(tableName = "posts")
open class Post : Serializable, Cloneable {
    @NonNull
    @PrimaryKey
    var id: String? = null

    var text: String? = null

    var time: Long = 0
    var uid: String? = null
    @Embedded
    var user: User? = null

    public override fun clone(): Post {
        return super.clone() as Post
    }
}

As you can see, the User object is @Embedded

And User's DAO

@Dao
public interface UserDAO extends DAOTemplate<User> {
@Query("SELECT *,(SELECT sound_time FROM sounds WHERE sound_uid =:id AND " +
                "(sound_time < strftime('%s', 'now'))) AS hasNewMusic    " +
                "FROM users WHERE user_uid = :id")
        LiveData<User> getUser(String id);
}

And User Pojo

@Keep
@IgnoreExtraProperties
@Entity(tableName = "users")
class User : ModelTemplate() {
    @NonNull
    @PrimaryKey
    @ColumnInfo(name = "user_uid")
    override var id: String? = null;

    var name: String? = null
    var icon: String? = null

    var hasNewMusic: Boolean = false
}

Now, I want the Embedded user field on the Post Object, to have the field, hasNewMusic, populated with a subQuery.

Tried the above, not working and not sure about if this is how to go about this.

Answer 1

First of all there is no Boolean in SQLite and Room (as I've read) maps 1 and 0 INTEGER values to Boolean's true and false (see Hardcode Boolean Query In Room Database). By the name I can guess sound_time is not bounded in 1 and 0 values. So, try to convert sound_time to it.

Also, I think you misused @Embedded annotation. It's used to put columns of one table into classes that can be grouped by some reason. For example here (@Embedded) is written:

So if you have a query that returns street, latitude, longitude, Room will properly construct an Address class.

So you should either modify PostDAO::getPosts() method to return columns of a User, through INNER JOIN plus your giant select. And I don't guarantee this will work as I haven't done that. Also It's bad for some reasons.

Or you can leave Post without User field and make an Entity called PostWithUser and use a @Relation annotation. Wich is better and recommended in documentation.

UPD:

To fetch two entities try this:

1. Remove User field from post.

2. Add ForeignKey to Post. I'll use userId, if uid is already for that use it instead:

   @Entity(tableName = "posts",
           foreignKeys = [ForeignKey(
                   entity = Post::class,
                   parentColumns = ["user_id"], //from ColumnInfo of User class
                   childColumns = ["userId"],
                   onDelete = CASCADE)],
           indices = [Index(value = ["userId"]]))
   class Post {
   
       @PrimaryKey
       var id: String? = null
   
       var userId: String? = null
   
       //...else code....
   
   }
   

3. Make PostWithUser class:

   class PostWithUser {
       @Embedded lateinit var post: Post
       @Embedded lateinit var user: User 
   }
   

4. Make PostWithUserDao:

   class PostWithUserDao {
       @Query("select * from post, user where post.userId = user.user_id")
       fun getPostsWithUsers(): List<PostWithUser>
   }
   

And as I didn't manage to fit sound_time subquery here, I would do it in a second query, but if you figure out how to do it, I think it can work.

Also see this: Room database with one-to-one relation