1

I've recently started evaluating Squeryl ORM and it looks great but finally I came to an SQL query that seems to be problematic to "build" with Squeryl.

I'd like to get all products that have a quantity which is higher than the average quantity of all products. In SQL, this would look like

SELECT * FROM product p WHERE p.quantity > (SELECT AVG(quantity) FROM product)

I already found out that Squeryl supports in(), exists() and notExists() in a where-clause subquery. But since Squeryl also supports the avg aggregation, I hoped that something like this would be possible:

val avgQuantity = from(products)(p => compute(avg(p.quantity)))
val oftenOccuringProducts = from(products)(p => where(p.quantity gt avgQuantity) select(p))

Sadly, this raises:

type mismatch; found : org.squeryl.Query[org.squeryl.dsl.Measures[Option[org.squeryl.PrimitiveTypeMode.FloatType]]] 
required: org.squeryl.dsl.NumericalExpression[?]

Of course, I'm able to do that in 2 queries (first, obtain the average quantity, then use it in a second query) but I'm wondering if it is possible to do it with one query including a subquery.

Is this impossible with Squeryl or am I just missing something?

I'd appreciate any help if this is possible or not, and if, how.

Thanks,

Sebastian

sebastian_oe
  • 7,186
  • 2
  • 18
  • 20

1 Answers1

1

Per the same discussion the mailing list, a fix was pushed to the Squeryl master branch and will be included in the next release.

Tomer Gabel
  • 4,104
  • 1
  • 33
  • 37
  • Could you please post a short snippet how the query given by sebastian_oe will look like? – Malte Schwerhoff Jul 03 '12 at 09:00
  • 2
    Hi, I just tried out the fix from the project's master branch and it's working as expected: `val avgQuantity: org.squeryl.Query[org.squeryl.dsl.Measures[Option[Double]]] = from(products)(p => compute(avg(p.quantity)))` which you can then use in `val oftenOccuringProducts = from(products)(p => where(p.quantity gt avgQuantity) select(p))`. This creates the query as 1 SQL statement, as expected. (Note that I added the type explicitly to the 1st statement to make it clearer.) – sebastian_oe Jul 03 '12 at 18:57