0

I've found similar questions, but no answers.

class SomeDomain {
    static hasMany= [productData:ProductData]
}

ProductData is simple type/value pair

I'm trying to find all SomeDomains that have multiple products of certain type (in a loop). Currently the relevant portion of the criteria looks like:

SomeDomain.createCriteria.list {
  somedata.each { type, value ->
    productData {
      eq("type", type)
      eq("value", value)
    }
  }
}

However, this generates only a single join with the SQL:

from some_domain this_ inner join product_data productdata_a1_ on this_.id=productdata_a1_.some_domain_id 
where (productdata_a1_.type_id=4 and productdata_a1_.value='GC') 
and (productdata_a1_.type_id=5 and productdata_a1_.value='P1')

obviously type_id is never going to succeed on and'd checks for =4 and =5...

What I'd really like is two inner joins to product_data... can't figure out how to force this, though.

I tried createAlias("productData", "product-${index}") this gave org.hibernate.QueryException: duplicate association path: productData

Trebla
  • 1,164
  • 1
  • 13
  • 28

1 Answers1

0

Unsure why you need multiple joins to same table ? if question is understood correctly

String query="from someDomain sd join productData pd where pd.type in (:types) and pd.value in (:values) "
def inputParams=[:]
inputParams.values=['GC','P1']
inputParams.types=[4,5]
List resultsList = SomeDomain.executeQuery(query,inputParams,[readOnly:true,timeout:15])

pd.type may have to be another join since in the debug it attempts to get the .id so add another join

  String query="from someDomain sd join productData pd join pd.types tp where tp.id in (:types) and pd.value in (:values) "

If you wanted to do multiple joins as suggested in the question

  String query="from someDomain sd join productData pd join pd.types tp, ProductData pd2 where tp.id in (:types) and pd.value in (:values) and pd2.something=pd.something"

That is then going off and looking productData that is linked to someDomain then again looking up entire ProductData as pd2 and then confirming where pd2.something = pd.something

Using comma's in HQL becomes a new lookup not related to existing query..

V H
  • 8,382
  • 2
  • 28
  • 48
  • I'm trying to do this in a criteria query, not HQL. I can get it working in HQL, but this is built in a loop (so I may need more joins to the same table). Two "ins" won't work because I need a.type to line up a.value and b.type to match b.value... can't have a.type lining up with b.value. E.g., [shirt:blue, pants:green] would give me errant ProductData if I was searching for someDomains that had shirt:green – Trebla Sep 14 '16 at 12:20
  • ...or using my sample data [4:GC, 5:P1]. I need someDomains that have product of type 4, with value GC and type 5, value P1. The in solution would give me someDomains with type 4, value P1 which is not correct. – Trebla Sep 14 '16 at 12:25
  • I could try to figure out a query for you in HQL (since this is what I am using most of the time these days) but as you say you want it for criteria query. Personally I can't see the major difference. The underlying queries created will be quote close / similar. If you need it a criteria query I guess you will need for an expert on that. Good luck. – V H Sep 14 '16 at 14:49
  • The main reason is that there are an arbitrary number of products... attempting to build a dynamic HQL criteria with a StringBuilder is possible, but extremely ugly. At this point I've implemented an alternate solution, but I remain curious if this is possible with a criteria query. – Trebla Sep 14 '16 at 15:07