针对下列复杂SQL查询，GORM怎么处理？

[Anxiangchegu]

这是从Java项目mapper.xml文件中获取得的，如果用转换为GORM?

`

SELECT ta.* FROM sys_dictionary_data ta LEFT JOIN sys_dictionary tb ON ta.dict_id = tb.dict_id AND tb.deleted = 0 WHERE ta.deleted = 0 AND tb.dict_code = 'sex'

<!-- 用户角色查询sql -->
<sql id="selectUserRoleSql">
    SELECT a.user_id,
           GROUP_CONCAT(b.role_name) role_name
    FROM sys_user_role a
             LEFT JOIN sys_role b ON a.role_id = b.role_id
    GROUP BY a.user_id
</sql>

<!-- 关联查询sql -->
<sql id="selectSql">
    SELECT a.*,
    b.organization_name,
    c.dict_data_name sex_name
    FROM sys_user a
    LEFT JOIN sys_organization b ON a.organization_id = b.organization_id
    LEFT JOIN (
    <include refid="selectSexDictSql"/>
    ) c ON a.sex = c.dict_data_code
    LEFT JOIN(
    <include refid="selectUserRoleSql"/>
    ) d ON a.user_id = d.user_id
    <where>
        <if test="param.userId != null">
            AND a.user_id = #{param.userId}
        </if>
        <if test="param.username != null">
            AND a.username LIKE CONCAT('%', #{param.username}, '%')
        </if>
        <if test="param.nickname != null">
            AND a.nickname LIKE CONCAT('%', #{param.nickname}, '%')
        </if>
        <if test="param.sex != null">
            AND a.sex = #{param.sex}
        </if>
        <if test="param.phone != null">
            AND a.phone LIKE CONCAT('%', #{param.phone}, '%')
        </if>
        <if test="param.email != null">
            AND a.email LIKE CONCAT('%', #{param.email}, '%')
        </if>
        <if test="param.emailVerified != null">
            AND a.email_verified = #{param.emailVerified}
        </if>
        <if test="param.realName != null">
            AND a.real_name LIKE CONCAT('%', #{param.realName}, '%')
        </if>
        <if test="param.idCard != null">
            AND a.id_card LIKE CONCAT('%', #{param.idCard}, '%')
        </if>
        <if test="param.birthday != null">
            AND a.birthday LIKE CONCAT('%', #{param.birthday}, '%')
        </if>
        <if test="param.organizationId != null">
            AND a.organization_id = #{param.organizationId}
        </if>
        <if test="param.status != null">
            AND a.`status` = #{param.status}
        </if>
        <if test="param.createTimeStart != null">
            AND a.create_time &gt;= #{param.createTimeStart}
        </if>
        <if test="param.createTimeEnd != null">
            AND a.create_time &lt;= #{param.createTimeEnd}
        </if>
        <if test="param.deleted != null">
            AND a.deleted = #{param.deleted}
        </if>
        <if test="param.deleted == null">
            AND a.deleted = 0
        </if>
        <if test="param.roleId != null">
            AND a.user_id IN (SELECT user_id FROM sys_user_role WHERE role_id=#{param.roleId})
        </if>
        <if test="param.organizationName != null">
            AND b.organization_name LIKE CONCAT('%', #{param.organizationName}, '%')
        </if>
        <if test="param.sexName != null">
            AND c.dict_data_name = #{param.sexName}
        </if>
        <if test="param.keywords != null">
            AND (
            a.username LIKE CONCAT('%', #{param.keywords}, '%')
            OR a.nickname LIKE CONCAT('%', #{param.keywords}, '%')
            OR b.organization_name LIKE CONCAT('%', #{param.keywords}, '%')
            OR c.dict_data_name LIKE CONCAT('%', #{param.keywords}, '%')
            OR d.role_name LIKE CONCAT('%', #{param.keywords}, '%')
            )
        </if>
    </where>
</sql>`

[github-actions[bot]]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[ivila]

you can use Raw SQL, or just build it manually, here is a minimal demo

package main

import (
        "fmt"
        "math/rand"

        "gorm.io/driver/sqlite"
        "gorm.io/gorm"
)

const SQL = `
SELECT ta.*
FROM sys_dictionary_data ta
LEFT JOIN sys_dictionary tb
ON ta.dict_id = tb.dict_id
AND tb.deleted = 0
WHERE ta.deleted = 0
AND tb.dict_code = "sex"`

const SQL2 = `
SELECT ta.*
FROM sys_dictionary_data ta
LEFT JOIN sys_dictionary tb
ON ta.dict_id = tb.dict_id
AND tb.deleted = 0
WHERE ta.deleted = 0
AND tb.dict_code = ?`

type SysDictionaryData struct {
        ID      uint64 `gorm:"column:id"`
        DictID  uint64 `gorm:"column:dict_id"`
        Deleted int8   `gorm:"column:deleted"`
        Data    string `gorm:"column:data"`
}

func (SysDictionaryData) TableName() string {
        return "sys_dictionary_data"
}

type SysDictionary struct {
        ID       uint64 `gorm:"column:id"`
        DictID   uint64 `gorm:"column:dict_id"`
        Deleted  int8   `gorm:"column:deleted"`
        DictCode string `gorm:"column:dict_code"`
}

func (SysDictionary) TableName() string {
        return "sys_dictionary"
}

func prepareForTest(db *gorm.DB) {
        db.AutoMigrate(&SysDictionary{}, &SysDictionaryData{})
        for i := uint64(1); i <= 10; i++ {
                db.Create(&SysDictionary{DictID: i, DictCode: "sex"})
                dataNum := rand.Int()%5 + 2
                for j := 1; j <= dataNum; j++ {
                        db.Create(&SysDictionaryData{DictID: i, Data: fmt.Sprintf("dict %d data %d", i, j)})
                }
        }
}

func runByRawSQL(db *gorm.DB) {
        fmt.Printf("run by raw sql\n\n")
        var dest []SysDictionaryData
        err := db.Raw(SQL).Find(&dest).Error
        fmt.Printf("err is %+v\n", err)
        fmt.Printf("data is %+v\n\n", dest)
}

func runByRawSQLWithParameter(db *gorm.DB, dict_code string) {
        fmt.Printf("run by raw sql with parameter\n\n")
        var dest []SysDictionaryData
        err := db.Raw(SQL2, dict_code).Find(&dest).Error
        fmt.Printf("err is %+v\n", err)
        fmt.Printf("data is %+v\n\n", dest)
}

func runByManuallyBuildTheSQL(db *gorm.DB, dict_code string) {
        fmt.Printf("run by manually build the SQL\n\n")
        query := db.Table("sys_dictionary_data ta").
                Joins("LEFT JOIN sys_dictionary tb ON ta.dict_id = tb.dict_id AND tb.deleted = ?", 0).
                Where("ta.deleted = 0 AND tb.dict_code = ?", dict_code)
        var dest []SysDictionaryData
        err := query.Find(&dest).Error
        fmt.Printf("err is %+v\n", err)
        fmt.Printf("data is %+v\n\n", dest)
}

func main() {
        db, _ := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{PrepareStmt: true})
        prepareForTest(db)

        db = db.Debug() // use debug to print sql to the console
        runByRawSQL(db)
        runByRawSQLWithParameter(db, "sex")
        runByManuallyBuildTheSQL(db, "sex")
}

[github-actions[bot]]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨