Retrieve user's posts from Room-CodePudding

I am trying this below, that I been writing. However, I am a bit new to this whole thing with Room. It does remind me of the Microsoft.Linq to some extent, however, the MS version is easier and more straightforward. Whereas this one is confusing a bit.

@Dao
interface AllDao {
// Account Data Access Object:
@Transaction
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insertAccount(account: Account)

@Delete
suspend fun deleteAccount(account: Account)

@Update
suspend fun updateAccount(account: Account)

@Transaction
@Query("SELECT * FROM `accounts` WHERE email = :email")
suspend fun getAccountByEmail(email: String): Flow<Account?>

// Post Data Access Object:
@Transaction
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertPost(post: Post)

@Delete
suspend fun deletePost(post: Post)

@Update
suspend fun updatePost(post: Post)

@Transaction
@Query("SELECT * FROM `posts` WHERE post_id = :post_id")
suspend fun getPostById(post_id: Int, user_id: Int): Flow<Post?>

@Transaction
@Query("SELECT * FROM `posts` ORDER BY posts.title")
fun getPostsByUserId(uid: Int): Flow<List<Posts>>

@Transaction
@Query("SELECT * FROM `posts` ORDER BY posts.title WHERE posts.post_id = :post_id AND accounts._id = :user_id")
fun getUserPostSingle(post_id: Int, user_id: Int) : Flow<Post?>

/*
    Account with Post Data Access Object:
*/
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(join: AccountWithPost)
}

Data structure: This is how I have setup the entities, however, this isn't as mentioned as straight forward as anticipated e.g., like Microsoft Linq.

@Entity(tableName = "accounts")
data class Account(
    @PrimaryKey(autoGenerate = true)
    @NonNull
    val id: Int,
    @ColumnInfo(name = "first_name")
    val firstName: String,
    @ColumnInfo(name = "last_name")
    val lastName: String?,
    val email: String
)


@Entity(
    tableName = "posts",
    foreignKeys = [ForeignKey(
        entity = Account::class,
        parentColumns = ["id"],
        childColumns = ["postUserId"],
        onDelete = ForeignKey.CASCADE,
        onUpdate = ForeignKey.CASCADE
    )]
)
data class post(
    @PrimaryKey(autoGenerate = true)
    @NonNull
    val post_id: Int,
    val title: String,
    val content: String,
)

data class AccountWithPost(
    @Embedded
    var account: Account,
    @Relation(
        parentColumn = "id",
        entity = Post::class,
        entityColumn = "postUserId"
    )
    var posts: List<Post>,
)

CodePudding user response：

You have a few issues, the most important as per the comments, is that you need to have something to relate a Post with it's PARENT account.

Another issue is that you appear to consider that AccountWithPost is a table (and you try to insert into this). It is not a table, rather it is a container that due to the @Relation annotation will retrieve an Account (the @Embedded) along with all the related Posts according to the ParentColumn and the Child Column (which is effectively the join).

Here's a working example (note without Flows/Suspends i.e. run on the mainThread for brevity/convenience).

The example (designed to just run once):-

adds 3 accounts and then
adds 5 posts to the first (Fred Bloggs)
adds 2 posts to the second account (Mary Smith)
adds 1 post to the third account (Jane Doe)
finally extracts everything as a List of AccountWithPosts

All of your classes PLUS an @Database annotated class :-

@Entity(tableName = "accounts")
data class Account(
    @PrimaryKey(autoGenerate = true)
    @NonNull
    val id: Int,
    @ColumnInfo(name = "first_name")
    val firstName: String,
    @ColumnInfo(name = "last_name")
    val lastName: String?,
    val email: String
)


@Entity(
    tableName = "posts",
    foreignKeys = [ForeignKey(
        entity = Account::class,
        parentColumns = ["id"],
        childColumns = ["postUserId"],
        onDelete = ForeignKey.CASCADE,
        onUpdate = ForeignKey.CASCADE
    )]
)
data class Post /* Changed to Post from post */(
    @PrimaryKey(autoGenerate = true)
    @NonNull
    val post_id: Int,
    @ColumnInfo(index = true) /* Index on FK column (else warning issued) */
    val postUserId: Int, /*<<<<<<<<<< Added  the Userid that is the parent to the post IMPORTANT */
    val title: String,
    val content: String,
)

data class AccountWithPost(
    @Embedded
    var account: Account,
    @Relation(
        parentColumn = "id",
        entity = Post::class,
        entityColumn = "postUserId"
    )
    var posts: List<Post>,
)

@Dao
interface AllDao {
    // Account Data Access Object:
    //@Transaction will be in a single transaction anyway
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    /*suspend*/ fun insertAccount(account: Account): Long /* Returns the rowid aka id of the inserted Account */

    @Delete
    /*suspend*/ fun deleteAccount(account: Account): Int /* Returns how many rows have been deleted */

    @Update
    /*suspend*/ fun updateAccount(account: Account): Int /* Returns how many rows have been updated */

    //@Transaction will be in a single transaction anyway
    @Query("SELECT * FROM `accounts` WHERE email = :email")
    /*suspend*/ fun getAccountByEmail(email: String): /*Flow<Account?>*/ List<Account> /*changed for demo on main thread */

    // Post Data Access Object:
    //@Transaction
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    /*suspend*/ fun insertPost(post: Post): Long /* id of inserted row */

    @Delete
    /*suspend*/ fun deletePost(post: Post): Int

    @Update
    /*suspend*/ fun updatePost(post: Post): Int

    @Transaction
    @Query("SELECT * FROM `posts` WHERE post_id = :post_id")
    /*suspend*/ fun getPostById(post_id: Int/*, user_id: Int UNUSED */): /*Flow<Post?>*/ List<Post>

    @Transaction
    @Query("SELECT * FROM `posts` /* ADDED */ WHERE postUserId=:uid /* END OF ADDED*/ ORDER BY posts.title")
    fun getPostsByUserId(uid: Int): /*Flow<List<Post>>*/ List<Post>

    @Transaction
    @Query("SELECT * FROM `posts`  WHERE posts.post_id = :post_id AND postUserId = :user_id /* CHANGED to use postUserId columns */ ORDER BY posts.title")
    fun getUserPostSingle(post_id: Int, user_id: Int) : /*Flow<Post?>*/ List<Post>

    /*
        Account with Post Data Access Object:
        Account With Post is NOT a table, a Post contains the reference
        Commented out
    */
    //@Insert(onConflict = OnConflictStrategy.IGNORE)
    //fun insert(join: AccountWithPost)

    @Transaction
    @Query("SELECT * FROM accounts")
    fun getAllAccountsWithTheirPosts(): List<AccountWithPost>
}

@Database(entities = [Account::class,Post::class], exportSchema = false, version = 1)
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
        }
    }
}

Please refer to the comments contained in the code

In addition to the above the code in the Activity (MainActivity) is :-

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 fbId = dao.insertAccount(Account(0,"Fred","Blogs","[email protected]"))
        val msId = dao.insertAccount(Account(0,"Mary","Smith","[email protected]"))
        val jdId = dao.insertAccount(Account(0,"Jane","Doe","[email protected]"))

        dao.insertPost(Post(0,fbId.toInt(),"FBP001","blah for fb p001"))
        dao.insertPost(Post(0,fbId.toInt(),"FBP002","blah for fb p002"))
        dao.insertPost(Post(0,fbId.toInt(),"FBP003","blah for fb p003"))
        dao.insertPost(Post(0,fbId.toInt(),"FBP004","blah for fb p004"))
        dao.insertPost(Post(0,fbId.toInt(),"FBP005","blah for fb p005"))

        dao.insertPost(Post(0,msId.toInt(),"MSP001","blah for ms p001"))
        dao.insertPost(Post(0,msId.toInt(),"MSP002","blah for ms p002"))

        dao.insertPost(Post(0,jdId.toInt(),"JDP001","blah for jd p001"))

        val sb = StringBuilder()
        for(awp in dao.getAllAccountsWithTheirPosts()) {
            sb.clear()
            for (p in awp.posts) {
                sb.append("\n\tPost Title is ${p.title} Content is ${p.content} ID is ${p.post_id} References User ${p.postUserId}")
            }
            Log.d("DBINFOI","Account FirstName is ${awp.account.firstName} "  
                    "Lastname is ${awp.account.lastName} "  
                    "Email is ${awp.account.email} ID is ${awp.account.id} "  
                    "The account has ${awp.posts.size} posts, if any they are:-$sb"
            )
        }
    }
}

Result

The output sent to the log is:-

D/DBINFOI: Account FirstName is Fred Lastname is Blogs Email is [email protected] ID is 1 The account has 5 posts, if any they are:-
        Post Title is FBP001 Content is blah for fb p001 ID is 1 References User 1
        Post Title is FBP002 Content is blah for fb p002 ID is 2 References User 1
        Post Title is FBP003 Content is blah for fb p003 ID is 3 References User 1
        Post Title is FBP004 Content is blah for fb p004 ID is 4 References User 1
        Post Title is FBP005 Content is blah for fb p005 ID is 5 References User 1
        
D/DBINFOI: Account FirstName is Mary Lastname is Smith Email is [email protected] ID is 2 The account has 2 posts, if any they are:-
        Post Title is MSP001 Content is blah for ms p001 ID is 6 References User 2
        Post Title is MSP002 Content is blah for ms p002 ID is 7 References User 2
        
D/DBINFOI: Account FirstName is Jane Lastname is Doe Email is [email protected] ID is 3 The account has 1 posts, if any they are:-
        Post Title is JDP001 Content is blah for jd p001 ID is 8 References User 3