0

Why doesn't this work...

void test_get_configurations_whose_descriptors_are_in_list_or_null() {
    DeviceConfiguration noDescriptors = DeviceConfiguration(setting1: 1)
    DeviceConfiguration hondaCivicConfig = DeviceConfiguration(setting1: 2)
    DeviceConfiguration hondaAccordConfig = DeviceConfiguration(setting1: 3)
    DeviceConfiguration hondaCivicFromJapanConfig = DeviceConfiguration(setting1: 4)

    StringDescriptor makeHonda = new StringDescriptor(name: "make", stringValue: "honda")
    StringDescriptor modelCivic = new StringDescriptor(name: "model", stringValue: "civic")
    StringDescriptor modelAccord = new StringDescriptor(name: "model", stringValue: "accord")
    StringDescriptor locationJapan = new StringDescriptor(name: "location", stringValue: "Japan")

    hondaCivicConfig.addToStringDescriptors(makeHonda)
    hondaCivicConfig.addToStringDescriptors(modelCivic)
    hondaAccordConfig.addToStringDescriptors(makeHonda)
    hondaAccordConfig.addToStringDescriptors(modelAccord)
    hondaCivicFromJapanConfig.addToStringDescriptors(makeHonda)
    hondaCivicFromJapanConfig.addToStringDescriptors(modelCivic)
    hondaCivicFromJapanConfig.addToStringDescriptors(locationJapan)

    noDescriptors.save()
    hondaCivicConfig.save()
    hondaAccordConfig.save()
    hondaCivicFromJapanConfig.save(flush: true)

    List configs1 = DeviceConfiguration.byEmptyOrContainedWithin([makeHonda, modelCivic]).listDistinct()
    //What I expect - configs1 = [ noDescriptors, hondaCivicConfig ]
    //What I get - configs2 = [ noDescriptors, hondaCivicConfig, hondaAccordConfig, hondaCivicFromJapanConfig ]

    StringDescriptor locationCanada = new StringDescriptor(name: "location", stringValue: "Canada")
    List configs2 = DeviceConfiguration.byEmptyOrContainedWithin([makeHonda, modelCivic, locationCanada])
    //What I expect - configs2 = [ noDescriptors ]
    //What I get - configs2 = [noDescriptors, hondaCivicConfig, hondaAccordConfig, hondaCivicFromJapanConfig ]
}

Given the domain's and named query:

class StringDescriptor {
    String name
    String stringValue
    static hasMany = [ deviceConfigurations: DeviceConfiguration ]
    static belongsTo = DeviceConfiguration
}

class DeviceConfiguration {
    Integer setting1
    static hasMany [ stringDescriptors: StringDescriptor ]

    static constraints = {
        stringDescriptors nullable: true
    }

    //Edited thanks to dmahapatro's help
    static namedQueries = {
        byEmptyOrContainedWithin { List<StringDescriptor> descriptors ->
            stringDescriptors(CriteriaSpecification.LEFT_JOIN) {
                or {
                    for(descriptor in descriptors) {
                        and {
                            ilike 'name', descriptor.name
                            ilike 'stringValue', descriptor.stringValue
                        }
                    }
                    isNull('name')
                }
            }
        }
    }
}

Any help would be appreciated... :( ... I'm assuming this happens because during the join operation you get a table that looks like this:

config name          setting1 name     stringValue
noDescriptors        1        null     null
hondaCivic           2        make     honda
hondaCivic           2        model    civic
hondaAccord          3        make     honda
hondaAccord          3        model    accord
hondaCivicFromJapan  4        make     honda
hondaCivicFromJapan  4        model    civic
hondaCivicFromJapan  4        location Japan

Which is a problem because aparently a query for a list of just [make-honda] will return every single configuration, even though I want it to return just the first ("noDescriptors"). I'm not sure how to fix this query...

My previous question before dmahapatro's help:

Why is the second part of my 'or' closure ignored in my named query? If I change it to 'and', and I change isEmpty to "sizeEq('stringDescriptors', descriptors.size())" then I can find "hondaCivic" still. So 'and' works the way I would expect, but not 'or'...

static namedQueries = {
    byEmptyOrContainedWithin { List<StringDescriptor> descriptors ->
        or {
            stringDescriptors {
                or {
                    for(descriptor in descriptors) {
                        and {
                            ilike 'name', descriptor.name
                            ilike 'stringValue', descriptor.stringValue
                        }
                    }
                }
            }
            isEmpty('stringDescriptors')
        }
    }
}
SnoopDougg
  • 1,467
  • 2
  • 19
  • 35
  • 1
    Because by default Grails uses inner join for querying associations. You have to use LEFT JOIN to expect `stringDescriptors` to be null. Try using `stringDescriptors(CriteriaSpecification.LEFT_JOIN) { or { ... } }` in named query where `CriteriaSpecification` is `org.hibernate.criterion.CriteriaSpecification`. – dmahapatro Oct 13 '14 at 20:04
  • Interesting, but the `isEmpty('stringDescirptors')` is outside the `stringDescriptors { }` closure... how would I test if stringDescriptors was null within that closure? – SnoopDougg Oct 13 '14 at 20:15
  • 1
    You would not need `isEmpty` anymore if left joined because parents with no associations will be pulled as well. Makes sense? – dmahapatro Oct 13 '14 at 20:20
  • Cool, thanks, I think so. Unfortunately, I'm getting the same results with those changes (I've updated my question above with what I've tried). I think it's because none of the conditions in the `or` closure cover the case where the stringDescriptors is null... and I'm not sure how to fix that :( – SnoopDougg Oct 13 '14 at 20:33
  • Ahh, got it. I added the condition `isNull('name')` to the `or` closure. Thank you for your help! If you submit an answer containing your comments, I'll mark it as the answer. I don't think I can mark comments as the answer. Thanks again! – SnoopDougg Oct 13 '14 at 21:10
  • Sorry, I spoke to soon. I've updated my question with what I've tried. I'm still not sure how to solve this. If you have any suggestions, I'd really appreciate them. – SnoopDougg Oct 14 '14 at 19:52

0 Answers0