How to get certain rows from the child table in GORM

I have two domains like this:

class Color {
  static hasMany = [shade: Shade]
  String colorName
  String colorType
}

class Shade {
  static belongsTo = [color: Color]
  String shadeName
  boolean isAvailable
}

I want to find out all the colors that have any shades that are not available:

So if my Data is like this:

ID     color_name    color_type
----    --------      ----------
22       RED           CRAYON
23       GREEN         PAINT
45       GREY          CRAYON

ID     color_id       shade_name     is_available
---    ----------     -------------   ----------
2      22              DARK           false
3      22              LIGHT          true
4      23              DARK           true
5      23              LIGHT          true
6      45              DARK           false
7      45              LIGHT          false

I want my result to be of size 2 with items of color with ids 22 and 45 since they have some shade that is not available

I tried this query but I'm not entirely sure whether this will return what I want

def query = Color.where {
 shade.isAvailable == false
}
def list = query.list()

When I view the sql generated by hibernate for this, I don't notice any group by clause and the select statement is getting coloumns from both color and shade

Solution

You can use Criteria or HQL to get what you need:

//Criteria Approach:
//In this approach "shade" will be fetched eagerly with color
def colors = Color.createCriteria().listDistinct{
    shade{
        eq('isAvailable', false)
    }
}

//HQL
//In this approach only color will be fetched
//without shade, unless specified as ".... inner join fetch c.shade ..."
def colors = Color.executeQuery("select distinct c from Color as c \
                                 inner join c.shade as s \
                                 where s.isAvailable is false")

I would prefer plural notations for hasMany associations, therefore I would go with shades instead of shade (makes the relationship more vivid).