Convert Sql Query to Grails/Gorm query

Question

How do I convert the following SQL query to Grails/Gorm? Can this be done with a basic query? I would like to avoid using Criteria, Projections and HQL to keep it consistent with the structure of the other queries in my code base (which are basic queries).

  SELECT dealer_group_user_dealer_users_id, COUNT(user_id) 
    FROM db.dealer_group_user_user 
    GROUP BY dealer_group_user_dealer_users_id;

And is it possible to perform the query in the gsp page to display the result for a specific user_id as opposed to running the query in the controller?

To update from a comment made, below is my domain classes.

class DealerGroupUser extends User{

   static hasMany = [dealerUsers: User]

   static constraints = {
   }
}


class User {
     transient authService

Boolean active = true
String firstName
String lastName
String title
String username
String emailAddress
String passwordHash
Date lastLoginTime
Date dateCreated
Date lastUpdated
Retailer dealer
Client client
Date passwordUpdated
Long dealerUser
Boolean isReadOnlyClientManager = false
String regionClientManager

// Transient properties
String fullName
String password

static transients = ['fullName', 'password']

static mapping = {
    //permissions fetch: 'join'
    sort firstName: 'asc' // TODO: Sort on fullName
}

static hasMany = [roles: Role, permissions: String]

static constraints = {
    // NOTE: If a username is not provided, the user's email address will be used
    firstName maxSize: 30, blank: false
    lastName maxSize: 30, blank: false
    title maxSize: 50, blank: false, nullable: true
    username blank: false, unique: true
    emailAddress email: true, unique: false, blank: false
    passwordHash blank: false
    lastLoginTime nullable: true
    active nullable: true
    dealer nullable: true
    client nullable: true
    passwordUpdated nullable: true
    dealerUser nullable: true
    regionClientManager nullable: true
}

void setEmailAddress(String emailAddress) {
    if (EmailValidator.instance.isValid(emailAddress)) {
        this.emailAddress = emailAddress
        if (!username) {
            username = emailAddress
        }
    }
}

static namedQueries = {
    dealerGroupUsers {
        eq 'class', 'com.db.torque.DealerGroupUser'
    }


}


Integer setPassword(String plainTextPassword) {
    plainTextPassword = plainTextPassword?.trim()
    if (plainTextPassword) {
        if (!plainTextPassword.matches("^(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z])(?=\\S+\$).{8,}\$")){
            return -1
        }
        String previousPassword = this.passwordHash
        String newPassword = authService.encrypt(plainTextPassword)
        if (previousPassword != newPassword) {
            this.passwordHash = newPassword
            return 1
        }
        else {
            return -2
        }
    }
    return -1
}

@Transient
public static List<User> findAllByRolesContains(Role role) {
    return User.executeQuery("""
            SELECT u
            FROM User as u
            WHERE :role IN elements(u.roles)
        """, [role: role])
}

String fullName() {
    return "${firstName} ${lastName}"
}

String toString() {
    return fullName()
  }
}

It is impossible to answer the first part of this with confidence without knowing what your domain classes look like. In GORM you always express queries in terms of your object model, not your persistence model. The answer to the second part about putting the query in the GSP is "Yes, but you definitely shouldn't do it". — Jeff Scott Brown
@JeffScottBrown I added the domain model from my user.groovy file. I believe this is what you need to look at. If not, then can you provide a little more clarity. I am relatively new to Grails, so please forgive me if I've misunderstood what you needed to look at. — Rooky Coder

V H V H · Accepted Answer · 2019-07-03T16:24:45

  SELECT dealer_group_user_dealer_users_id, COUNT(user_id) 
    FROM db.dealer_group_user_user 
    GROUP BY dealer_group_user_dealer_users_id;

In your user class you have :

Retailer dealer

So lets start from the beginning what is query doing, listing users from user then getting a count of how many times user appears on Retailer domain class ?

The best way to do this would

be

String query = """ select 
      new map(
              u.id as userId, 
              (select count(d) from DealerGroupUser d left join d.dealerUsers du where du=u) as userCount
      )
      From user u order by u.id
"""
def results = User.executeQuery(query,[:],[readOnly:true]

This should do what you are doing I think.

And is it possible to perform the query in the gsp page to display the result for a specific user_id as opposed to running the query in the controller?

Views are presentation layer and hardcore work should be kept out of it - if needed to use a TagLib call. Controllers although used in grails examples and rolled out as part of defaults to make things easier also not the best place. You should be doing that in a service which is injected in a controller and presented in view as the actual model of what is needed.

That is the proper way - GSP have a runtime size - keep it short, keep it sweet

Convert Sql Query to Grails/Gorm query

1 Answers