I have a database structure with a many-to-many relationship between Dream
s and Tag
s.
Dream
s and Tag
s are kept in separate tables, and there is a join table between them as usual in this kind of situation, with the class DreamTag
representing the connection:
protected class DreamTagTable(tag: Tag) extends Table[DreamTag](tag, "dreamtags") {
def dreamId = column[Long]("dream_id")
def dream = foreignKey("dreams", dreamId, dreams)(_.id)
def tagId = column[Long]("tag_id")
def tag = foreignKey("tags", tagId, tags)(_.id)
// default projection
def * = (dreamId, tagId) <> ((DreamTag.apply _).tupled, DreamTag.unapply)
}
I have managed to perform the appropriate double JOIN
to retrieve a Dream
with its Tag
s, but I struggled to do it in a fully non-blocking manner.
Here is my code for performing the retrieval, as this may shed some light on things:
def createWithTags(form: DreamForm): Future[Seq[Int]] = db.run {
logger.info(s"Creating dream [$form]")
// action to put the dream
val dreamAction: DBIO[Dream] =
dreams.map(d => (d.title, d.content, d.date, d.userId, d.emotion))
.returning(dreams.map(_.id))
.into((fields, id) => Dream(id, fields._1, fields._2, fields._3, fields._4, fields._5))
.+=((form.title, form.content, form.date, form.userId, form.emotion))
// action to put any tags that don't already exist (create a single action)
val tagActions: DBIO[Seq[MyTag]] =
DBIO.sequence(form.tags.map(text => createTagIfNotExistsAction(text)))
// zip allows us to get the results of both actions in a tuple
val zipAction: DBIO[(Dream, Seq[MyTag])] = dreamAction.zip(tagActions)
// put the entries into the join table, if the zipAction succeeds
val dreamTagsAction = exec(zipAction.asTry) match {
case Success(value) => value match {
case (dream, tags) =>
DBIO.sequence(tags.map(tag => createDreamTagAction(dream, tag)))
}
case Failure(exception) => throw exception
}
dreamTagsAction
}
private def createTagIfNotExistsAction(text: String): DBIO[MyTag] = {
tags.filter(_.text === text).result.headOption.flatMap {
case Some(t: MyTag) => DBIO.successful(t)
case None =>
tags.map(t => (t.text))
.returning(tags.map(_.id))
.into((text, id) => MyTag(id, text)) += text
}
}
private def createDreamTagAction(dream: Dream, tag: MyTag): DBIO[Int] = {
dreamTags += DreamTag(dream.id, tag.id)
}
/**
* Helper method for executing an async action in a blocking way
*/
private def exec[T](action: DBIO[T]): T = Await.result(db.run(action), 2.seconds)
Now I'm at the stage where I want to be able to update a Dream
and the list of Tag
s, and I'm struggling.
Given a situation where the existing list of tags is ["one", "two", "three"]
and is being updated to ["two", "three", "four"]
I want to:
Tag
for "one", if no other Dream
s reference it.Tag
and DreamTag
entries already exist.Tag
"four" if it doesn't exist, and add an entry to the join table for it.I think I need to do something like list1.diff(list2)
and list2.diff(list1)
but that would require getting performing a get first, which seems wrong.
Perhaps my thinking is wrong - Is it best to just clear all entries in the join table for this Dream
and then create every item in the new list, or is there a nice way to diff the two lists (previous and existing) and perform the deletes/adds as appropriate?
Thanks for the help.
N.B. Yes, Tag
is a super-annoying class name to have, as it clashes with slick.lifted.Tag
!
I went for option 2 as mentioned by Richard in his answer...
// action to put any tags that don't already exist (create a single action)
val tagActions: DBIO[Seq[MyTag]] =
DBIO.sequence(form.tags.map(text => createTagIfNotExistsAction(text)))
// zip allows us to get the results of both actions in a tuple
val zipAction: DBIO[(Int, Seq[MyTag])] = dreamAction.zip(tagActions)
// first clear away the existing dreamtags
val deleteExistingDreamTags = dreamTags
.filter(_.dreamId === dreamId)
.delete
// put the entries into the join table, if the zipAction succeeds
val dreamTagsAction = zipAction.flatMap {
case (_, tags) =>
DBIO.sequence(tags.map(tag => createDreamTagAction(dreamId, tag)))
}
deleteExistingDreamTags.andThen(dreamTagsAction)
I struggled to do it in a fully non-blocking manner.
I see you have an eval
call which is blocking. I looks like this can be replaced with a flatMap
:
case class Dream()
case class MyTag()
val zipAction: DBIO[(Dream, Seq[MyTag])] =
DBIO.successful( (Dream(), MyTag() :: MyTag() :: Nil) )
def createDreamTagAction(dream: Dream)(tag: MyTag): DBIO[Int] =
DBIO.successful(1)
val action: DBIO[Seq[Int]] =
zipAction.flatMap {
case (dream, tags) => DBIO.sequence(tags.map(createDreamTagAction(dream)))
}
Is it best to just clear all entries in the join table for this Dream and then create every item in the new list, or is there a nice way to diff the two lists (previous and existing) and perform the deletes/adds as appropriate?
Broadly, you have three options:
Look in the database to see what tags exist, compare them to what you want the state to be, and compute a set of insert and delete actions.
Delete all the tags and insert the state you want to reach.
Move the problem to SQL so you insert tags where they don't already exist in the table, and delete tags that don't exist in your desired state. You'd need to look at the capabilities of your database and likely need to use Plain SQL in Slick to get the effect. I'm not sure what the insert would be for adding tags (perhaps a MERGE or upsert of some kind), but deleting would be of the form: delete from tags where tag not in (1,2)
if you wanted a final state of just tags 1 and 2.
The trades off:
For 1, you need to run 1 query to fetch existing tags, and then 1 query for the deletes, and at least 1 for the inserts. This will change the smallest number of rows, but will be the largest number of queries.
For 2, you'll be executing at least 2 queries: a delete and 1 (potentially) for a bulk insert. This will change the largest number of rows.
For 3, you'll be executing a constant 2 queries (if your database can carry out the logic for you). If this is even possible, the queries will be more complicated.