1

I want to query for all InvItems, which has multiple Attributes key/values pairs, that should fullfill my where condition.

class InvItem{
  static hasMany = [ attributes : InvAttributes ]
}

class invAttributes{
  String key
  String valueShort

  static belongsTo = [ invItem : InvItem ]
}

Let's say in sql: (this is how I would do it in sql!)

SELECT
id,
FROM inv_item inv
JOIN inv_attribute att1 ON inv.id = att1.inv_item_id
JOIN inv_attribute att2 ON inv.id = att2.inv_item_id
WHERE (att1.key = 'Alias'      AND att1.value_short LIKE 'foo%')  
AND   (att2_.key = 'DatasetId' AND att2.value_short LIKE 'bar%')

I tried a createCriteria for muliple occurence of attributes like this:

ciRequest is a list of attributes with key and valueShort

    def criteria = InvItem.createCriteria()
    def result = criteria.list {
        ciRequest.attributes.each { req ->
             attributes {
                eq('key', "${req.key}")
                like('valueShort', '%' + "${req.value.trim().toLowerCase()}" + '%')
            }
        }
    }

but this results in :

SELECT id
FROM inv_item this_ 
 INNER JOIN inv_attribute attributes1_ ON this_.id = attributes1_.inv_item_id
WHERE (attributes1_.key = ? AND attributes1_.value_short LIKE ?) AND
      (attributes1_.key = ? AND attributes1_.value_short LIKE ?)

How can I manage it, that there will be a second, third, multiple join on the InvAttribute table, so that it can check all the conditions ?

physi
  • 151
  • 8
  • 1
    This isn't the solution to your problem but `static hasMany { attributes : InvAttributes }` and `static belongsTo { invItem : InvItem}` are invalid. You probably want `static hasMany = [ attributes : InvAttributes ]` and `static belongsTo = [ invItem : InvItem ]`. – Jeff Scott Brown Apr 10 '15 at 14:16
  • 1
    I think better use HQL, of course you can play with fetchMode, but I don't sure. Better create dynamicaly HQL and use findAll(hql) – Koloritnij Apr 10 '15 at 14:38
  • @JeffScottBrown you're right, I've fixed that in my posting, it was out of memory typed :-) – physi Apr 10 '15 at 17:28

0 Answers0