Good day.

Someone has post a similar question, but I can't find a working solution so far. Could anyone please have a look , you are forum hero if you can solve it.

Basically I need 'or' two criteria. The scenario is that : we got a TXN, if the TXN is for stock , then TXN.stockLegs will be a non empty collection; if the TXN is for foreign exchange, the TXN.fxLegs will be a non empty collection;

Element in stockLegs is of type StockLeg; Element in fxLegs is FXLeg; both StockLeg and FXLeg got a field called currency.

Now I want to get all TXNs that involve USD, for example.

That is : get all transactions that either its stockLegs contains any stockLeg with USD currency ; or its fxLegs contains any fxLeg with USD currency;

Hibernate Criteria.createCriteria sounds like a solution, but thing is that hibernate will automatically 'and' criteria created . But what I need is really 'OR' .

Any solutions ?

regards

Hi


List b = sess.createCriteria(TXN.class,"t")
.createCriteria("stockLegs","sl")
.createCriteria("t.fxLegs","fl")
.add(Restrictions.or(Restrictions.eq("sl.currency", "USD") ,Restrictions.eq("fl.currency", "USD"))).list();

this may similar to your database structure

Amila

(Don't forget to rate if helps)

Thanks Amila. I tried this solution and looks like it's not what I am after.

The following code essentially produced sql that 'and' two criteria together, while I really need 'or'.

I tried to use the 'LEFT_JOIN' when calling the criteria.createCriteria. It worked , but the performance just like a dog.

I think the question is to union two result set, that is : txns with stock legs involving USD and txns with fx legs involving USD.

regards

CFN

I checked the forum and found that 'UNION' is not currently supported by HQL or Hibernate Criteria.

I will just give up and compromise the requirement a bit.