goormgrails-ormgo-gorm

how to use gorm to make a raw query to DB and get fields from different tables in result?


I have the following (note that in the query I expect to get fields from different tables. Maybe there is some kind of peculiarity in this):


type Result struct {
    ID        string     `gorm:"column:id"`           
    CreatedAt time.Time  `gorm:"column:created_at"`   
    UpdatedAt time.Time  `gorm:"column:updated_at"`   
    DeletedAt *time.Time `gorm:"column:a_deleted_at"` 
    Category  string     `gorm:"column:category_code"`                    

    LastReleaseID          string    `gorm:"column:r_id"`
    LastReleaseIconID      uuid.UUID `gorm:"column:i_id"`          
    LastReleaseName        string    `gorm:"column:r_name"`
    LastReleaseVersion     string    `gorm:"column:r_version"`
    LastReleaseSubVersion  string    `gorm:"column:r_subversion"`
    LastReleaseStatus      string    `gorm:"column:r_status"`
    
    Developer string  `gorm:"column:b_dev"` 
}





func DoRequestToDB(db *gorm.DB, sqlArgs ...any) error {

    sql := `SELECT
        a."id",
        a."created_at",
        a."updated_at",
        a."deleted_at" AS a_deleted_at,
        a."category_code"
        ,
        r.id AS r_id,
        r.icon_id AS i_id,
        r.name AS r_name,
        r.version AS r_version,
        r.subversion AS r_subVersion
        r.status AS r_status
        ,
        b.developer AS b_dev
    FROM "applications" as a
    JOIN (
        SELECT release_filtered.*
        FROM releases AS release_filtered
        JOIN (
            SELECT
                application_id,
                max(created_at) as max_created_at
            FROM releases
            WHERE deleted_at is null
            AND status in (?)
            GROUP BY "application_id"
        ) AS release_condition
        ON release_filtered.application_id = release_condition.application_id
        AND release_filtered.created_at = release_condition.max_created_at
        and release_filtered.deleted_at is null
    ) AS r
    ON r.application_id = a.id
    JOIN builds AS b
    ON r.build_id = b.id
    WHERE a.deleted_at IS NULL`


    var result := make([]Result, 0)
    err := db.Raw(sqlQuery, sqlArgs...).Find(&result).Error
    if err != nil {
        return nil, err
    }

    return result, nil
}

I use a PostgreSQL database. I don't get an error, but there is nothing in the result too.

At the same time, if I execute the same query through the psql utility in the terminal, I get the result.

What am I doing wrong?


Solution

  • Everything turned out to be simpler - I specified the wrong database scheme. Everything works