Slick 3: count "OR NULL" with leftJoin and groupBy

Question

I’m creating a microblog system. Like Twitter, I want to retrieve post data with “(1) Like Count” and “(2) Whether the user has already liked it?”.

My DB Schema:
post (id: Int, title: String)
favorite (id: Int, post_id: Int, user_id: Int)

I can get what I want in SQL (assume that user’s id is 1).

SELECT post.id, post.title, COUNT(favorite.id), COUNT(favorite.user_id = 1 OR NULL) FROM post 
  LEFT JOIN favorite ON post.id = favorite.post_id
  GROUP BY post.id

But with Slick 3, I don’t know how to get the same result on (2).

Post.joinLeft(Fav).on(_.id  === _.postId)
    .groupBy(_._1)
    .map { case (post, group) => (
        post,
        group.map(_._2.map(_.id)).countDefined,  // (1) this works!
        group.map(_._2.???).countDefined   // (2) ?
    )}

How can I query it?

update: Slick Schema for Fav column (I used slick-codegen)

val id: Rep[Int] = column[Int]("id", O.AutoInc, O.PrimaryKey)
val userId: Rep[Int] = column[Int]("user_id")
val postId: Rep[Int] = column[Int]("post_id")

Answer 1

You can remove the compilation error from the answer of Sarvesh Kumar Singh by changing below lines (but you will get a runtime error on slick 3.1.X and 3.2.X):

group.filter{
      case (p, f) => f.filter(_.userId === 1).isDefined
    }.length

Since the type of f is Rep[Option[Favorite]] you do not directly have f.userId available.

NOTE: That slick throws a runtime error when you try to call length function after groupBy + filter or map. You can track the issue here - https://github.com/slick/slick/issues/1355

You could instead use the below code:

Post.joinLeft(Favorite)
  .on(_.id  === _.postId)
  .groupBy(_._1)
  .map{ case (post, group) => (
    post,
    group.map(_._2.map(_.id)).countDefined,
    group.map(_._2.filter(_.userId === 1).map(_.userId)).countDefined 
    )}

Answer 2

I am not very sure if this will work, but I remember doing something similar to this earlier.

You can try this for now (assuming that favourite.user_id is Column[Option[Int]]),

Post.joinLeft(Fav)
  .on(_.id  === _.postId)
  .groupBy(_._1)
  .map({ case (post, group) => (
    post,
    group.map(_._2.map(_.id)).countDefined,
    group.filter({
      case (p, f) => f.user_id.isEmpty || f.user_id.filter(_ == 1).isDefined
    }).length
  )})