CODEWITHSUNDEEP
grails
K3rLa3da
K3rLa3da

Reputation: 43

Grails: search using createCriteria

I need to do simple search (Two my example-simple domains and controller action are below). I want to return list of users with firstName, lastName or Car.carName like searchPattern 

   class User {
        String firstName
        String lastName
        static hasMany = [car : Car]
    }

    class Car {
    User user
    String carName
    }

     def list(String search){
...
     def searchPattern = "%" + search + "%"
     def domains = User.createCriteria().list(max: max, offset: offset) {
                or {
                    like("firstName", searchPattern)
                    like("lastName", searchPattern)
                    car {
                       like("carName", searchPattern)
                       }

    }
    }

It returns incorrect results - doesn't see user, which hasn't got car. Can you help me to change it for correct working? Thanks a lot

Upvotes: 1

Views: 509

Answers (3)

K3rLa3da
K3rLa3da

Reputation: 43

Thanks a lot to all for your help and for usefull links. This decided my problem:

    import org.hibernate.criterion.CriteriaSpecification
.....

    def domains = User.createCriteria().list(max: max, offset: offset) {
    createAlias('cars', 'c', CriteriaSpecification.LEFT_JOIN)

    or {
        like("firstName", searchPattern)
        like("lastName", searchPattern)
        like("c.carName", searchPattern)
    }

Upvotes: 0

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9885

First you need to set up the domain class associations correctly. It seems you're going for a has-many association between User and Car. There are two variations: uni-directional and bi-directional. However, your implementation uses neither. Going with the assumption that you want a bi-directional association, you'll need to modify your Car class like this:

class Car {
    static belongsTo = [user: User]
    String carName
}

And for clarity, since a User has many Cars, it'd be worth pluralizing the collection name:

class User {
    String firstName
    String lastName
    static hasMany = [cars : Car]
}

For more on associations, you can read my article on the subject.

Next, since you want Users even if they do not have Cars, you should know about a subtle default built into GORM: the SQL database tables are automatically INNER JOINed. It is this INNER JOIN that's causing Users without Cars to be ignored. To address this, you'll need to change the join to an OUTER JOIN. You can do something like this:

import static org.hibernate.sql.JoinType.*

def domains = User.createCriteria().list(max: max, offset: offset) {
    createAlias('cars', 'c', LEFT_OUTER_JOIN)

    or {
        like("firstName", searchPattern)
        like("lastName", searchPattern)
        like("c.carName", searchPattern)
        isNull("c.carName")
    }
}

If I recall, aliases are used differently, hence the c.carName. You can read a bit more about using a LEFT OUTER JOIN here.

Upvotes: 0

injecteer
injecteer

Reputation: 20699

try this one:

car{
  or{
    isNull 'carName'
    like 'carName', searchPattern
  }
}

Upvotes: 1

Related Questions