Write join query with groupby in Scala ActiveRecord

Question

I am trying to write a specific query in scala Active record. But it always returns nothing. I have read the wiki on the github page but it does not contain a lot of info on it. The query I am trying to write is

SELECT e.name, e.id, COUNT(pt.pass_id) as pass_count, e.start_date, e.total_passes_to_offer
FROM events e inner join passes p on e.id = p.event_id inner join pass_tickets pt on p.id = pt.pass_id where e.partner_id = 198 group by e.name, e.id

What I have tried is

Event.joins[Pass, PassTicket](
                (event, pass, passTicket) => (event.id === pass.eventId, pass.id === passTicket.passId)
            ).where(
                (event, _, _) => event.partnerId === partnerId
            ).select(
                (event, pass, _) => (event.name, event.id, PassTicket.where(_.passId === pass.id).count, event.startDate, event.totalPassesToOffer)
            ).groupBy( data => data._2)

But first, the return type becomes a map, not a list. And second when executed, it doesnt return anything even though the data exists. When running the SQL query directly against the database, the expected results return.

Answer 1

scala-activerecord dsl is based on squeryl, so when figuring out a complicated query we could drop down to squeryl level and use its statement facility for pretty printing an SQL statment. This way we could iteratively tweek the dsl until we get the desired SQL statement. For example, say we have the following schema:

object Tables extends ActiveRecordTables {
  val persons = table[Person]
  val tickets = table[Ticket]
}

case class Person(name: String, email: String, age: Int) extends ActiveRecord
case class Ticket(price: Float, priority: Boolean) extends ActiveRecord {
  lazy val person = belongsTo[Person]
}

object Person extends ActiveRecordCompanion[Person]
object Ticket extends ActiveRecordCompanion[Ticket]

and we drop to squeryl dsl to define the following query

  val query =
    dsl.join(Person.toQuery, Ticket.toQuery)((person, ticket) =>
      groupBy(person.name, person.age)
      compute(count(ticket.id))
      on(person.id === ticket.id)
    )

then we could pretty print the statement using

println(Person.inTransaction(query.statement))

which outputs actual SQL statement

Select
  q1.people6_name as g0,
  q1.people6_age as g1,
  count(q7.tickets11_id) as c0
From
(Select
   people6.name as people6_name,
   people6.email as people6_email,
   people6.age as people6_age,
   people6.id as people6_id
 From
   people people6
)  q1
 inner join (Select
   tickets11.priority as tickets11_priority,
   tickets11.price as tickets11_price,
   tickets11.id as tickets11_id
 From
   tickets tickets11
)  as q7 on (q1.people6_id = q7.tickets11_id)
Group By
  q1.people6_name,
  q1.people6_age

Once we figure out the correct dsl in squeryl, then we at least know it is possible, and then we could attempt to write it also in scala-activerecord. Potential advantage of this approach is that squerly seems to have more documentation. Note what it states on Group and Aggregate Queries which should indirectly also hold true for scala-activerecord:

Squeryl diverges slightly from SQL in that aggregate functions are not allowed within a select. They are instead declared in a ‘compute’ clause which is in fact a select in disguise, since it’s arguments end up in the generated SQL’s select clause. The motivation for this design choice is to make it a bit harder to write invalid Select statements, since the DSL forces a ‘compute’ clause to either replace a select or to follow a groupBy.

From my understanding, this means we should not write PassTicket.where(_.passId === pass.id).count in the select clause.

Regarding groupBy returning a Map, we could call values.toList on it to get the list back, for example, say we have

  Person("Picard", "[email protected]", 34).save
  Person("Data", "[email protected]", 40).save
  Person("Geordi", "[email protected]", 40).save

then println(Person.groupBy(person => person.age).values.toList) should give

List(
  List(Person(Data,[email protected],40), Person(Geordi,[email protected],40)), 
  List(Person(Picard,[email protected],34))
)