Gideon Gideon - 6 months ago 89
JSON Question

Performing createCriteria() on Parent where the Child and its property is evaluated

I'm using a Hibernate in Grails with a properly setup

Parent
domain class and
Child
domain class, fully configured, having the static
hasMany
and
belongsTo
properties. Let's just use the class names
Parent
and
Child
for this scenario, although, it can be applied to other scenarios as well, practicality-wise.

To test this mapping, I've also performed many successful CRUDs. Although after a while, advance queries arise. I'm currently stuck in performing some "advance" queries, which I had summarized below. Using the given
Domain
mapping:

class Parent {
Long id
static hasMany = [ children: Child ]
}

class Child {
Long id
static belongsTo = [ parent: Parent ]
}


How can select the following criteria:


  1. Select all parent with no child

  2. Select all parent with child

  3. Select all parent with male child (and consequently should only include male children when converted as JSON via the
    as JSON
    groovy type-casting)



I have no idea how to start coding 1 and 2, maybe there's something to do with
child.size()
— I really don't know. Although my idea in 3 is:

ArrayList<Parent> PARENTS_WITH_SON = Parent.createCriteria().list {
children {
eq("gender", "male")
}
}


But the problem is that when converted to JSON, it includes the non-male entries. Can someone help me with the queries? Using
createCriteria()
would be nice, but it can also in
HQL
.

Answer

1 & 2 are really easy ones:

1. Select all parent with no child

Parent.createCriteria().list {
    sizeEq("children", 0)
}

2. Select all parent with child

Parent.createCriteria().list {
    sizeGt("children", 0)
}

3. Select all parent with male child (and consequently should only include male children when converted as JSON via the as JSON groovy type-casting)

Parent.createCriteria().listDistinct {
    children {
        eq("gender", "male")
    }
}

Using above query you will get collection of parents having a male child. But when you do a direct JSON conversion then GORM will fetch all of a parent's children. So either you can write a custom JSON converter or we can modify the query to return only required data in a Map.

/*
  create a new Grails Criteria that internally will use
  Hibernate's org.hibernate.Criteria.
*/
List<Map<String, Object>> resultList = Parent.createCriteria().list {
    /*
     Set a strategy for handling the query results.
     This determines the "shape" of the query result.

     See org.hibernate.Criteria.setResultTransformer(...)

     CriteriaSpecification.ALIAS_TO_ENTITY_MAP transformer will transform the results into a Map<String, Object>
    */
    resultTransformer(org.hibernate.criterion.CriteriaSpecification.ALIAS_TO_ENTITY_MAP)

    /*
     Join an association using the specified join-type, 
     assigning an alias to the joined association.

     org.hibernate.Criteria.createAlias(...)
    */
    createAlias("children", "child", JoinType.INNER_JOIN);

    projections {
        property("id", "parentId")
        property("child.id", "childId")
        property("child.gender", "gender")
    }

    eq("child.gender", "MALE")
}

This will give you a List of Map having parentId, childId and gender as its keys. Json for it would be:

[
  {
    "childId": 1,
    "gender": "MALE",
    "parentId": 2
  },
  {
    "childId": 3,
    "gender": "MALE",
    "parentId": 2
  },
  {
    "childId": 4,
    "gender": "MALE",
    "parentId": 3
  },
  {
    "childId": 5,
    "gender": "MALE",
    "parentId": 3
  }
]