SnoopDougg SnoopDougg - 1 month ago 9
Groovy Question

grails dynamically add gorm subquery to existing query

I would like to have a util for building queries, so that I can add specificity to a common query rather than hard coding similar queries over and over again.
For instance:

DetachedCriteria query = DeviceConfiguration.where { ... }
while(query.list(max: 2).size() > 1) QueryUtil.addConstraint(query, newConstraint)


But I'm having trouble with queries that involve many-to-many relationships.

If my domain classes are:

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

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


And my device configurations look like this:

DeviceConfiguration hondaAccord = new DeviceConfiguration(setting1: 1, setting2: 1)
DeviceConfiguration hondaCivic = new DeviceConfiguration(setting1: 2, setting2: 2)
DeviceConfiguration accord = new DeviceConfiguration(setting1: 3, setting2: 3)
StringDescriptor hondaDescriptor = new StringDescriptor(name: "make", stringValue: "honda")
StringDescriptor civicDescriptor = new StringDescriptor(name: "model", stringValue: "civic")
StringDescriptor accordDescriptor = new StringDescriptor(name: "model", stringValue: "accord")
hondaAccord.addToStringDescriptors(hondaDescriptor)
hondaAccord.addToStringDescriptors(accordDescriptor)
hondaCivic.addToStringDescriptors(hondaDescriptor)
hondaCivic.addToStringDescriptors(civicDescriptor)
accord.addToStringDescriptors(accordDescriptor)
hondaAccord.save(failOnError: true)
hondaCivic.save(failOnError: true)
accord.save(failOnError: true, flush: true)


I would like to be able to do this:

def query = DeviceCollector.where{ stringDescriptors {name =~ "make" & stringValue =~ "honda"} }
if(query.list(max: 2)?.size() > 1)
def query2 = query.where { stringDescriptors {name =~ "model" & stringValue =~ "civic"} }
if(query2.list(max: 2)?.size() > 1)
//...


But that doesn't work - query2 gives the same results as the first query. And yet when I do THIS, it works perfectly:

def query = DeviceCollector.where{ stringDescriptors {name =~ "make" & stringValue =~ "honda"} }
if(query.list(max: 2)?.size() > 1)
def query2 = query.where { eq('setting1', 1) }
if(query.list(max: 2)?.size() > 1)
def query3 = query.build { eq('setting2', 1) }


Please advise :(

EDIT thanks to injecteer

Now my domain includes this:

class DeviceConfiguration {
//...
static namedQueries = {
byStringDescriptor { String name, String value ->
stringDescriptors {
ilike 'name', name
ilike 'stringValue', value
}
}
}
}


And my attempt to string the queries together looks like this:

//Lists hondaAccord and hondaCivic
DeviceConfiguration.byStringDescriptor("make", "honda").list()
//Lists hondaAccord and accord
DeviceConfiguration.byStringDescriptor("model", "accord").list()
// LISTS NOTHING... BUT WHYYYYY?
DeviceConfiguration.byStringDescriptor("make", "honda").byStringDescriptor("model", "accord").list()


I am confused. Yet again.

EDIT thanks to injecteer's updated answer

Yay, here is the named query that worked for me:

class DeviceConfiguration {
//...
static namedQueries = {
byStringDescriptor { List<StringDescriptor> descriptors ->
sizeEq('stringDescriptors', descriptors.size())
stringDescriptors {
or {
for(descriptor in descriptors) {
and {
ilike 'name', descriptor.name
ilike 'stringValue', descriptor.stringValue
}
}
}
}

}
}
}


The results (YAYYY) :) ...

StringDescriptor hondaDescriptor = new StringDescriptor(name: "make", stringValue: "honda")
StringDescriptor accordDescriptor = new StringDescriptor(name: "model", stringValue: "accord")

//returns nothing - **check**
def hondaQuery = DeviceConfiguration.byStringDescriptor([hondaDescriptor]).list()

//returns accord configuration - **check**
def accordQuery = DeviceConfiguration.byStringDescriptor([accordDescriptor]).list()

//returns just the hondaAccord configuration - **YESSSSSS**
def hondaAccordQuery = DeviceConfiguration.byStringDescriptorUsingOr([hondaDescriptor, accordDescriptor]).listDistinct()


injecteer is my favorite person ever.

Answer

Use criteria query or named queries. they both allow for better chaining

class DeviceConfiguration {

  static namedQueries = {

    byDescriptors { List vals -> 
      stringDescriptors { 
        or{
          for( def tuple in vals ){
            and{
              ilike 'name', "%${tuple[ 0 ]}%"
              ilike 'stringValue', "%${tuple[ 1 ]}%"
            }
          }
        }
      }
    }

  }       
}

so you can call:

DeviceConfiguration.byDescriptors( [ [ 'make', 'honda' ], [ 'model', 'accord' ] ] ).findAllBySetting1( 10 )

you should know, what conjunction is appropriate and or or

UPDATE 2

with so many of ands you won't find anything...

if you fire up a query like blah( honda, accord ).list() it would try find stringDescriptors with name='honda' AND name='accord' which is not possble, so it returns no results!

That's why I tend to think, that your domain model does NOT allow such queries at all - even at SQL-level.

Your attributes shall be clearly distinguishable, so that you can find by honda (type 'make') and accord (type 'model') it shouldn't look for "honda" in "model".

Can a single DeviceConfiguration instance contain several StringDescriptors of the same type?