CODEWITHSUNDEEP
sqlscalaslick
Łukasz Biały
Łukasz Biały

Reputation: 470

Scala Slick 3.1.0 - counting columns from 2 tables in left join query

I am trying to construct a double count left join query in Slick 3.1 similar to this:

SELECT 
  COUNT(p.id) AS replies,
  COUNT(f.id) AS images
  FROM posts AS p
  LEFT JOIN files AS f
  ON p.id = f.post_id
  WHERE thread = :thread_id

Join part of the query is quite simple and looks like this:

val joinQ = (threadId: Rep[Long]) => 
   (postDAO.posts joinLeft fileRecordDAO.fileRecords on (_.id === _.postId))
     .filter(_._1.thread === threadId)

Using joinQ(1L).map(_._1.id).length generates COUNT(1) which counts all rows - not the result I want to obtain. Using joinQ(1L).map(_._1.id).countDistinct generates COUNT(DISTINCT p.id) which is somewhat what I'm looking for, but trying to do two of these generates this monstrosity:

select x2.x3, x4.x5 
  from (select count(distinct x6.`id`) as x3 
  from `posts` x6 
  left outer join `files` x7 on x6.`id` = x7.`post_id` 
  where x6.`thread` = 1) x2,
  (select 
    count(distinct (case when (x8.`id` is null) then null else 1 end)) as x5 
    from `posts` x9 
    left outer join `files` x8 on x9.`id` = x8.`post_id` 
    where x9.`thread` = 1) x4

Here's the double countDistinct code:

val q = (threadId: Rep[Long]) => {
  val join = joinQ(threadId)
  val q1 = join.map(_._1.id).countDistinct // map arg type is 
  val q2 = join.map(_._2).countDistinct    // (Post, Rep[Option[FileRecord]])
  (q1, q2)
}

Any ideas? :)

Upvotes: 2

Views: 1110

Answers (1)

Igor Mielientiev
Igor Mielientiev

Reputation: 586

Count is aggregation function, so you also need grouping by some field (e.x. id). Try next query: 

def joinQ(threadId: Long) = (postDAO.posts joinLeft fileRecordDAO.fileRecords on (_.id === _.postId))
 .filter(_._1.thread === threadId)

val q = (threadId: Long) => {
  joinQ(threadId).groupBy(_._1.id).map {
    case (id, qry) => (id, qry.map(_._1.id).countDistinct, qry.map(_._2.map(_.id)).countDistinct)
  }
}

It generates next sql: 

SELECT x2.`id`, 
       Count(DISTINCT x2.`id`), 
       Count(DISTINCT x3.`id`) 
FROM   `posts` x2 
       LEFT OUTER JOIN `files` x3 
                    ON x2.`id` = x3.`post_id` 
WHERE  x2.`thread` = 10 
GROUP  BY x2.`id`

Upvotes: 0

Related Questions