I have a database that connects Workers to a Job. Each job then consists of Posters where each Poster can only be assigned to a single person.
The is all simple enough but the flow is a bit more complicated. A worker needs to apply to a Job before they can get it assigned to them. After they are hired then the Workers can start to select Posters they want to work on.
What would be great would be if there was a way to add additional information to my Junction tables. That way in my Jobs and Workers Junction table I could do 2 separate queries, one where the Job has been applied, and then another where the Job had been approved.
Another way to do this would be to have a separate Junction table, one for the applied Jobs and another for the accepted Jobs; but that would get very messy and annoying to deal with, as I would have to work with two separate lists. What would be best is if I could add a column to the Junction table which then gets used in the Job list, and the individual Jobs can just applied or approved.
Here are the entities and relations in question
@Entity(
foreignKeys = [
ForeignKey(
entity = Owner::class,
parentColumns = ["owner_id"],
childColumns = ["owner_id_map"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class Job (
@PrimaryKey val job_id: Long,
val status: Int,
@ColumnInfo(index = true)
val owner_id_map: Long,
val title: String,
val city: String
) : Serializable
JobPW stands for Job with Workers and Posters
data class JobPW(
@Embedded val job: Job,
@Relation(
parentColumn = "job_id",
entityColumn = "worker_id",
associateBy = Junction(JobWorkerCrossRef::class)
)
val workers: List<Worker>,
@Relation(
entity = Poster::class,
parentColumn = "job_id",
entityColumn = "job_id_map"
)
val posters: List<PosterWithWorker>
) : Serializable
@Entity
class Worker(
@PrimaryKey
@ColumnInfo(name = "worker_id")
val worker_id: Long,
val workerName: String,
) : Serializabe
@Entity(
primaryKeys = ["job_id","worker_id"], /* composite primary key */
foreignKeys = [
ForeignKey(
entity = Job::class,
parentColumns = ["job_id"],
childColumns = ["job_id"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
),
ForeignKey(
entity = Worker::class,
parentColumns = ["worker_id"],
childColumns = ["worker_id"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class JobWorkerCrossRef(
val job_id: Long,
@ColumnInfo(index = true)
val worker_id: Long,
)
Here is the query that builds the jobs list.
@Transaction
@Query("SELECT * FROM job")
fun getJobsFlow(): Flow<List<JobPW>>
@Transaction
@Query("SELECT * FROM job")
suspend fun getJobs(): List<JobPW>
(One version is a flow, I would like to use this one preferably)
What would be great would be if there was a way to add additional information to my Junction tables.
There is a way. You simply (depending upon the results) treat the table as a normal table and retrieve the data from it.
So you could have something like:-
data class JobPW(
@Embedded val job: Job,
@Relation(
parentColumn = "job_id",
entityColumn = "worker_id",
associateBy = Junction(JobWorkerCrossRef::class)
)
/*<<<<<<<<<< ADDDED >>>>>>>>>>*/
val workers: List<Worker> ,
@Relation(
entity = JobWorkerCrossRef::class,
entityColumn = "job_id",
parentColumn = "job_id"
)
val junctionWithExtra: List<JobWorkerCrossRef>,
(<<<<<<<<<< END OF ADDITION >>>>>>>>>*/
@Relation(
entity = Poster::class,
parentColumn = "job_id",
entityColumn = "job_id_map"
)
val posters: List<PosterWithWorker>
) : Serializable
Working Example
The following is a working example based upon the code from the question with omitted classes added to cope (adapted to run on the main thread without flows/suspends for brevity of the demo). So the whole database code for the demo is:-
@Entity
data class Owner(
@PrimaryKey
val owner_id: Long?=null,
val ownerName: String
)
@Entity
data class Poster(
@PrimaryKey
val poster_id: Long?=null,
@ColumnInfo(index = true)
val job_id_map: Long,
val worker_id: Long,
val posterName: String
)
@Entity(
foreignKeys = [
ForeignKey(
entity = Owner::class,
parentColumns = ["owner_id"],
childColumns = ["owner_id_map"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class Job (
@PrimaryKey val job_id: Long?=null,
val status: Int,
@ColumnInfo(index = true)
val owner_id_map: Long,
val title: String,
val city: String
) : Serializable
data class JobPW(
@Embedded val job: Job,
@Relation(
parentColumn = "job_id",
entityColumn = "worker_id",
associateBy = Junction(JobWorkerCrossRef::class)
)
val workers: List<Worker> ,
@Relation(
entity = JobWorkerCrossRef::class,
entityColumn = "job_id",
parentColumn = "job_id"
)
val junctionWithExtra: List<JobWorkerCrossRef>,
@Relation(
entity = Poster::class,
parentColumn = "job_id",
entityColumn = "job_id_map"
)
val posters: List<PosterWithWorker>
) : Serializable
@Entity
data class Worker(
@PrimaryKey
@ColumnInfo(name = "worker_id")
val worker_id: Long?=null,
val workerName: String
) : Serializable
data class PosterWithWorker(
@Embedded
val poster: Poster,
@Relation(
entity = Worker::class,
entityColumn = "worker_id",
parentColumn = "poster_id"
)
val workers: List<Worker>
)
@Entity(
primaryKeys = ["job_id","worker_id"], /* composite primary key */
foreignKeys = [
ForeignKey(
entity = Job::class,
parentColumns = ["job_id"],
childColumns = ["job_id"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
),
ForeignKey(
entity = Worker::class,
parentColumns = ["worker_id"],
childColumns = ["worker_id"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class JobWorkerCrossRef(
val job_id: Long,
@ColumnInfo(index = true)
val worker_id: Long,
val extraData: String
)
@Dao
interface AllDAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(owner: Owner): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(worker: Worker): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(poster: Poster): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(job: Job): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(jobWorkerCrossRef: JobWorkerCrossRef): Long
@Transaction
@Query("SELECT * FROM job")
/*suspend*/ fun getJobs(): List<JobPW>
}
@Database(entities = [Owner::class,Job::class,Poster::class,Worker::class,JobWorkerCrossRef::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDAOs(): AllDAOs
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
}
}
}
To demonstrate the following code was used in an activity. The code loads some data (intended to be used just for the single run and not repeated) and then extracts the data using the query from the question with the modified JobPW POJO (note that this is modified solely to get the extra data):-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getAllDAOs()
val o1id = dao.insert(Owner(ownerName = "Owner1"))
val o2id = dao.insert(Owner(ownerName = "Owner2"))
val o3id = dao.insert(Owner(ownerName = "Owner3"))
val j1id = dao.insert(Job(status = 1, owner_id_map = o1id, title = "Job1", city = "Somewhere"))
val j2id = dao.insert(Job(status = 2, owner_id_map = o1id, title = "Job2", city = "Elsewhere"))
val j3id = dao.insert(Job(status = 3, owner_id_map = o2id, title = "Job3", city = "Everywhere"))
val j4id = dao.insert(Job(status = 4, owner_id_map = o3id, title = "Job4", city = "Nowhere"))
val w1id = dao.insert(Worker(workerName = "Fred"))
val w2id = dao.insert(Worker(workerName = "Mary"))
val w3id = dao.insert(Worker(workerName = "Jane"))
val w4id = dao.insert(Worker(workerName = "Tom"))
val w5id = dao.insert(Worker(workerName = "Anne"))
val w6id = dao.insert(Worker(workerName = "Bill"))
val p1id = dao.insert(Poster(job_id_map = j1id, worker_id = w6id, posterName = "Poster1 for Job1 by Bill"))
val p2id = dao.insert(Poster(job_id_map = j1id, worker_id = w5id, posterName = "Poster2 for Job1 by Anne"))
val p3Id = dao.insert(Poster(job_id_map = j2id, worker_id = w6id, posterName = "Poster3 for Job2 by Bill"))
val p4id = dao.insert(Poster(job_id_map = j3id, worker_id = w4id, posterName = "Poster4 for Job3 by Tom"))
dao.insert(JobWorkerCrossRef(job_id = j1id, worker_id = w1id, extraData = "J1W1ABC"))
dao.insert(JobWorkerCrossRef(job_id = j1id, worker_id = w3id, extraData = "J1W3DEF"))
dao.insert(JobWorkerCrossRef(j1id,w5id,"J1W5GHI"))
dao.insert(JobWorkerCrossRef(j2id,w2id,"J2W2JKL"))
dao.insert(JobWorkerCrossRef(j2id ,w4id,"J2W4MNO"))
dao.insert(JobWorkerCrossRef(j2id,w6id,"J2W6PQR"))
dao.insert(JobWorkerCrossRef(j4id,w1id,"J4W1STU"))
dao.insert(JobWorkerCrossRef(j4id,w2id,"J4W2VWX"))
dao.insert(JobWorkerCrossRef(j4id,w3id,"J4W3YZ1"))
dao.insert(JobWorkerCrossRef(j4id,w4id,"J4W4234"))
dao.insert(JobWorkerCrossRef(j4id,w5id,"J4W5567"))
dao.insert(JobWorkerCrossRef(j4id,w6id,"J4W6890"))
for(jpw in dao.getJobs()) {
val pli = StringBuilder()
for (p in jpw.posters) {
val pwli = StringBuilder()
for (w in p.workers) {
pwli.append("\n\n\tWorker is ${w.workerName} ID is ${w.worker_id} (via Poster)")
}
pli.append("\n\tPoster is ${p.poster.posterName} ID is ${p.poster.poster_id} mapped to JobID ${p.poster.job_id_map} there are ${p.workers.size} Workers. They are:-${pwli}")
}
val jpel = StringBuilder()
for (je in jpw.junctionWithExtra) {
jpel.append("\n\tJunctionWithExtra (Extra Data) is ${je.extraData} JobID is ${je.job_id} WorkerID is ${je.worker_id}")
}
val jwl = StringBuilder()
for (w in jpw.workers) {
jwl.append(("\n\tWorker is ${w.workerName} ID is ${w.worker_id} (via Job)"))
}
Log.d(
"DBINFO",
"Job is ${jpw.job.title} City is ${jpw.job.city} Status is ${jpw.job.status} JOBID is ${jpw.job.job_id} OwenerID is ${jpw.job.owner_id_map}" +
".\nThere are ${jpw.posters}! They are:-$pli" +
".\nThere are ${jpw.junctionWithExtra.size} Junctions With Extra Data! They are:-${jpel}" +
".\nThere are ${jpw.workers.size} Workers!. They are:-${jwl}")
}
}
}
Result
2023-03-29 11:20:35.042 D/DBINFO: Job is Job1 City is Somewhere Status is 1 JOBID is 1 OwenerID is 1.
There are [PosterWithWorker(poster=Poster(poster_id=1, job_id_map=1, worker_id=6, posterName=Poster1 for Job1 by Bill), workers=[Worker(worker_id=1, workerName=Fred)]), PosterWithWorker(poster=Poster(poster_id=2, job_id_map=1, worker_id=5, posterName=Poster2 for Job1 by Anne), workers=[Worker(worker_id=2, workerName=Mary)])]! They are:-
Poster is Poster1 for Job1 by Bill ID is 1 mapped to JobID 1 there are 1 Workers. They are:-
Worker is Fred ID is 1 (via Poster)
Poster is Poster2 for Job1 by Anne ID is 2 mapped to JobID 1 there are 1 Workers. They are:-
Worker is Mary ID is 2 (via Poster).
There are 3 Junctions With Extra Data! They are:-
JunctionWithExtra (Extra Data) is J1W1ABC JobID is 1 WorkerID is 1
JunctionWithExtra (Extra Data) is J1W3DEF JobID is 1 WorkerID is 3
JunctionWithExtra (Extra Data) is J1W5GHI JobID is 1 WorkerID is 5.
There are 3 Workers!. They are:-
Worker is Fred ID is 1 (via Job)
Worker is Jane ID is 3 (via Job)
Worker is Anne ID is 5 (via Job)
2023-03-29 11:20:35.042 D/DBINFO: Job is Job2 City is Elsewhere Status is 2 JOBID is 2 OwenerID is 1.
There are [PosterWithWorker(poster=Poster(poster_id=3, job_id_map=2, worker_id=6, posterName=Poster3 for Job2 by Bill), workers=[Worker(worker_id=3, workerName=Jane)])]! They are:-
Poster is Poster3 for Job2 by Bill ID is 3 mapped to JobID 2 there are 1 Workers. They are:-
Worker is Jane ID is 3 (via Poster).
There are 3 Junctions With Extra Data! They are:-
JunctionWithExtra (Extra Data) is J2W2JKL JobID is 2 WorkerID is 2
JunctionWithExtra (Extra Data) is J2W4MNO JobID is 2 WorkerID is 4
JunctionWithExtra (Extra Data) is J2W6PQR JobID is 2 WorkerID is 6.
There are 3 Workers!. They are:-
Worker is Mary ID is 2 (via Job)
Worker is Tom ID is 4 (via Job)
Worker is Bill ID is 6 (via Job)
2023-03-29 11:20:35.042 D/DBINFO: Job is Job3 City is Everywhere Status is 3 JOBID is 3 OwenerID is 2.
There are [PosterWithWorker(poster=Poster(poster_id=4, job_id_map=3, worker_id=4, posterName=Poster4 for Job3 by Tom), workers=[Worker(worker_id=4, workerName=Tom)])]! They are:-
Poster is Poster4 for Job3 by Tom ID is 4 mapped to JobID 3 there are 1 Workers. They are:-
Worker is Tom ID is 4 (via Poster).
There are 0 Junctions With Extra Data! They are:-.
There are 0 Workers!. They are:-
2023-03-29 11:20:35.047 D/DBINFO: Job is Job4 City is Nowhere Status is 4 JOBID is 4 OwenerID is 3.
There are []! They are:-.
There are 6 Junctions With Extra Data! They are:-
JunctionWithExtra (Extra Data) is J4W1STU JobID is 4 WorkerID is 1
JunctionWithExtra (Extra Data) is J4W2VWX JobID is 4 WorkerID is 2
JunctionWithExtra (Extra Data) is J4W3YZ1 JobID is 4 WorkerID is 3
JunctionWithExtra (Extra Data) is J4W4234 JobID is 4 WorkerID is 4
JunctionWithExtra (Extra Data) is J4W5567 JobID is 4 WorkerID is 5
JunctionWithExtra (Extra Data) is J4W6890 JobID is 4 WorkerID is 6.
There are 6 Workers!. They are:-
Worker is Fred ID is 1 (via Job)
Worker is Mary ID is 2 (via Job)
Worker is Jane ID is 3 (via Job)
Worker is Tom ID is 4 (via Job)
Worker is Anne ID is 5 (via Job)
Worker is Bill ID is 6 (via Job)
As can be the respective extra data has been retrieved simply by treating the JobWorkerCrossRef table as a normal table via an @Relation rather than as a Junction table.
So in short to not get data from the junction table you use it as a junction table via a Junction.
One step further, combining the Worker and the Extra data
To get the combined data mapped Worker and Extra you could have POJO's that gets the specific Worker details by @Relationing the Worker.
As an example of the latter you could have:-
data class JobPWE(
@Embedded
val job: Job,
@Relation(
entity = JobWorkerCrossRef::class,
parentColumn = "job_id",
entityColumn = "job_id"
)
val junctionWithExtra: List<JobWorkerCrossRefWithWorker>,
@Relation(
entity = Poster::class,
parentColumn = "job_id",
entityColumn = "job_id_map"
)
val posters: List<PosterWithWorker>
)
and for example
data class JobWorkerCrossRefWithWorker(
@Embedded
val jwxr: JobWorkerCrossRef,
@Relation(
entity = Worker::class,
parentColumn = "worker_id",
entityColumn = "worker_id"
)
val workerDetail: Worker
)
along with (allowing getJobs to be as is):-
@Transaction
@Query("SELECT * FROM job")
fun getJobsV2(): List<JobPWE>
And then by using the following in the activity code:-
for (jpwe in dao.getJobsV2()) {
val pli = StringBuilder()
for (p in jpwe.posters) {
val pwli = StringBuilder()
for (w in p.workers) {
pwli.append("\n\t\tWorker is ${w.workerName} ID is ${w.worker_id} (via Poster)")
}
pli.append("\n\tPoster is ${p.poster.posterName} ID is ${p.poster.poster_id} mapped to JobID ${p.poster.job_id_map} there are ${p.workers.size} Workers. They are:-${pwli}")
}
val jpel = StringBuilder()
for (je in jpwe.junctionWithExtra) {
jpel.append(
"\n\tJunctionWithExtra (Extra Data) is ${je.jwxr.extraData} (from junction) " +
"JobID is ${je.jwxr.job_id} (from junction table) " +
"WorkerID is ${je.jwxr} (from junction table) " +
"WorkerName is ${je.workerDetail.workerName} (from Worker table related to the junction table)"
)
}
Log.d(
"DBINFOV2",
"Job is ${jpwe.job.title} City is ${jpwe.job.city} Status is ${jpwe.job.status} JOBID is ${jpwe.job.job_id} OwnerID is ${jpwe.job.owner_id_map}" +
".\nThere are ${jpwe.posters.size} posters! They are:-$pli" +
".\nThere are ${jpwe.junctionWithExtra.size} Junctions With Extra Data! They are:-${jpel}"
)
}
Then when using the same data the Result in the log would show:-
2023-03-29 16:44:02.176 D/DBINFOV2: Job is Job1 City is Somewhere Status is 1 JOBID is 1 OwnerID is 1.
There are 2 posters! They are:-
Poster is Poster1 for Job1 by Bill ID is 1 mapped to JobID 1 there are 1 Workers. They are:-
Worker is Fred ID is 1 (via Poster)
Poster is Poster2 for Job1 by Anne ID is 2 mapped to JobID 1 there are 1 Workers. They are:-
Worker is Mary ID is 2 (via Poster).
There are 3 Junctions With Extra Data! They are:-
JunctionWithExtra (Extra Data) is J1W1ABC (from junction) JobID is 1 (from junction table) WorkerID is JobWorkerCrossRef(job_id=1, worker_id=1, extraData=J1W1ABC) (from junction table) WorkerName is Fred (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J1W3DEF (from junction) JobID is 1 (from junction table) WorkerID is JobWorkerCrossRef(job_id=1, worker_id=3, extraData=J1W3DEF) (from junction table) WorkerName is Jane (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J1W5GHI (from junction) JobID is 1 (from junction table) WorkerID is JobWorkerCrossRef(job_id=1, worker_id=5, extraData=J1W5GHI) (from junction table) WorkerName is Anne (from Worker table related to the junction table)
2023-03-29 16:44:02.176 D/DBINFOV2: Job is Job2 City is Elsewhere Status is 2 JOBID is 2 OwnerID is 1.
There are 1 posters! They are:-
Poster is Poster3 for Job2 by Bill ID is 3 mapped to JobID 2 there are 1 Workers. They are:-
Worker is Jane ID is 3 (via Poster).
There are 3 Junctions With Extra Data! They are:-
JunctionWithExtra (Extra Data) is J2W2JKL (from junction) JobID is 2 (from junction table) WorkerID is JobWorkerCrossRef(job_id=2, worker_id=2, extraData=J2W2JKL) (from junction table) WorkerName is Mary (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J2W4MNO (from junction) JobID is 2 (from junction table) WorkerID is JobWorkerCrossRef(job_id=2, worker_id=4, extraData=J2W4MNO) (from junction table) WorkerName is Tom (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J2W6PQR (from junction) JobID is 2 (from junction table) WorkerID is JobWorkerCrossRef(job_id=2, worker_id=6, extraData=J2W6PQR) (from junction table) WorkerName is Bill (from Worker table related to the junction table)
2023-03-29 16:44:02.176 D/DBINFOV2: Job is Job3 City is Everywhere Status is 3 JOBID is 3 OwnerID is 2.
There are 1 posters! They are:-
Poster is Poster4 for Job3 by Tom ID is 4 mapped to JobID 3 there are 1 Workers. They are:-
Worker is Tom ID is 4 (via Poster).
There are 0 Junctions With Extra Data! They are:-
2023-03-29 16:44:02.177 D/DBINFOV2: Job is Job4 City is Nowhere Status is 4 JOBID is 4 OwnerID is 3.
There are 0 posters! They are:-.
There are 6 Junctions With Extra Data! They are:-
JunctionWithExtra (Extra Data) is J4W1STU (from junction) JobID is 4 (from junction table) WorkerID is JobWorkerCrossRef(job_id=4, worker_id=1, extraData=J4W1STU) (from junction table) WorkerName is Fred (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J4W2VWX (from junction) JobID is 4 (from junction table) WorkerID is JobWorkerCrossRef(job_id=4, worker_id=2, extraData=J4W2VWX) (from junction table) WorkerName is Mary (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J4W3YZ1 (from junction) JobID is 4 (from junction table) WorkerID is JobWorkerCrossRef(job_id=4, worker_id=3, extraData=J4W3YZ1) (from junction table) WorkerName is Jane (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J4W4234 (from junction) JobID is 4 (from junction table) WorkerID is JobWorkerCrossRef(job_id=4, worker_id=4, extraData=J4W4234) (from junction table) WorkerName is Tom (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J4W5567 (from junction) JobID is 4 (from junction table) WorkerID is JobWorkerCrossRef(job_id=4, worker_id=5, extraData=J4W5567) (from junction table) WorkerName is Anne (from Worker table related to the junction table)
JunctionWithExtra (Extra Data) is J4W6890 (from junction) JobID is 4 (from junction table) WorkerID is JobWorkerCrossRef(job_id=4, worker_id=6, extraData=J4W6890) (from junction table) WorkerName is Bill (from Worker table related to the junction table)
i.e. the Worker and the Extra data are combined.
(One version is a flow, I would like to use this one preferably)
Wrapping in a Flow is as simple as doing that other than handling the Flow.