cant get where queiries to work across associations -

[will.woodman]

this is related to earlier post see : http://grails.1312388.n4.nabble.com/Grails-user-f1312389.html

 i've tried to make the example and tests easier to read.  However the results of the where query across association always seems to return the empty [] which is not what i expect and my assertions fail .  

I create three domain classes

Author which can have many books (1:m) and books which can have many readers, and read has read many books (M;N)
as follows

domain class Author :
package com.softwood.book

class Author {

        String name
        Date dateCreated
        //List books  //change set to list
       
        static hasMany = [books:Book]
       
    static constraints = {
                        name (size: 3..20)
    }
}

domain class book
package com.softwood.book

class Book {
       
        String title
        Date dateCreated
       
        static hasMany = [readBy:Reader]

        static belongsTo = [author:Author]
       
    static constraints = {
                title (size: 3..20)
    }
}

domain class Reader :
package com.softwood.book

class Reader {
       
        String name
        Date dateCreated

        static hasMany = [hasRead:Book]
       
        static belongsTo = [Book]
       
    static constraints = {
    }
}


here are two integration tests both of which fail to return the expected results.

...
        @Test
        void testReadersQuery()
        {
                def book1 = new Book (title:"war of the worlds")
                def book2 = new Book (title: "famous five")
                def book3 = new Book (title: "goblet of fire")
               
                def will = new Reader (name:"william")
                def maz = new Reader (name:"marian")
               
                book1.addToReadBy (will)
                book2.addToReadBy (maz)
                book3.addToReadBy (will)
                book3.addToReadBy (maz)
               
                book1.save()
                book2.save()
                book3.save ()
               
                assert book1.readBy.size() == 1
                assert book2.readBy.size() == 1
                assert book3.readBy.size() == 2
                assert will.hasRead.size() ==2
                assert maz.hasRead.size() == 2
               
                //query m:n relationship
                def bQuery = Book.where {
                        readBy{name == "william"}
                }
               
                def resBook = bQuery.findAll()
                assert resBook != []   //fails here
                assert resBook.size() == 2

               
        }
       
        @Test
        void testAuthorsQuery ()
        {
                def book1 = new Book (title:"war of the worlds")
                def book2 = new Book (title: "famous five")
                def book3 = new Book (title: "goblet of fire")

                def unk = new Author (name:"uknown")
                unk.addToBooks (book1)
                unk.addToBooks (book2)
                unk.save()
                assertFalse unk.hasErrors()

                def jk = new Author (name: "JK Rowling")
                jk.addToBooks (book3).save()
                assertFalse jk.hasErrors()

                //query 1:m relationship
                def resUnkAuthorQuery = Author.where
                {
                        books.title == "unknown"
                }
                def resUnkAuth = resUnkAuthorQuery.findAll()
                assert resUnkAuth != []   //fails here
                assert resUnkAuth.size()  == 2

        }

having read the documentation - and tried many and varied options round the query (includin withCriteria etc ) i just cant get this to work in integration testing

is this a bug or am i doing something wrong in the syntax??

Really need some help on this - i cant even write my basic tests i need to use to show my logic works in code.  i'm at ground zero on this right now.

regards Will

[johnrengelman]

Have you tried using bQuery.list() and resUnkAuthorQuery.list() rather than findAll()?

The .where {} syntax returns a DetachedCriteria, so it doesn't hold an actual data yet (more like a proxy to the results).

If you want to do the search immediately then you should be doing:

def resBook = Book.findAll {
  readBy.name == "william"
}

At least, that's how I read the documentation: http://grails.org/doc/2.0.x/guide/GORM.html#whereQueries

John

On Mon, Feb 20, 2012 at 5:31 PM, will.woodman <[hidden email]> wrote:

this is related to earlier post see :
http://grails.1312388.n4.nabble.com/Grails-user-f1312389.html

 i've tried to make the example and tests easier to read.  However the
results of the where query across association always seems to return the
empty [] which is not what i expect and my assertions fail .

I create three domain classes

Author which can have many books (1:m) and books which can have many
readers, and read has read many books (M;N)
as follows

*domain class Author :*
package com.softwood.book

class Author {

       String  name
       Date    dateCreated
       //List  books  //change set to list

       static hasMany = [books:Book]

   static constraints = {
                       name (size: 3..20)
   }
}

*domain class book *
package com.softwood.book

class Book {

       String  title
       Date    dateCreated

       static hasMany = [readBy:Reader]

       static belongsTo = [author:Author]

   static constraints = {
               title (size: 3..20)
   }
}

*domain class Reader : *
package com.softwood.book

class Reader {

       String name
       Date dateCreated

       static hasMany = [hasRead:Book]

       static belongsTo = [Book]

   static constraints = {
   }
}


here are two integration tests both of which fail to return the expected
results.

...
       @Test
       void testReadersQuery()
       {
               def book1 = new Book (title:"war of the worlds")
               def book2 = new Book (title: "famous five")
               def book3 = new Book (title: "goblet of fire")

               def will = new Reader (name:"william")
               def maz = new Reader (name:"marian")

               book1.addToReadBy (will)
               book2.addToReadBy (maz)
               book3.addToReadBy (will)
               book3.addToReadBy (maz)

               book1.save()
               book2.save()
               book3.save ()

               assert book1.readBy.size() == 1
               assert book2.readBy.size() == 1
               assert book3.readBy.size() == 2
               assert will.hasRead.size() ==2
               assert maz.hasRead.size() == 2

               //query m:n relationship
               def bQuery = Book.where {
                       readBy{name == "william"}
               }

               def resBook = bQuery.findAll()
               *assert resBook != []   *//fails here
               assert resBook.size() == 2


       }

       @Test
       void testAuthorsQuery ()
       {
               def book1 = new Book (title:"war of the worlds")
               def book2 = new Book (title: "famous five")
               def book3 = new Book (title: "goblet of fire")

               def unk = new Author (name:"uknown")
               unk.addToBooks (book1)
               unk.addToBooks (book2)
               unk.save()
               assertFalse unk.hasErrors()

               def jk = new Author (name: "JK Rowling")
               jk.addToBooks (book3).save()
               assertFalse jk.hasErrors()

               //query 1:m relationship
               def resUnkAuthorQuery = Author.where
               {
                       books.title == "unknown"
               }
               def resUnkAuth = resUnkAuthorQuery.findAll()
               *assert resUnkAuth != []   *//fails here
               assert resUnkAuth.size()  == 2

       }

having read the documentation - and tried many and varied options round the
query (includin withCriteria etc ) i just cant get this to work in
integration testing

is this a bug or am i doing something wrong in the syntax??

Really need some help on this - i cant even write my basic tests i need to
use to show my logic works in code.  i'm at ground zero on this right now.

regards Will

--
View this message in context: http://grails.1312388.n4.nabble.com/cant-get-where-queiries-to-work-across-associations-tp4405452p4405452.html
Sent from the Grails - user mailing list archive at Nabble.com.

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

   http://xircles.codehaus.org/manage_email

[will.woodman]

i'll try but i think its more invidious than that.

spent all day trying to hack this and figure out what was going on - in the console version i tried to create a Book (class in the middle so to speak ) of readers (m:n) and author (1:m)

i then printed out the getErrors () - and found it hadnt created a Book because it thinks that author attribute couldnt be null.

Hah thinks i so change my constraints for Book as follows

class Book {
       
        String title
        Date dateCreated
        //Reader[] readBy //m:n
        //Author author //1:m
       
       
        static hasMany = [readBy:Reader]

        static belongsTo = [author:Author]
       
    static constraints = {
                title (size: 3..20)
                readBy (nullable : true)
                author (nullable : true )  //constraint to ensure its nullable
    }
       
        String toString ()
        {
                "$title"
        }
}

so this starts to improve - but not fix - my Book.save() now persists and saves any readers, etc added to a book.

however i'm left with an two errors in my tests which wont go away

the first like this

@Test
        void testAuthorsQuery ()
        {
                def book1 = new Book (title:"war of the worlds")
                def book2 = new Book (title: "famous five")
                def book3 = new Book (title: "goblet of fire")

                def unk = new Author (name:"uknown")
                unk.addToBooks (book1)
                unk.addToBooks (book2)
                unk.save()
....
                //query 1:m relationship
                def resUnkAuthorQuery = Author.where
                {
                        books.title == "%unk%"
                }
                def resUnkAuth = resUnkAuthorQuery.findAll()  //returns [] not a match
                assert resUnkAuth != []
                assert resUnkAuth.size()  == 2
}

and a second test like this

        @Test
        void testFindReaderByBookQuery ()
        {
                def book1 = new Book (title:"war of the worlds")
                def book2 = new Book (title: "famous five")
                def book3 = new Book (title: "goblet of fire")
               
                def will = new Reader (name:"william")
                def maz = new Reader (name:"marian")
               
                book1.addToReadBy (will)
                book2.addToReadBy (maz)
                book3.addToReadBy (will)
                book3.addToReadBy (maz)
               
                book1.save()
                book2.save()
                book3.save ()
        ....
                def fRes = Reader.findAllByHasRead (Book.findByTitle ("war of the worlds"))
                assert fRes != null
                assert fRes.size() == 1
                       }

which fails seriously with a  and i cant figure out why its saying #1 param not being set .


| Error 2012-02-21 22:39:47,248 [main] ERROR util.JDBCExceptionReporter  - Parameter "#1" is not set; SQL statement:
select this_.id as id6_0_, this_.version as version6_0_, this_.date_created as date3_6_0_, this_.name as name6_0_ from reader this_ where this_.id=? [90012-147]
| Failure:  testFindReaderByBookQuery(com.softwood.book.AuthorIntegTests)
|  org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select this_.id as id6_0_, this_.version as version6_0_, this_.date_created as date3_6_0_, this_.name as name6_0_ from reader this_ where this_.id=?]; SQL state [90012]; error code [90012]; Parameter "#1" is not set; SQL statement:
select this_.id as id6_0_, this_.version as version6_0_, this_.date_created as date3_6_0_, this_.name as name6_0_ from reader this_ where this_.id=? [90012-147]; nested exception is org.h2.jdbc.JdbcSQLException: Parameter "#1" is not set; SQL statement:
select this_.id as id6_0_, this_.version as version6_0_, this_.date_created as date3_6_0_, this_.name as name6_0_ from reader this_ where this_.id=? [90012-147]
        at org.grails.datastore.gorm.GormStaticApi.methodMissing(GormStaticApi.groovy:108)
        at com.softwood.book.AuthorIntegTests.testFindReaderByBookQuery(AuthorIntegTests.groovy:211)
Caused by: org.h2.jdbc.JdbcSQLException: Parameter "#1" is not set; SQL statement:
select this_.id as id6_0_, this_.version as version6_0_, this_.date_created as date3_6_0_, this_.name as name6_0_ from reader this_ where this_.id=? [90012-147]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
        at org.h2.message.DbException.get(DbException.java:167)
        at org.h2.message.DbException.get(DbException.java:144)
        at org.h2.expression.Parameter.checkSet(Parameter.java:73)
        at org.h2.command.Prepared.checkParameters(Prepared.java:161)
        at org.h2.command.CommandContainer.query(CommandContainer.java:77)
        at org.h2.command.Command.executeQuery(Command.java:132)
        at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        ... 2 more

however a small improvement for days slog - you think you understand it till you try it and find you dont

any other ideas ?  changing findAll for listAll on the detached query makes no dif - the error is the same

[will.woodman]

slowly getting there - i think but not out the woodwork.

 

one of the fails turned out to be a spelling error in string -

however my last remaining error i still cant figure ..

i'm trying to query in either direction over M:N relationship. (between the Readers : Book join)


heres the last failing test

        @Test
        void testFindReaderByBookQuery ()
        {
                def book1 = new Book (title:"war of the worlds")
                def book2 = new Book (title: "famous five")
                def book3 = new Book (title: "goblet of fire")
               
                def will = new Reader (name:"william")
                def maz = new Reader (name:"marian")
               
                book1.addToReadBy (will)
                book2.addToReadBy (maz)
                book3.addToReadBy (will)
                book3.addToReadBy (maz)
               
                book1.save()
                book2.save()
                book3.save ()
               
                def jk = new Author (name: "JK Rowling")
                jk.addToBooks (book3)
                assert jk.save () != null
...
                assert Book.count() == 3
                assert Reader.count () == 2
                assert Author.count () ==1
               
                def allRes = Reader.findAll ()
                assert allRes.size() == 2
                assert allRes.collect {r -> r.name}.sort() == ["marian", "william"]
               
                def allBooks = Book.findAll ()
                assert allBooks.size () == 3
                assert allBooks.collect {b -> b.title}.sort() == ["famous five", "goblet of fire", "war of the worlds"]
               
                def queryBook4reader = Reader.where  //query many through assoc to the one side -- non owning
                {
                        hasRead.title == "goblet of fire"
                }
                def resReader = queryBook4reader.findAll()
                assert resReader != []
                assert resReader.size()  == 2
                assert resReader.collect{r->r.name}.sort() == ["marian", "william"].sort()

                def queryReader4book = Book.where  //query many through assoc to the one side -- owning side
                {
                        readBy.name == "william"
                }
                def resBook = queryReader4book.findAll()
                assert resBook != []
                assert resBook.size()  == 2
                assert resBook.collect{b->b.title}.sort() == ["goblet of fire", "war of the worlds"].sort()

                //query from owned n side into the m Book side - fails sql #1 not set
                def rRes = Reader.findAllWhere (hasRead : book1)   //makes no diff if i use hasRead: [book1]
                assert rRes != null
                assert rRes.size() == 1
                assert rRes.collect{r->r.name}.sort() == ["will"]

                // query using direct query from many side to 1 on Author
                def fRes = Book.findAllWhere (author : jk)  //this works
                assert fRes[0].title == "goblet of fire"

                //query from owning m side into the n Readers - fails sql #1 not set
                fRes = Book.findAllWhere (readBy : maz)  
                assert fRes != null
                assert fRes.size() == 2
                assert fRes.collect{b->b.title}.sort() == ["famous five","goblet of fire"].sort()
        }


these queries fail with error

| Error 2012-02-22 21:47:54,357 [main] ERROR util.JDBCExceptionReporter  - Parameter "#1" is not set; SQL statement:
select this_.id as id0_0_, this_.version as version0_0_, this_.date_created as date3_0_0_, this_.name as name0_0_ from reader this_ where (this_.id=?) [90012-147]
| Failure:  testFindReaderByBookQuery(com.softwood.book.AuthorIntegTests)
|  org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select this_.id as id0_0_, this_.version as version0_0_, this_.date_created as date3_0_0_, this_.name as name0_0_ from reader this_ where (this_.id=?)]; SQL state [90012]; error code [90012]; Parameter "#1" is not set; SQL statement:
select this_.id as id0_0_, this_.version as version0_0_, this_.date_created as date3_0_0_, this_.name as name0_0_ from reader this_ where (this_.id=?) [90012-147]; nested exception is org.h2.jdbc.JdbcSQLException: Parameter "#1" is not set; SQL statement:
select this_.id as id0_0_, this_.version as version0_0_, this_.date_created as date3_0_0_, this_.name as name0_0_ from reader this_ where (this_.id=?) [90012-147]
        at org.grails.datastore.gorm.GormStaticApi.findAllWhere(GormStaticApi.groovy:426)
        at com.softwood.book.AuthorIntegTests.testFindReaderByBookQuery(AuthorIntegTests.groovy:266)
Caused by: org.h2.jdbc.JdbcSQLException: Parameter "#1" is not set; SQL statement:
select this_.id as id0_0_, this_.version as version0_0_, this_.date_created as date3_0_0_, this_.name as name0_0_ from reader this_ where (this_.id=?) [90012-147]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
        at org.h2.message.DbException.get(DbException.java:167)
        at org.h2.message.DbException.get(DbException.java:144)
        at org.h2.expression.Parameter.checkSet(Parameter.java:73)
        at org.h2.command.Prepared.checkParameters(Prepared.java:161)
        at org.h2.command.CommandContainer.query(CommandContainer.java:77)
        at org.h2.command.Command.executeQuery(Command.java:132)
        at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        ... 2 more

why is this failing ??

[will.woodman]

so if any else has had trouble over simple query forms - i appear got the following tests to all work (though there is a query i have sepreately posted over where query and trying to do a join fetch - and whether the syntax supports this


i hope this might help other newbies who are figuring out how to get various query type to run

so heres the intragtion test file

import static org.junit.Assert.*
import org.junit.*
import org.hibernate.FetchMode as FM

class QueryApproachesTests {

    @Before
    void setUp() {
        // Setup logic here
                def book1 = new Book (title:"war of the worlds")
                def book2 = new Book (title: "famous five")
                def book3 = new Book (title: "goblet of fire")
                assert book1.save().hasErrors () != true
               
                def will = new Reader (name:"william")
                def maz = new Reader (name:"marian")
               
                book1.addToReadBy (will)
                book2.addToReadBy (maz)
                book3.addToReadBy (will)
                book3.addToReadBy (maz)
               
                book1.save()
                book2.save()
                book3.save ()
               
                def unk = new Author (name:"unknown")
                unk.addToBooks (book1)
                unk.addToBooks (book2)
                def sUnk = unk.save()
                assert sUnk != null
                assert book1.author.name == "unknown"

                def jk = new Author (name: "JK Rowling")
                assert jk.addToBooks (book3).save()

                def anyOld = new Author (name: "any old person")  //author with no books
                assert anyOld.save () != null
               
                assert Book.count() == 3
                assert Reader.count() == 2
                assert Author.count() == 3
               
                assert book1.readBy.size() == 1
                assert book2.readBy.size() == 1
                assert book3.readBy.size() == 2
                assert will.hasRead.size() == 2
                assert maz.hasRead.size() == 2
    }

    @After
    void tearDown() {
        // Tear down logic here
    }

    @Test //#1
    void testBasicDynamicQuery() {
        def aRes = Author.findAll ()
                assert aRes != []
                assert aRes.collect {a ->a.name}.sort () == ["JK Rowling", "any old person", "unknown"]
    }
       
        @Test //#2
        void testBasicDynamicByQuery() {
                /** dynamic queries can't work on association attributes
                 *  can only include upto 2 attributes through the dynamic build approach
                 */
                def aRes = Author.findAllByName ("unknown") // returns ArrayList
                assert aRes != []
                assert aRes.size () == 1
                assert aRes.collect {a ->a.name}.sort () == ["unknown"]

                def aBook = Book.findByTitle ("goblet of fire") // returns instance
                assert aBook != null
                assert aBook.title == "goblet of fire"

                //query using text matching single char match and block match
                def rRes = Reader.findByNameLike ("m_ri%")
                assert rRes != null
                assert rRes.name == "marian"

                //query using property names returns arrayList
                rRes = Reader.findAllWhere (name : "william")
                assert rRes != null
                assert rRes.size() == 1
                assert rRes[0].name == "william"


        }

        @Test //#3
        void testWhereQuery() {
               
           //find authors named 'unknown', return detachedQuery
           def query = Author.where {
                   name == "unknown"  //using % makes it fail
           }
           
           def author = query.findAll() //get arraylist
           assert author != null
           assert author != []
           assert author.size () == 1 //expect 1 author with book "world"
           assert author[0].name == "unknown"

           //use matches like form  =~ in case insensitive form    
           query = Author.whereAny {
                   name ==~ "JK%"
           }
           author = query.findAll()  //get array list
           assert author != null
           assert author != []
           assert author.size () == 1 //expect 1 author with book "world"
           assert author[0].name == "JK Rowling"

           //get all books
           query = Book.where ()  
           {
            join 'author'  //load author eagerly, from many to one - doesnt do anything in sql console log
            order 'title', 'asc' //order by title attribute  
                        fetchMode 'readBy', FM.SELECT  // eager fetch readers avoid duplicates but runs a second select
           }

           def books = query.findAll() //get arraylist
           assert books != null
           assert books != []
           assert books.size () == 3 //expect 1 author with book "world"
           assert books.collect{b->b.title}.sort() ==  ["famous five", "goblet of fire", "war of the worlds"]

           //query book based on 'readBy' association on book following m:n
           def bQuery = Book.where {
                   readBy.name == "william"
           }
           def bRes = bQuery.findAll()
           assert bRes != []
           assert bRes.size() == 2
           assert bRes.collect{b->b.title} == ["war of the worlds", "goblet of fire"]

           //query Reader through assoc following m:n to the owning side
           def queryBook4reader = Reader.where  
           {
                   hasRead.title == "goblet of fire"
           }
           def resReader = queryBook4reader.findAll()
           assert resReader != []
           assert resReader.size()  == 2
           assert resReader.collect{r->r.name}.sort() == ["marian", "william"].sort()

           //query Reader through assoc following m:n to the owning side
           queryBook4reader = Reader.where  
           {
                   hasRead{title == "goblet of fire"}  //using this form allows multiple matches
           }
           resReader = queryBook4reader.findAll()
           assert resReader != []
           assert resReader.size()  == 2
           assert resReader.collect{r->r.name}.sort() == ["marian", "william"].sort()
        }

        @Test //#4
        void testCriteriaQuery()
        {
                def resReader = Reader.withCriteria  //query many through assoc to the one side -- non owning
                {
                        hasRead{
                                eq ('title', "goblet of fire")
                        }
                        fetchMode "hasRead", FM.SELECT  // eager fetching for the set
                }
                assert resReader != []
                assert resReader.size()  == 2
                assert resReader.collect{r->r.name}.sort() == ["marian", "william"].sort()

                def resBook = Book.withCriteria  //query many through assoc to the one side -- owning side
                {
                        readBy{
                                eq ('name', "william")
                        }
                }
                assert resBook != []
                assert resBook.size()  == 2
                assert resBook.collect{b->b.title}.sort() == ["goblet of fire", "war of the worlds"].sort()

                /**
                 * when eager fetching records use best form
                 * you can use the 'join' method from m:1 association but
                 * for 1:m side use fetchMode = FetchMode.SELECT
                 *
                 * note that if you include association in query contraints,
                 * these associations will be eagerly loaded automatically
                 */
                // eager select from m:1 side  - use the join form
                resBook = Book.withCriteria  //query many through assoc from the many to the one side -- owning side
                {
                        author{
                                eq ('name', "unknown")
                        }
                        join 'author' //eager load author association
                        order 'title', 'asc'
                }
                assert resBook != []
                assert resBook.size()  == 2
                assert resBook.collect{b->b.title} == ["famous five", "war of the worlds"]

                //eager select from the owning side 1:m - use the fetchMode.SELECT form
                def authRes = Author.withCriteria {
                        eq 'name', "unknown"
                        fetchMode 'books', FM.SELECT //select books eagerly from owning side into the many
                        order 'books', 'asc' //order books collection
                }
                assert authRes != []
                assert authRes.size () == 1
                assert authRes[0].books.collect { b-> b.getClass()} == [Book, Book] //check we didnt get a lazy proxy
                assert authRes[0].books.collect {b-> b.title}.sort() == ["famous five","war of the worlds"]
        }
       
        @Test //#5
        void testQueryByExample()
        {
                def unk = Author.findByName ("unknown")//get(0) counts from 1, not 0
                assert unk != null
                assert unk.name == "unknown"
                def fRes = Book.findAllByAuthor (unk)
                assert fRes != null
                assert fRes.size() == 2
                assert fRes.collect {b -> b.title}.sort()  == ["war of the worlds", "famous five"].sort()
        }
       
        @Test //#6
        void testHQLQuery()
        {
                //basic get all
                def aRes = Author.findAll ("from Author as a")
                assert aRes != null
                assert aRes.size() == 3
                assert aRes.collect {a->a.name}.sort () == ["JK Rowling", "any old person", "unknown"]

                def wow = Book.findByTitle ("war of the worlds")// cant find Book.load(1)
                assert wow != null
                assert wow.title == "war of the worlds"
                assert wow.id == 16  //why its sixteen i dont know! was expecting 1, 1st record in Book table

                // find readers by testing the hasRead association into book
                def rRes = Reader.findAll(
                        'from Reader r where :book in elements(r.hasRead)',
                        [book: wow])  // you can avoid DB read  if you know the id by just load the proxy from session
                assert rRes != null
                assert rRes != []
                assert rRes.collect {r -> r.name}.sort () == ["william"]

                def unk = Author.findByName ("unknown")
                def jk = Author.findByName ("JK Rowling")
                def aop = Author.findByName ("any old person")
               
                wow = Book.findByTitle ("war of the worlds")
                def ff = Book.findByTitle ("famous five")
                def gof = Book.findByTitle ("goblet of fire")//get(3) gets unknown
                def will = Reader.findByName ("william")
                def maz = Reader.findByName ("marian")
               
                //cartesion jon test
                def cartProd = Book.executeQuery ("from Book b, Author a")
                assert cartProd == [[wow, unk], [wow, jk], [wow, aop], [ff, unk], [ff, jk], [ff, aop], [gof, unk], [gof, jk], [gof, aop]]  

                // using implicit join in the where clause from many side back to one side
                def bRes1 = Book.executeQuery ("from Book b where b.author.name = 'JK Rowling'")  
                assert bRes1 == [gof]

                //use explicit join in the from clause, note as no select this returns array of object array tuple
                def aRes1 = Author.executeQuery ("from Author a inner join a.books b where b.title like 'gob%'")  
                assert aRes1 == [[jk, gof]]
               
                // as above but select only the books - get standard array
                def bSelRes = Book.executeQuery ("select book from Book as book join book.readBy as r where r.name like 'william'")
                assert bSelRes == [wow, gof]
               
                //query using findAll returns array of tuples -
                //if you add a select this will error
                def b1Res = Book.findAll( "from Book as book join book.readBy as r where r.name like 'william'")
                [[wow, will], [gof, will]]
               
                def readRes = Reader.executeQuery ("select reader from Reader as reader join reader.hasRead book where book.title = 'goblet of fire'")
                assert readRes.sort() == [maz, will].sort()
               
                /**
                 * note findAllBy with "select .. statement causes an exception
                 * whereas executeQuery just takes the HQL as typed - not sure what the engine does in the back
                 * thats makes the findAll by method not work
                 */
               
                //join across all the tables
                def authRes = Author.executeQuery ("select a from Author as a join a.books as b join b.readBy as r where r.name = 'william'")
                assert authRes.sort () == [unk, jk].sort()
               
       
                //right join from book into author - no book for author='any old person'
                def bookRes = Book.executeQuery("select b, a from Book b right join b.author a")
                assert bookRes == [[wow,unk], [ff,unk], [gof, jk], [null, aop]]
               
                //join fetch on books - to get the authots in one db hit
                bookRes = Book.executeQuery("select b from Book b right join fetch b.author")
                assert bookRes == [wow, ff, gof, null]

                //show difference between join and join fetch - join returns array of tuples
                authRes = Author.executeQuery ("from Author as a join a.books as b where b.title = 'war of the worlds'")
                assert authRes.sort () == [[unk, wow]].sort()
               
                //join fetch - returns array of authors with books - but auto loads the books association
                authRes = Author.executeQuery ("from Author as a join fetch a.books")
                assert authRes.sort () == [jk, unk, unk].sort()

                //join fetch - returns distinct array of authors - but auto loads the books association
                authRes = Author.executeQuery ("select distinct a from Author as a join fetch a.books")
                assert authRes.sort () == [jk, unk].sort()

                }
}