squeryl: how to use oneToMany relation for constructing queries

Question

I have a liftweb application in which I use Record/Squeryl as an ORM framework. Here is my schema:

object AppSchema extends Schema {
  import org.squeryl.dsl.OneToManyRelation

  val groups = table[Group]("groups")
  val domains = table[Domain]("domains")

  val groupToDomains =
    oneToManyRelation(groups, domains).
      via((g, d) => g.id === d.groupId)
}

object Group extends Group with MetaRecord[Group] with Loggable {

}

class Group extends Record[Group] with KeyedRecord[Long] with Loggable {
  override def meta = Group
  @Column(name = "id") override val idField = new LongField(this)
  val name = new StringField(this, 700, "")
  @Column(name = "date_added") val createdAt = new DateTimeField(this)

  lazy val domains: OneToMany[Domain] = AppSchema.groupToDomains.left(this)
}

object Domain extends Domain with MetaRecord[Domain] with Loggable {

}

class Domain extends Record[Domain] with KeyedRecord[Long] {
  override def meta = Domain

  @Column(name = "id") override val idField = new LongField(this)
  @Column(name = "group_id") val groupId = new LongField(this)
  val name = new StringField(this, 700, "")
  @Column(name = "date_added") val createdAt = new DateTimeField(this)

  lazy val group: ManyToOne[Group] = AppSchema.groupToDomains.right(this)  
}

I'm trying to implement a function which counts domains in a group:

def countDomainsInGroup(group: Group): Long = {
  val l = from(AppSchema.domains)(d => where(d.groupId === group.id) compute(count()))
  println("Count domains: " + l.statement)
  l.single.measures
}

This generates the following SQL:

Select
  count(*) as c0
From
  domains domains1
Where
  (domains1.group_id = 45)

Now everything is OK with that, but I suppose there should be a shortcut, as we already have group.domains, which has the proper WHERE statement defined. But when I try to use it like this:

def countDomainsInGroup(group: Group): Long = {
  val l = from(group.domains)(d => compute(count()))
  println("Count domains: " + l.statement)
  l.single.measures
}

I get the following SQL generated:

Select
  count(*) as c0
From
  (Select
     domains8.name as domains8_name,
     domains8.id as domains8_id,
     domains8.group_id as domains8_group_id,
     domains8.date_added as domains8_date_added,
   From
     domains domains8
   Where
     (45 = domains8.group_id)
  )  q1

As you can see, a subquery is generated here, which I don't need. What am I doing wrong?

score 0 · Accepted Answer · answered Nov 27 '13 at 17:19

0

If you take a closer look at OneToMany / ManyToOne, you'll see that they extend Query. They are basically a convenience for executing a query that retrieves all of the objects related to a parent or child by primary key. If you look at it that way, the SQL you see isn't surprising. You are asking for the count of the results of the query, and Squeryl is purposely pretty literal when outputting SQL so that you can optimize things yourself without worrying about rewriting. If a subquery isn't optimal, there is no reason why you would have to compute your count on the relation.

answered Nov 27 '13 at 17:19

Dave Whittaker

3,102
13
14

Yes, I can understand this. What I cannot understand, is how to count domains in group without a subquery, just like that: Select count(*) From domains domains8 Where (45 = domains8.group_id) – Uniqus Nov 30 '13 at 20:26
But you have the proper query for that above. Your countDomainsInGroup functions is correct, and as you remarked, it outputs the SQL you are looking for. – Dave Whittaker Dec 01 '13 at 15:41
But this query is not optimal. That's not what I need. For now I implemented `Group.countDomains` helper method, which issues simple `select count(*) ...` query without any subqueries. And I have a lot of inter-related models in my app, so I have to implement such helper methods everywhere =/ – Uniqus Dec 06 '13 at 06:59
I think you misunderstand what I'm saying. The optimal query you want, without any subqueries would be: `from(AppSchema.domains)(d => where(d.groupId === group.id) compute(count()))`. You've already remarked that you've tried that and verified that it outputs the appropriate SQL. Squeryl is designed to be more DSL than ORM. The `OneToMany`/`ManyToOne` types are fine for quickly accessing related elements, but if you want optimized SQL you are better off leveraging Squeryl's DSL capabilities and writing a typesafe query. – Dave Whittaker Dec 06 '13 at 14:58
Ok, got it. Thank you for your explanation. I indeed expected squeryl to be ORM rather than DSL, now I see that was my misunderstanding. – Uniqus Dec 23 '13 at 10:40

squeryl: how to use oneToMany relation for constructing queries

1 Answers1