example.sql

SELECT * FROM blog.POSTS p 
LEFT JOIN BLOG.POST_VOTES pv  
ON p.POST_ID = pv.POST_ID
JOIN (SELECT p2.POST_ID as id, COUNT(pv2.VOTE_ID) "count" FROM BLOG.POSTS p2 LEFT 
      JOIN BLOG.POST_VOTES pv2  ON p2.POST_ID = pv2.POST_ID GROUP BY p2.POST_ID) r 
ON p.POST_ID = r.id ORDER BY r."count";

scalikeJDBC.scala

   val x = SubQuery.syntax("x").include(v, p, u)
      DB readOnly { implicit session =>
        withSQL {
          select.all(p,u,v)
            .from(Post as p)
            .leftJoin(Vote as v)
            .on(p.postId, v.postId)
            .join(User as u)
            .on(p.userId, u.userId)
            .join{
              select(p.result.*,count(v.voteId))
                .from(Post as p)
                .leftJoin(Vote as v)
                .on(p.postId, v.postId)
                .join(User as u)
                .on(p.userId, u.userId)
                .groupBy(p.postId)
                .as(x)
            }.on(x(p).postId, p.postId)
        }
          .one(Post(p.resultName, u.resultName))
          .toMany(
            rs => Vote.opt(v)(rs),

          ).map{
          (p:Post,v:Seq[Vote])=> {
            p.copy(votes = v)
          }
        }.list.apply
      }