CODEWITHSUNDEEP
grailshql
user2109125
user2109125

Reputation: 51

Grails: writing HQL query when domains are not directly matched

I have a domain class named Logging which stores an id of another domain class: Organization The structure of both domains is provided:

class Logging {
   Date dateCreated
   long user_id
   long organization_id
   String memberCode

   static constraints = {
      user_id(nullable: false)
      organization_id(nullable: false)
      memberCode(nullable: true)
   }
}

class Organization {
   Type type
   String name
   String memberCode
   User manager
   String collateralAutoEmails
   boolean isBlocked = true

   static constraints = {
      name(blank: false, unique: true)
      manager(nullable: true)
      memberCode(nullable: true)
      collateralAutoEmails(nullable: true)
   }

   static mapping = {
      manager(lazy: false)
   }
}

User enters several parameters: dateCreated, the memberCode and the name of the organization. I need to select all elements from the Logging domain matching these criterias.
The tricky part for me is writing the query for the name of the organisation parameter. According to the search rules I should check whether organization.name field contains data entered by user as a substring(case insensetive) and select the corresponding element from the Logging domain.
The two domains are not mapped directly and I can't join those tables.I have tried different approaches but still haven't found the solution.

Upvotes: 0

Views: 935

Answers (2)

James Kleeh
James Kleeh

Reputation: 12228

Here you go

Logging.executeQuery("Select l from Logging l, Organization o where l.organization_id = o.id and o.dateCreated = :dateCreated and o.memberCode = :memberCode and o.name = :name", [dateCreated: dateCreated, memberCode: memberCode, name: name])

Upvotes: 2

Saurabh Dixit
Saurabh Dixit

Reputation: 633

Try something like this:

Organization.executeQuery("select o from Organization o, Logging l where o.name like = :orgName AND o.id=l.organization_id", [orgName : orgName ])

I didn't tried it, if it works then more search options can be added on the query, and also % can be added on the parameter, in order to enhance the search.

Upvotes: 0

Related Questions