Mapping nested joins

[Peter Brant]

Hi Max,

It looks like the result set mapping of nested joins isn't quite
working (Assembla link below). Unrolling the joins is an effective
workaround, but it's a bit ugly (and hurts composability).

For both the failing inner and outer join test example, the underlying
problem is that the wrong ResultSetMapper is used. In particular,
ExportedSelectElement uses the mapper of the referred SelectElement.
I think it should be using "its" relation for the ResultSetMapper
instead of the referred view, but I'm getting slightly lost in the
details. Does this make sense?

This feels like something that shouldn't be that hard to fix, but I'm
having trouble seeing it.

Pete

http://www.assembla.com/spaces/squeryl/tickets/40-nested-joins-aren-t-mapped-correctly

[Maxime Lévesque]

I'm debugging this right now, I have to put my head into it to
tell you something usefull, I'll keep you posted,

meanwhile do you use this : println(aQuery.dumpAst) :
?

'QueryExpressionNode[root:101ac1c]:rsm='ResultSetMapper:ca9a2d()--
    'FieldSelectElement:A3.name
        org.squeryl.dsl.ast.FieldSelectElement$$anon$3
    'FieldSelectElement:A3.id
        org.squeryl.dsl.ast.FieldSelectElement$$anon$3
    'ExportedSelectElement:q4.B8_name,(selectElement='FieldSelectElement:B8.name)
        org.squeryl.dsl.ast.ExportedSelectElement$$anon$4
    'ExportedSelectElement:q4.B8_aId,(selectElement='FieldSelectElement:B8.aId)
        org.squeryl.dsl.ast.ExportedSelectElement$$anon$4
    'ExportedSelectElement:q4.B8_id,(selectElement='FieldSelectElement:B8.id)
        org.squeryl.dsl.ast.ExportedSelectElement$$anon$4
    'ViewExpressionNode[sample:A[113cf49]]:rsm='ResultSetMapper:4610fa($(1->A.name:java.lang.String),$(2->A.id:java.lang.Integer))--*
    'QueryExpressionNode[383244]:rsm='ResultSetMapper:16c1bce()--
        'FieldSelectElement:B8.name
            org.squeryl.dsl.ast.FieldSelectElement$$anon$3
        'FieldSelectElement:B8.aId
            org.squeryl.dsl.ast.FieldSelectElement$$anon$3
        'FieldSelectElement:B8.id
            org.squeryl.dsl.ast.FieldSelectElement$$anon$3
        'ViewExpressionNode[sample:B[1289e48]]:rsm='ResultSetMapper:12e5c94($(3->B.name:java.lang.String),$(4->B.aId:java.lang.Integer),$(5->B.id:java.lang.Integer))--*
    'BinaryOperatorNode:=
        'SelectElementReference:A3.id:java.lang.Integer
        'SelectElementReference:q4.B8_aId:java.lang.Integer

[Maxime Lévesque]

The issue of the wrong ResultSetMapper being used is fixed by this :

 https://github.com/max-l/Squeryl/commit/63be006225e9fb07f3750c08b1f1ff733f19c780


There is still an issue with your second test case :

    val q0 = from(b)( b => select(b) )

    val aQuery = join(a, q0.leftOuter) ( (a, b) =>
      select(a, b)
        on(a.id === b.map(_.aId))
    )

    checkLeftJoinQuery(aQuery)

Not sure it's a bug though (but my brain has not been caffeinated yet...), the SQL produced is :

Select
  A3.name as A3_name,
  A3.id as A3_id,
  q4.B8_name as q4_B8_name,
  q4.B8_aId as q4_B8_aId,
  q4.B8_id as q4_B8_id
From
A A3
left outer join (Select
   B8.name as B8_name,
   B8.aId as B8_aId,
   B8.id as B8_id
 From
   B B8
)  as q4 on (A3.id = q4.B8_aId)

Which returns nothing when run in the H2 console ....

ML

2011/4/14 Maxime Lévesque <maxime....@gmail.com>

[Peter Brant]

Thanks much (also for the dumpAst() mention -- much better than
stumbling around in the debugger).

Given the test data in the test class, I think aQuery and aQuery2
should return the same results. The result row is correct in any
case. When I looked at it before, it looked like the wrong
ResultSetMapper was being consulted when populating the final result
from the result set row. Since there were no result columns in the
ResultSetMapper it was using, isNoneInOuterJoin() was returning true.

Pete

Select
A3.name as A3_name,
A3.id as A3_id,
q4.B8_name as q4_B8_name,
q4.B8_aId as q4_B8_aId,
q4.B8_id as q4_B8_id
From
A A3
left outer join (Select
B8.name as B8_name,
B8.aId as B8_aId,
B8.id as B8_id
From
B B8
) as q4 on (A3.id = q4.B8_aId)

ResultSetRow:[a one:String,1:Integer,b one:String,1:Integer,1:Integer]
darn... no go

2011/4/15 Maxime Lévesque <maxime....@gmail.com>:

[Maxime Lévesque]

Just checked in a fix in master and inculded a test case for it in the main suite.

 https://github.com/max-l/Squeryl/commit/d53bc56b86d3203698b748637900c14fd836234f

I managed to misconfigure my Git client to mess the end of lines, so I'll have some cleaning up to do ....

ML

[Peter Brant]

Very cool. Thanks much. I guess the patch turned to be pretty small
after all (although I know from Flying Saucer the small patches are
often the hardest won...)

Pete

2011/4/18 Maxime Lévesque <maxime....@gmail.com>: