grails - 将 Sql 查询转换为 Grails/Gorm 查询

Question

如何将以下 SQL 查询转换为 Grails/Gorm？这可以通过基本查询来完成吗？我想避免使用 Criteria、Projections 和 HQL 以使其与我的代码库中的其他查询(基本查询)的结构保持一致。

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

是否可以在 gsp 页面中执行查询以显示特定 user_id 的结果，而不是在 Controller 中运行查询？

从评论中更新，下面是我的域类。

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()
  }
}

Answer 1

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

在您的用户类中，您有:

Retailer dealer

那么让我们从头开始查询正在做什么，从用户中列出用户，然后计算用户在零售商域类中出现的次数？

最好的方法是

是

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]

这应该做你正在做的我认为。

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?

浏览量是 presentation层和核心工作应该被排除在外 - 如果需要使用 TagLib称呼。
Controller 虽然在 grails 示例中使用并作为默认设置的一部分推出以使事情变得更容易，但也不是最好的地方。您应该在 service 中这样做这是 injected在 controller并呈现在 view作为所需的实际模型。

这是正确的方法 - GSP 有一个运行时大小 - 保持简短，保持甜蜜