SLICK: How to use query result in another query?

Question

I'd like to perform something like the following:

I'd like to return a list of users sorted first by who the user is "following", second by some additional point score. The following code below which I wrote however doesn't work because the funder is the lifted Slick type and therefore is never found in the List.

        //The following represents the query for only funders who we are following
        val following_funders:  List[User]  = (
            for {
          funder <- all_funders
          f <- follows if f.followerId === id //get all the current users follower objects
          if f.followeeId === funder.id
        } yield funder
        ).list

      val all_funders_sorted = for {
        funder <- all_funders
        following_funder =  following_funders contains funder
      } yield (funder, following_funder)
      //sort the funders by whether or not they are following the funder and then map it to only the funders (i.e. remove the boolean)
      all_funders_sorted.sortBy(_._2.desc).sortBy(_._1.score.desc).map( x => x._1 )

All help appreciated!

cvogt cvogt · Accepted Answer · 2014-04-22T10:06:44

You need to work with ids (i.e. primary keys) in Slick. That's how objects are uniquely identified on the db side. You do not need to execute the first query. You can use it as a component of your second without executing it first using the in operator:

    //The following represents the query for only funders who we are following
    val following_funders_ids = (
        for {
      funder <- all_funders
      f <- follows if f.followerId === id //get all the current users follower objects
      if f.followeeId === funder.id
    } yield funder.id

  val all_funders_sorted = for {
    funder <- all_funders
    following_funder = funder.id in following_funders_ids
  } yield (funder, following_funder)
  //sort the funders by whether or not they are following the funder and then map it to only the funders (i.e. remove the boolean)
  all_funders_sorted.sortBy(_._1.impactPoints.desc).sortBy(_._2.desc).map( x => x._1 )

Be aware that your sort order was wrong, if you first want to sort by following. Slick translates .sortBy(_.a).sortBy(_.b) to ORDER BY B,A because that's how Scala collections work:

scala> List( (1,"b"), (2,"a") ).sortBy(_._1).sortBy(_._2)
res0: List[(Int, String)] = List((2,a), (1,b))

SLICK: How to use query result in another query?

2 Answers