my project have next tables: Posts, Users, comments. Post one-to-many comments, Post many-to-one users. I want to get All posts with comments and user and order by comments count. First problem was uncorrect limit, but I fixed this by sub-queries. But how can I sort posts by count queries ?
This is my DSL operation in scalikeJDBC:
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
}
I know how to do it in 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";
How can i order by count(v.voteId) in DSL ? .orderBy(x(count(v.voteId))) not working
