dongqie5529 2018-12-20 04:59
浏览 38

SQLX扫描到结构数组

I'm trying to implement a sql query that returns data in a way that jQuery Datatables expects while allowing users to customize the query (I'm doing alot of input validate to make sure there isn't any sql injection).The query is supposed to filter all of the edits by the object_name and editor display_name. Then of those edits it is supposed to get the last edit for each object based on it's perm_id (uid). Then it tries to fit everything into a jQuery Datatables friendly format that supports pagination. I think the query itself is working I'm just not sure if sqlx supports the array_agg scanning into a struct. When I try to scan it into the DataTableResponse struct I get this error.

unsupported Scan, storing driver.Value type []uint8 into type *[]edits.Edit

type Edit struct {
    Id                 *int            `db:"id" json:"id,omitempty"`
    UserId             *int            `db:"user_id" json:"user_id,omitempty"`
    PermId             *string         `db:"perm_id" json:"perm_id,omitempty"`
    ObjectName         *string        `db:"object_name,omitempty"`
    EditTypeId         *int            `db:"edit_type_id" json:"edit_type_id,omitempty"`
    EditDate           *time.Time      `db:"edit_date" json:"edit_date,omitempty"`

    DisplayName *string `db:"display_name" json:"display_name,omitempty"`
}

type DataTableResponse struct {
    Draw int `db:"draw" json:"draw"`
    RecordsTotal int `db:"records_total" json:"recordsTotal"`
    RecordsFiltered int `db:"records_filtered" json:"recordsFiltered"`
    Data []Edit `db:"data" json:"data"`
}


response := []DataTableResponse{}
err = db.Select(&response,
    fmt.Sprintf(`
        SELECT DISTINCT ON (results.perm_id) %d as draw, array_agg((%s)) as data,COUNT(DISTINCT perm_id) as records_total,COUNT(DISTINCT perm_id) - count(results.*) as records_filtered
        FROM (SELECT edit_metadata.*,user.display_name
                FROM edit_metadata INNER JOIN user 
                ON edit_metadata.user_id = user.id 
                WHERE user.display_name ilike $1 AND edit_metadata.object_name ilike $2 
                ORDER BY edit_metadata.edit_date ASC) 
        as results 
        GROUP BY results.perm_id,results.edit_date
        ORDER BY results.perm_id,%s %s
        LIMIT %s
        OFFSET %s`,draw, requestedFields, sortBy, sortDir,length, start),
    fmt.Sprintf("%%%s%%", c.Query("display_name")),
    fmt.Sprintf("%%%s%%", c.Query("object_name")))
if err != nil {
    log.Fatal(err)
}
c.JSON(http.StatusOK, response)
  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 Oracle中如何从clob类型截取特定字符串后面的字符
    • ¥15 想通过pywinauto自动电机应用程序按钮,但是找不到应用程序按钮信息
    • ¥15 MATLAB中streamslice问题
    • ¥15 如何在炒股软件中,爬到我想看的日k线
    • ¥15 seatunnel 怎么配置Elasticsearch
    • ¥15 PSCAD安装问题 ERROR: Visual Studio 2013, 2015, 2017 or 2019 is not found in the system.
    • ¥15 (标签-MATLAB|关键词-多址)
    • ¥15 关于#MATLAB#的问题,如何解决?(相关搜索:信噪比,系统容量)
    • ¥500 52810做蓝牙接受端
    • ¥15 基于PLC的三轴机械手程序