Grails querying a domain class with a collection not producing expected results

Question

(Grails 2.4.2)

I'm trying to obtain a list of Stories that a user can access. So all Stories the user is the owner of the story or user is in a collection of Editors of this story. I've tried many different queries, from createCriteria to writing HQL. I can query all stories that the user is an owner of. I can query all stories that the user is in the collection of editors, but when I put the queries together with an OR..I don't get the right results.

class Story {
  String title
  Date dateCreated
  Date lastUpdated
  String description
  Boolean isPublic
  List storyContent = []
  User owner

    static belongsTo = [owner: User]
    static hasMany = [storyContent : StoryContent, viewers : Viewer, editors : Editor] 
...more


class Editor  { 
    User user
    static belongsTo = [story: Story]

When I do the following query:

def hql = "from Story as s left outer join s.editors as se where s.owner.username = 'joe'"
def results = Story.executeQuery(hql)

I get the correct results:

Result: [[com.storycreate.Story : 3, com.storycreate.Editor : 1], [com.storycreate.Story : 2, null]]

Joe is the owner of story 2 and story 3. Now I query for all stories where Joe is the editor

def hql = "from Story as s left outer join s.editors as se where se.user.username='joe'"
def results = Story.executeQuery(hql)

and get the correct results: (Joe is both editor and owner of story 3 and editor of story4)

Result: [[com.storycreate.Story : 3, com.storycreate.Editor : 1], [com.storycreate.Story : 4, com.storycreate.Editor : 4]]

now if I combine this to get a list of stories where joe is either the owner or an editor:

def hql = "from Story as s left outer join s.editors as se where (s.owner.username='joe' OR se.user.username='joe')"
def results = Story.executeQuery(hql)

I get the incorrect results (missing Story 2 where joe is owner)

Result: [[com.storycreate.Story : 3, com.storycreate.Editor : 1], [com.storycreate.Story : 4, com.storycreate.Editor : 4]]

Eventually, I want the query to give me a list of all stories where isPublic is true OR logged in user is the owner, editor, or viewer of the story. But I seem to be missing some understanding of how Hibernate is working here.

If joe is the owner of story 2 and 3, I think this result Result: [[com.storycreate.Story : 3, com.storycreate.Editor : 1], [com.storycreate.Story : 2, null]] is not showing it. — ivan.sim
Maybe I'm I not understanding the results. I'm a bit of a newbie with Grails. To me the result shows 2 rows. The first showing a Story object of ID 3 and the second show a Story object ID 2. Is that not right? — jer0dh

dmahapatro dmahapatro · Accepted Answer · 2014-10-31T02:29:34

This integration spec passes for me in 2.4.3:

class StoryControllerIntSpec extends IntegrationSpec {

    def setup() {
        User joe = new User(userName: "joe").save()
        User bar = new User(userName: "bar").save()
        User foo = new User(userName: "foo").save()

        // joe as both owner and editor
        Story s1 = new Story(isPublic: true, title: "Story1", owner: joe)
        Editor e1 = new Editor(user: joe)
        s1.addToEditors(e1)

        // joe only as owner
        Story s2 = new Story(isPublic: true, title: "Story2", owner: joe)
        Editor e2 = new Editor(user: foo)
        s2.addToEditors(e2)

        // joe only as editor
        Story s3 = new Story(isPublic: true, title: "Story3", owner: bar)
        Editor e3 = new Editor(user: joe)
        s3.addToEditors(e3)

        // joe not related to story
        Story s4 = new Story(isPublic: true, title: "Story4", owner: foo)
        Editor e4 = new Editor(user: bar)
        s4.addToEditors(e4)

        [s1, s2, s3, s4]*.save()
    }

    void "test something"() {
        given:
        StoryController controller = new StoryController()

        when:
        def model = controller.index()

        then:
        model.result.size() == 3
        def stories = model.result.collect { it[0] }
        stories.size() == 3
        stories*.title == ['Story1', 'Story2', 'Story3']
    }
}

// Controller
class StoryController {

    def index() {
        def hql = "from Story as s left outer join s.editors as se \
                   where (s.owner.userName='joe' OR se.user.userName='joe')"
        def results = Story.executeQuery(hql)

        [result: results]
    }
}

Grails querying a domain class with a collection not producing expected results

1 Answers