I have two models:
case class User(uid: Option[Int], email: String, password: String, created_at: Timestamp, updated_at: Timestamp)
case class UserProfile(firstname: String, lastname: String, gender: Int, user_id: Long),
And a DAO with user table defined:
package dao
import java.sql.Timestamp
import scala.concurrent.{Await, Future}
import javax.inject.Inject
import models.{User, UserProfile}
import play.api.db.slick.DatabaseConfigProvider
import play.api.db.slick.HasDatabaseConfigProvider
import play.api.libs.concurrent.Execution.Implicits.defaultContext
import slick.driver.JdbcProfile
import slick.profile.SqlProfile.ColumnOption.SqlType
import scala.concurrent.duration._
import com.github.t3hnar.bcrypt._
class UsersDAO @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] {
import driver.api._
private val Users = TableQuery[UsersTable]
private val UsersProfile = TableQuery[UserProfileTable]
def all(): Future[Seq[User]] = db.run(Users.result)
def insert(user: User): Future[Int] = {
println("coming inside insert of user dao")
println(user)
// insertUP(user)
val hashPassword = user.password.bcrypt
val updatedUser = user.copy(password = hashPassword)
val query = db.run((Users returning Users.map(_.uid)) += updatedUser)
// val uid = Await.result(query, 30 seconds)
// println(s"UID ---------> $uid")
query
}
def findByEmail(email: String): Option[User] = {
val query = for {
u <- Users if u.email === email
} yield u
val f: Future[Option[User]] = db.run(query.result).map(_.headOption)
val result = Await.result(f, 30 seconds)
println(result.isDefined)
result
}
def authenticate(username: String, password: String): Future[Option[User]] = {
val query = db.run(Users.filter(_.email === username).result.map(_.headOption.filter(user => password.isBcrypted(user.password)))).map(_.headOption)
query
}
private class UsersTable(tag: Tag) extends Table[User](tag, "users") {
def uid = column[Int]("uid", O.PrimaryKey, O.AutoInc, O.SqlType("INT"))
def email = column[String]("email")
def password = column[String]("password")
def created_at = column[Timestamp]("created_at", SqlType("timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"))
def updated_at = column[Timestamp]("updated_at", SqlType("timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"))
def idx = index("email_UNIQUE", email, unique = true)
def * = (uid.?, email, password, created_at, updated_at) <> (User.tupled, User.unapply _)
}
private class UserProfileTable(tag: Tag) extends Table[UserProfile](tag, "user_profile"){
def firstname = column[String]("firstname")
def lastname = column[String]("lastname")
def gender = column[Int]("gender")
def user_id = column[Int]("user_id")
def * = (firstname, lastname, gender, user_id) <> (UserProfile.tupled, UserProfile.unapply)
def fk_user_id = foreignKey("fk_user_id", user_id, Users)(_.uid)
}
}
In the insert function, how could I add uid to user_id field of user profile table in the same function or call?
Tried the solution suggested by Pawel, but getting exception:
failed slick.SlickException: This DBMS allows only a single AutoInc column to be returned from an INSERT
Now, after trying some solutions, the insert function is like this:
def insert(user: User): Future[Int] = {
val hashPassword = user.password.bcrypt
val updatedUser = user.copy(password = hashPassword)
val insertUser = (Users returning Users.map(_.uid)) += updatedUser
def insertUserProfile(updatedUserProfile: UserProfile) = (UsersProfile returning UsersProfile.map(_.user_id)) += updatedUserProfile
val insertUserThenProfile = for {
createdUserId <- insertUser
createdUserProfileId <- insertUserProfile(UserProfile("First name", "Last name", gender = 0, user_id = createdUserId))
} yield createdUserProfileId
db.run(insertUserThenProfile.transactionally)
}
But still getting the error:
failed slick.SlickException: This DBMS allows only a single AutoInc column to be returned from an INSERT
Pawels solution should work fine, but some DBMS gives exception for not returning AUtoInc field, and for trying to return something else. You can see the note in documentation: http://slick.lightbend.com/doc/3.0.0/queries.html
Note
Many database systems only allow a single column to be returned which must be the table’s auto-incrementing primary key. If you ask for other columns a SlickException is thrown at runtime (unless the database actually supports it).
So, Now, my models are like this:
case class User(uid: Option[Int], email: String, password: String, created_at: Timestamp, updated_at: Timestamp)
case class UserProfile(upid: Option[Int], firstname: String, lastname: String, gender: Int, user_id: Int)
And the Table class:
private class UserProfileTable(tag: Tag) extends Table[UserProfile](tag, "user_profile"){
def upid= column[Int]("upid", O.PrimaryKey, O.AutoInc, O.SqlType("INT"), O.Default(0))
def firstname = column[String]("firstname")
def lastname = column[String]("lastname")
def gender = column[Int]("gender")
def user_id = column[Int]("user_id")
def * = (upid.?, firstname, lastname, gender, user_id) <> (UserProfile.tupled, UserProfile.unapply)
def fk_user_id = foreignKey("fk_user_id", user_id, Users)(_.uid)
}
And finally the insert method:
def insert(user: User): Future[Int] = {
val hashPassword = user.password.bcrypt
val updatedUser = user.copy(password = hashPassword)
val insertUser = (Users returning Users.map(_.uid)) += updatedUser
def insertUserProfile(updatedUserProfile: UserProfile) = (UsersProfile returning UsersProfile.map(_.upid)) += updatedUserProfile
val insertUserThenProfile = for {
createdUserId <- insertUser
createdUserProfileId <- insertUserProfile(UserProfile(Some(0), "First name", "Last name", gender = 0, user_id = createdUserId))
} yield createdUserProfileId
db.run(insertUserThenProfile.transactionally)
}
I don't know how are you planning to provide values for UserProfile (it's up to you, maybe an additional parameter in the insert method), but I would try something like this:
def insert(user: User): Future[UserProfile] = {
val hashPassword = user.password.bcrypt
val updatedUser = user.copy(password = hashPassword)
val insertUser = (Users returning Users.map(_.uid)) += updatedUser
def insertUserProfile(updatedUserProfile: UserProfile) = (UsersProfile returning UsersProfile.map(_.user_id)) += updatedUserProfile
val insertUserThenProfile = for {
createdUserId <- insertUser
createdUserProfileId <- insertUserProfile(UserProfile("First name", "Last name", gender = 0, user_id = createdUserId))
} yield createdUserProfileId
db.run(insertUserThenProfile.transactionally)
}