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")
Favourite
? – sarveshseri