Quantcast

GORM criteria: how to revert to old "left outer join" behaviour for a query?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

GORM criteria: how to revert to old "left outer join" behaviour for a query?

Bob Brown
I am maintaining a 1.1.1 application.

The following works in 1.1.1:

///
        def boards = Board.withCriteria {
            eq('yearmonth', yearmonth)
            isNotNull('practice')

            or {
                for (pocket in 6..40)
                    and {
                        eq("pocket${pocket}", brochure)
                        "pocket${pocket}" {
                            eq('type', 'pocket')
                        }
                    }
                for (mag in 1..2) {
                    and {
                        eq("magazine${mag}", brochure)
                        "magazine${mag}" {
                            eq('type', 'magazine')
                        }
                    }
                }
            }
        }
///

(a 'board' has a number of 'pockets' [named
pocket(6..40)/magazine(1..2)]...the query finds any board that has any
pocket associated with a given 'brochure' [ensuring the correct type]
instance).

It does not work with 2.0.4 or 2.1.0-RC2: no error but the query retrieves
nothing.

Looking at the SQL, I see that 1.1.1 generates "Left Outer Join", whereas
2.x uses "inner Join." This is mentioned in the docs.

My Question:

Can I force this query to use the old "left outer join" behaviour?

I have searched (eg
http://www.intelligrape.com/blog/2011/11/01/criteria-query-with-left-outer-j
oin/,
http://grails.1312388.n4.nabble.com/Left-Join-alias-in-Criteria-for-Grails-2
-0-NEED-HELP-td4222029.html), which mention, eg:

///
List blogStats = Blog.createCriteria().list {
      createAlias('comments', 'c', CriteriaSpecification.LEFT_JOIN)
      projections {
                         groupProperty('id')
                groupProperty('title')
          count('c.id')
      }
}
///

I am not sure how to adopt this in my query...my GORM-fu is at its limit
here...

Is there anyone out there who can assist?

Is there a better way to do what I am doing?

Suggestions/thoughts gratefully accepted.

Cheers,

BOB


---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: GORM criteria: how to revert to old "left outer join" behaviour for a query?

Bob Brown
Seems like my GORM-fu is better than I realised.

After working the problem a bit more, I have:

///
        def boards = Board.withCriteria {
            eq('yearmonth', yearmonth)
            isNotNull('practice')

            or {
                for (pocket in 6..40)
                    and {
                        createAlias("pocket${pocket}".toString(),
"p${pocket}".toString(), CriteriaSpecification.LEFT_JOIN)
                        eq("pocket${pocket}", brochure)
                        eq("p${pocket}.type", 'pocket')
                    }
                for (mag in 1..2) {
                    and {
                        createAlias("magazine${mag}".toString(),
"m${mag}".toString(), CriteriaSpecification.LEFT_JOIN)
                        eq("magazine${mag}", brochure)
                    }
                }
            }
        }
///


This gives the appropriate behaviour, so I am happy.

Cheers,

BOB

> -----Original Message-----
> From: Bob Brown [mailto:[hidden email]]
> Sent: Friday, 15 June 2012 10:48 AM
> To: [hidden email]
> Subject: [grails-user] GORM criteria: how to revert to old "left outer
join"

> behaviour for a query?
>
> I am maintaining a 1.1.1 application.
>
> The following works in 1.1.1:
>
> ///
>         def boards = Board.withCriteria {
>             eq('yearmonth', yearmonth)
>             isNotNull('practice')
>
>             or {
>                 for (pocket in 6..40)
>                     and {
>                         eq("pocket${pocket}", brochure)
>                         "pocket${pocket}" {
>                             eq('type', 'pocket')
>                         }
>                     }
>                 for (mag in 1..2) {
>                     and {
>                         eq("magazine${mag}", brochure)
>                         "magazine${mag}" {
>                             eq('type', 'magazine')
>                         }
>                     }
>                 }
>             }
>         }
> ///
>
> (a 'board' has a number of 'pockets' [named
> pocket(6..40)/magazine(1..2)]...the query finds any board that has any
> pocket associated with a given 'brochure' [ensuring the correct type]
> instance).
>
> It does not work with 2.0.4 or 2.1.0-RC2: no error but the query retrieves
> nothing.
>
> Looking at the SQL, I see that 1.1.1 generates "Left Outer Join", whereas
2.x

> uses "inner Join." This is mentioned in the docs.
>
> My Question:
>
> Can I force this query to use the old "left outer join" behaviour?
>
> I have searched (eg
> http://www.intelligrape.com/blog/2011/11/01/criteria-query-with-left-
> outer-j
> oin/,
>
http://grails.1312388.n4.nabble.com/Left-Join-alias-in-Criteria-for-Grails-2

> -0-NEED-HELP-td4222029.html), which mention, eg:
>
> ///
> List blogStats = Blog.createCriteria().list {
>       createAlias('comments', 'c', CriteriaSpecification.LEFT_JOIN)
>       projections {
>         groupProperty('id')
>                 groupProperty('title')
>           count('c.id')
>       }
> }
> ///
>
> I am not sure how to adopt this in my query...my GORM-fu is at its limit
> here...
>
> Is there anyone out there who can assist?
>
> Is there a better way to do what I am doing?
>
> Suggestions/thoughts gratefully accepted.
>
> Cheers,
>
> BOB
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>     http://xircles.codehaus.org/manage_email
>



---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Loading...