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","fred_bloggs@email.com"))
val msId = dao.insertAccount(Account(0,"Mary","Smith","m_smith@email.com"))
val jdId = dao.insertAccount(Account(0,"Jane","Doe","jane_doe@email.com"))
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 fred_bloggs@email.com 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 m_smith@email.com 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 jane_doe@email.com 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
