CODEWITHSUNDEEP
scalaormsubquerysqueryl
sebastian_oe
sebastian_oe

Reputation: 7340

Using gt, gte, etc. in WHERE subquery in Squeryl ORM

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

Upvotes: 1

Views: 591

Answers (1)

Tomer Gabel
Tomer Gabel

Reputation: 4112

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

Upvotes: 1

Related Questions