在选择中使用子查询

Question

Is there any way to use the SubQuery function in Select?? I have seen it as part of Where clause, but I need on select.

I am solving this temporary doing this:

func GetUserProviders(userID int) ([]userprovider, error) {
    providers := []userprovider{}
    query := `SELECT (count(users_providers.user_id) > 0) 
                FROM users_providers 
                WHERE users_providers.user_id = '` + strconv.Itoa(userID) + `' AND users_providers.provider_id=providers.id`
    rows, err := db.DB.Table("providers").
        Select("providers.id, providers.name, (" + query + ") as checked").Rows()

    if err == nil {
        for rows.Next() {
            var provider = userprovider{}
            db.DB.ScanRows(rows, &provider)
            providers = append(providers, provider)
        }
    }
    return providers, err
}

But I would prefer, if possible, to use the function of the ORM instead concatenating strings.

In this case there is no danger, but for other cases, it would be great if there was any function to tranform

// SQL expression
type expr struct {
    expr string
    args []interface{}
}

into a sanitized String.

Thanks in advance.

Answer 1

Ok... I found the solution:

q := db.DB.Table("users_providers").
    Select("(count(users_providers.user_id) > 0)").
    Where("users_providers.user_id = ? AND users_providers.provider_id=providers.id", userID).
    SubQuery()
rows, err := db.DB.Table("providers").
    Select("providers.id, providers.name, ? as checked", q).
    Rows()

The Select function accepts 2 arguments: one for the query and the other one for the args, working in the same way than the Where.

Thanks any way :)