CODEWITHSUNDEEP
sqlscalasubquerysql-order-byquill.io
user9920500
user9920500

Reputation: 695

Order by in Quill SQL produces Subquery

I have an employee class.

 case class Employee(name: String, age: Int, company: String)

and I write a Group-By query in Quill.

val q = quote {
     query[Employee]
     .filter(_.age == lift(100))
     .groupBy(_.company)
     .map { a =>
      val tot = a._2.size
      val totCond = (a._2.map { i =>
        if (i.name == "software") 1 else 0
      }.sum)
      (tot, totCond)
    }
}

ctx.run(q)

This query translates to 

SELECT COUNT(*), SUM(CASE WHEN x36.name = 'software' THEN 1 ELSE 0 END) FROM employee x36 
WHERE x36.age = ? GROUP BY x36.company

which is fine.

Now I want to sort the query like

  quote {
   query[Employee]
     .filter(_.age == lift(100))
     .groupBy(_.company)
     .map { a =>
       val tot = a._2.size
       val totCond = (a._2.map { i =>
         if (i.name == "software") 1 else 0
       }.sum)
       (tot, totCond)
     }.sortBy(_._1)(Ord.desc)
 }

But this translates to 

 SELECT x36._1, x36._2 FROM (SELECT COUNT(*) AS _1, SUM(CASE WHEN x36.name = 'software' THEN 1 
 ELSE 0 END) AS _2 FROM employee x36 WHERE x36.age = ? GROUP BY x36.company) AS x36 ORDER BY 
 x36._1 DESC

this appears as a subquery which does all logic and the main query which just selects the record from main query.

I want to get rid of this and do everything in only one query. Is there a way to do it ??

Upvotes: 2

Views: 450

Answers (0)

Related Questions