CODEWITHSUNDEEP
grailsgrails-orm
ivish
ivish

Reputation: 612

Using OR operator between group of columns in Grails criteria

I have a code that queries database using Grails criteria from groovy code. Below is only part of test code that adds where clause/condition to the criteria.

for (Map.Entry<String, String> entry : searchMap.entrySet()) {
    String name = entry.getKey();
    String city = entry.getValue();
    eq("user.name", name)
    eq("user.city", city)
}

I want the resulting WHERE clause to look like,

    WHERE ( user.name = ? and user.city = ?) 
    OR ( user.name = ? and user.city = ?)
    OR ( user.name = ? and user.city = ?)
    .......

But my code always generates WHERE clause in the format,

WHERE ( user.name = ? and user.city = ?) 
    AND ( user.name = ? and user.city = ?)
    AND ( user.name = ? and user.city = ?)
    .......

Basically I want OR instead of AND between the name, city column groups. I tried solutions like,

    or {
        eq("user.name", name)
        eq("user.city", city)
    }

    or {
        and {
            eq("user.name", name)
            eq("user.city", city)
        }
    }

But nothing replaces AND with OR. How can I achieve that.

EDIT

With,

or {
        eq("user.name", name)
        eq("user.city", city)
    }

the WHERE clause is generated as,

WHERE ( user.name = ? and user.city = ?) 
    AND ( user.name = ? OR user.city = ?)
    AND ( user.name = ? OR user.city = ?)
    .......

which is exacly opposite of what I need.

Upvotes: 1

Views: 1439

Answers (2)

devbd
devbd

Reputation: 431

Hope this will be a solution for you. You have to mention and block explicitly.

def xx = Risk.createCriteria().list{
        or{
            and{
                eq 'isAccpeted', true
                eq 'isValid', true
            }
            and{
                eq 'investigationStarted', false
                eq 'isValid', true
            }
        }
    }

output

where ((this_.is_accpeted=? and this_.is_valid=?) or (this_.investigation_started=? and this_.is_valid=?))

As your code

or {
  and {
        eq("user.name", name)
        eq("user.city", city)
    }
  and {
        eq("user.name", name)
        eq("user.city", city)
    }
}

Upvotes: 1

Mario
Mario

Reputation: 4988

You can group criteria in a logical OR using an or { } block

Your code could look like this:

For(Map.Entry<String, String> entry : searchMap.entrySet()) {
    String name = entry.getKey();
    String city = entry.getValue();

    or {
        eq("user.name", name)
        eq("user.city", city)
    }
}

You can read about it in http://gorm.grails.org/latest/hibernate/manual/index.html#criteria In the section 7.5.1. Conjunctions and Disjunctions you will find a couple of examples

When you have to deal with these scenarios I recommend to you to enable the sql loggin, if you use grails 3 in this article explain it clearly the process, if instead you are using grails 2 check this post

Upvotes: 0

Related Questions