0

I have 2 domainClasses as below :

class Customer {
   def name   
   static hasMany = [accounts:Account]
}

class Account {
   def accountNo
   def type
}

Here Account's type can be 'Saving','Current','FD'

I want to write a criteria to search all those customers who have account types 'Saving','Current'.

What should be the criteria, I tried using below :

def customers = Customer.createCriteria().list {
    accounts {
         and {
            eq('type','Saving')
            eq('type','Current')
        }
    }
}

But when it executes it create inner join which is giving 0 result.

kritikaTalwar
  • 1,730
  • 1
  • 17
  • 25
Urvin Shah
  • 61
  • 1
  • 7

1 Answers1

2

You can either use or instead of and as suggested by Y. Tarion or use in:

def types = ["Savings", "Current"]
def customers = Customer.createCriteria().list {
    accounts {
        "in" "type", types
    }
}
Sascha Frinken
  • 3,134
  • 1
  • 24
  • 27
  • Actually this query return the customers who have either type "Savings" or "Current" but actually I want the customers who have atleast both types of accounts they may have more than these 2 types of accounts as well like "Savings","Current" or "Savings","Current","FD" etc. – Urvin Shah Feb 16 '17 at 01:37
  • OK now I got you. Actually it is a bit complicated and I am not sure if you can do this with criteria. The SQL would look like: select * from customer where id in (select customer_accounts_id from customer_account where account_id=1) and id in (select customer_accounts_id from customer_account where account_id=2) See: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation – Sascha Frinken Feb 16 '17 at 17:40
  • I tried by following way but its not generating appropriate out put : def results = Customer.withCriteria {         accounts.each {           'in' "id" , new DetachedCriteria(Account).build {             projections {               property('customer.id')             }             eq('type',it)           }           } – Urvin Shah Feb 28 '17 at 08:32