Slick Query Optimization

Question

I have the following Scala/Slick/PlayFramework code:

class UserRepository @Inject()(dbConfigProvider: DatabaseConfigProvider, loginInfoRepository: LoginInfoRepository) extends BaseRepository[UserTable, User](TableQuery[UserTable], dbConfigProvider.get[JdbcProfile].db) with UserQuery {

  val db = dbConfigProvider.get[JdbcProfile].db
  def findUserByProviderIdAndKey(providerId: String, providerKey: String): Future[User] = {
    for {
      maybeLoginInfo <- loginInfoRepository.findByProviderIdAndProviderKey(providerId, providerKey)
      maybeUser <- db.run(query.filter(_.loginId === maybeLoginInfo.loginId).result.head) //query refers to the user table with a loginId column
    } yield maybeUser

  }

Which queries a user table and a login_info table. The user table has a foreign key into login_info table.

Currently, the findByProviderIdAndProviderKey method returns a Future[LoginInfo]. However, I would like to change it to return a Future[Option[LoginInfo]] for user id's that don't exist. It can be easily changed in the findByProviderIdAndProviderKey method, but I'm not sure how the change for comprehension to deal with an option value.

I guess I could to something like below (needs refactoring):

    class UserRepository @Inject()(dbConfigProvider: DatabaseConfigProvider, loginInfoRepository: LoginInfoRepository) extends BaseRepository[UserTable, User](TableQuery[UserTable], dbConfigProvider.get[JdbcProfile].db) with UserQuery {

      val db = dbConfigProvider.get[JdbcProfile].db
      def findUserByProviderIdAndKey(providerId: String, providerKey: String): Future[User] = {
       loginInfoRepository.findByProviderIdAndProviderKey(providerId, providerKey).map{
       case (Some(x)) => db.run(query.fitler(_.loginId == x.loginInd).result.headOption)
       case None=> Option(None)
}

Now, I have the following questions:

1. How would you deal with the query not returning values for a given query? For instance, if the login information doesn't exist in the table? Am I doing it the right way? Or is there a better way?

2. I would have thought that slick caters for Option values automatically in joins, but seems like it does not. Why are Option values not lifted?

3. Would the above be translated into two SQL queries and database calls? If yes, how do I re-structure it so it only issues one database call with a proper join?

Thanks in advance!

Answer 1

Since you only posted part of your code, I will try to construct an example based on your question.

According to your question loginInfoRepository.findByProviderIdAndProviderKey returns a Future[LoginInfo], that means you already call db.run within that method. So to answer point 3: Yes, since you call db.run twice, it results in two roundtrips to your database.

Slicks queries are composable, so what you probably want to do is, use the query of findByProviderIdAndProviderKey within findUserByProviderIdAndKey and not the result:

def findUserByProviderIdAndKey(providerId: String, providerKey: String): Future[Option[User]] = {
    val userQuery = for {
      loginInfo <- loginInfoQuery if loginInfo.providerId === providerId && loginInfo.providerKey === providerKey
      user <- query if user.loginId === loginInfo.loginId
    } yield user

    db.run( userQuery.result.headOption )
}

The first line in the for comprehension queries all LoginInfos with the given parameters (I guessed your query and field names). The second line takes those LoginInfos and queries Users with the given loginId.

Now if the loginInfoQuery does not return anything, the second line will never be executed (the nature of the for comprehension) and slick will return nothing. If the first line returns something, but there is no User with the given loginId, slick will also return nothing. Only if both lines return something, slick will actually return values.

To return one option from the result set your use userQuery.result.headOption. You should never use userQuery.result.head unless you are 100% sure, that something will be returned (which you usually never can be).

This query will only hit the database once and will probably result in a joined query ( you would need to look at the created statement, to be sure ).