SLICK: To insert value in 3 different tables using one slick api call

Question

The question is related to slick: I have three tables:
1) Users
2)Team2members
3)Team2Owners

In my post request to users, I am passing values of memberOf and managerOf, these values will be inserted in the tables Team2members and Team2Owners respectively and not in Users table. Though other values of the post request will be inserted in 'Users' table.

My Post request looks like below:

{"kind": "via#user",
      "userReference":{"userId":"priya16"},
      "user":"[email protected]","memberOf":{"teamReference":{"organizationId":"airtel","teamId":"supportteam"}},
       "managerOf":{"teamReference":{"organizationId":"airtel","teamId":"supportteam"}},
      "firstName":"Special_fn1",
      "lastName":"specialChar_ln1",
      "preferredName":[{"locale":"employee1","value":"@#$%^&*(Z0FH"}],
      "description":" preferredNameSpecialChar test "}  

I am forming the query which is shown below:
The query seems to work fine when only memberInsert is defined, when I try to define both the values i.e.memberInsert and managerInsert then insertion happens only for second value.

val query = config.api.customerTableDBIO(apiRequest.parameters.organizationId).flatMap { tables =>
      val userInsert = tables.Users returning tables.Users += empRow
      val memberInsert = inputObject.memberOf.map(m => m.copy(teamReference = m.teamReference.copy(organizationId = apiRequest.parameters.organizationId))).map { r =>
        for {
          team2MemberRow <- tables.Team2members returning tables.Team2members += Teams2MembersEntity.fromEmtToTeams2Members(r, empRow.id)
          team <- tables.Teams.filter(_.id === r.teamReference.teamId.toLowerCase).map(_.friendlyName).result.headOption
        } yield (team2MemberRow, team)
      }
      val managerInsert = inputObject.managerOf.map(m => m.copy(teamReference = m.teamReference.copy(organizationId = apiRequest.parameters.organizationId))).map { r =>
        for {
          team2OwnerRow <- tables.Team2owners returning tables.Team2owners += Teams2OwnersEntity.fromEmtToTeam2owners(r, empRow.id)
          team <- tables.Teams.filter(_.id === r.teamReference.teamId.toLowerCase).map(_.friendlyName).result.headOption
        } yield (team2OwnerRow, team)
      }

      userInsert.flatMap { userRow =>
        val user = UserEntity.fromDbEntity(userRow)
        if (memberInsert.isDefined) memberInsert.get
          .map(r => user.copy(memberOf = Some(Teams2MembersEntity.fromEmtToMemberRef(r._1, r._2.map(TeamEntity.toApiFriendlyName).getOrElse(List.empty)))))
        else DBIO.successful(user)
        if (managerInsert.isDefined) managerInsert.get
          .map(r => user.copy(managerOf = Some(Teams2OwnersEntity.fromEmtToManagerRef(r._1, r._2.map(TeamEntity.toApiFriendlyName).getOrElse(List.empty)))))
        else DBIO.successful(user)
      }
    }

Answer 1

The query seems to work fine when only memberInsert is defined, when I try to define both the values i.e.memberInsert and managerInsert then insertion happens only for second value.

The problem looks to be with the final call to flatMap.

That should return a DBIO[T]. However, your expression generates a DBIO[T] in various branches, but only one value will be returned from flatMap. That would explain why you don't see all the actions being run.

Instead, what you could do is assign each step to a value and sequence them. There are lots of ways you could do that, such as using DBIO.seq or andThen.

Here's a sketch of one approach that might work for you....

val maybeInsertMemeber: Option[DBIO[User]] =
  member.map( your code for constructing an action here )

val maybeInsertManager Option[DBIO[User]] =
  manager.map( your code for constructing an action here )

DBIO.sequenceOption(maybeInsertMember) andThen
  DBIO.sequenceOption(maybeInsertManager) andThen
  DBIO.successful(user)

The result of that expression is a DBIO[User] which combines three queries together.