CODEWITHSUNDEEP
sqlscalaslick-3.0
IUnknown
IUnknown

Reputation: 2636

multiple aggregates and subquery in slick 3.1

I am trying to translate this sql into a slick 3.1 style collection query (single call). This sql (postgres) returns what I am looking for:

select 
    minDate.min as lastModified, 
    (select count("id") from "Items" where "orderId" = 1) as totalItemCount, 
    (select count("id") from "Items" where "orderId" = 1 and "dateModified" >= minDate.min) as addedCount
from 
    (select min("dateModified") as "min" from "Items" where "orderId" = 1 and "state" = 'new') as minDate

Returns: for a specified set of Items (from orderId), returns:

But after many attempts, I can't figure out how to translate this to a single slick-style query

Upvotes: 2

Views: 1530

Answers (1)

nikiforo
nikiforo

Reputation: 424

This codes

import scala.slick.driver.PostgresDriver

case class Item(id: Int, orderId: Int, state: String, dateModified: Int)

object SlickComplexQuery {
  def main(args: Array[String]) = {
    val driver = PostgresDriver

    import driver.simple._

    class ItemsTable(tag: Tag) extends Table[Item](tag, "Items"){
      def id = column[Int]("id")
      def orderId = column[Int]("orderId")
      def state = column[String]("state")
      def dateModified = column[Int]("dateModified")

      def * = (id, orderId, state, dateModified) <> (Item.tupled, Item.unapply)
    }

    val items = TableQuery[ItemsTable]

    val query1 = items
      .filter(i => i.orderId === 1 && i.state === "new")
      .map(_.dateModified)
      .min

    val query2 = items
      .filter(_.orderId === 1)
      .map(_.id)
      .length

    val query3 = items
      .filter(i => i.orderId === 1 && i.dateModified >= query1)
      .map(_.id)
      .length

    val query = Query(query1, query2, query3)

results in such query:

select x2.x3, x4.x5, x6.x7
  from (select min(x8.x9) as x3
          from (select x10."dateModified" as x9
                  from "Items" x10
                  where (x10."orderId" = 1) and (x10."state" = 'new')) x8) x2,
       (select count(1) as x5
          from (select x11."id" as x12
                  from "Items" x11
                  where x11."orderId" = 1) x13) x4,
       (select count(1) as x7
          from (select x14."id" as x15
                  from "Items" x14, (select min(x16.x17) as x18
                                      from (select x19."dateModified" as x17
                                              from "Items" x19
                                              where (x19."orderId" = 1) and (x19."state" = 'new')) x16) x20
                  where (x14."orderId" = 1) and (x14."dateModified" >= x20.x18)) x21) x6

This query is much alike yours, slick 2.0 was used.

Upvotes: 2

Related Questions