Correlated subqueries

[Peter Brant]

Hi Max (and all),

I was wondering if anybody had done any thinking on how Squeryl could
support correlated subqueries. It seems like it shouldn't too hard to
add support for them, but I was a little uncertain how best to provide
access to the outer query from the subquery. They do have the
StatementWriter in common, but I wasn't sure this was an appropriate
place to track query/AST nesting. Does that sound like a reasonable
approach?

Thanks,

Pete

[Maxime Lévesque]

The 1st query described here : 

  http://en.wikipedia.org/wiki/Correlated_subquery

could be implemented in a very similar way as the IN operator.

is this what you have in mind ?

[Peter Brant]

At least initially I was just thinking of support for them in the
context of an EXISTS (where they're pretty much essential) or IN
expression vs. generalized support for subqueries as values.

Right now

from(songs, artists)((s,a) =>
where(
s.interpretId in from(artists)(a => where(s.id === 123 and
a.firstName === "Poncho") select(a.id))
)
select(s)
orderBy(s.title asc)
).distinct

blows up with

Exception in thread "main" java.lang.RuntimeException: could not find
the target of : 'FieldSelectElement:Song9.id
at scala.Predef$.error(Predef.scala:58)
at org.squeryl.dsl.ast.ExportedSelectElement$$anonfun$target$1.apply(SelectElement.scala:356)
at org.squeryl.dsl.ast.ExportedSelectElement$$anonfun$target$1.apply(SelectElement.scala:356)
at scala.Option.getOrElse(Option.scala:104)
at org.squeryl.dsl.ast.ExportedSelectElement.target(SelectElement.scala:356)
at org.squeryl.dsl.ast.ExportedSelectElement.alias(SelectElement.scala:320)
at org.squeryl.dsl.ast.SelectElementReference.doWrite(SelectElement.scala:284)
at org.squeryl.dsl.ast.ExpressionNode$class.write(ExpressionNode.scala:37)
at org.squeryl.dsl.ast.SelectElementReference.write(SelectElement.scala:246)
at org.squeryl.dsl.ast.BinaryOperatorNodeLogicalBoolean.doWrite(ExpressionNode.scala:122)
,,,,

Pete

2011/3/23 Maxime Lévesque <maxime....@gmail.com>:

[Maxime Lévesque]

 If I remember well, they exist for update statements (not in front of a git repo right now...)

the example you show looks like a bug, cause I use in (subquery) all  over the place...

can you submit a reproduction ? 

[Peter Brant]

OK, thanks, I'll do that. IN with a subquery works fine as long it
doesn't contain any references to the outer query. For example,

from(songs, artists)((s,a) =>
where(
s.interpretId in from(artists)(a => where(a.firstName ===

"Poncho") select(a.id))
)
select(s)
orderBy(s.title asc)
).distinct

works fine.

I looked into this a bit yesterday and I think the problem is that the
inner query needs to be aware of the context in which it's being used
when generating the output SQL. It isn't currently. Alternatively,
maybe the necessary context could be pulled in when creating the AST
node. It does look like it's fairly close to working though (e.g.
aliases are being resolved correctly).

[Maxime Lévesque]

 Thanks for looking into this, I'm in Istanbul right now taking drum lessons,

so my time is a bit scarse, but I can still answer questons !

[Peter Brant]

Thanks!

(Enjoy Istanbul. It's an impressive city.)

Pete

2011/3/24 Maxime Lévesque <maxime....@gmail.com>: