sqlx
sqlx copied to clipboard
jmoiron
why?
I have 2 codes that produce same sql statement: 1. This code works correctly.
func applyFilter(filter Filter, data map[string]interface{}, buf *bytes.Buffer) {
var wc []string
arrjoin := `
LEFT OUTER JOIN
caldera_operations__arrangements
ON
caldera_operations.id = caldera_operations__arrangements.operation_id `
endjoin := `
LEFT OUTER JOIN
endpoints
ON
endpoints.id = caldera_operations__arrangements.endpoint_id `
if filter.Name != nil {
data["name"] = fmt.Sprintf("%%%s%%", *filter.Name)
wc = append(wc, "LOWER(name) LIKE LOWER(:name)")
}
if filter.ArrangementID != nil {
data["arrangement_id"] = *filter.ArrangementID
// arrjoin = " INNER JOIN caldera_operations__arrangements ON caldera_operations.id = caldera_operations__arrangements.operation_id "
wc = append(wc, "caldera_operations__arrangements.arrangement_id = :arrangement_id")
}
if filter.EndpointID != nil {
data["endpoint_id"] = *filter.EndpointID
// endjoin = " INNER JOIN caldera_operations__arrangements ON caldera_operations.id = caldera_operations__arrangements.operation_id "
wc = append(wc, "caldera_operations__arrangements.endpoint_id = :endpoint_id")
}
buf.WriteString(arrjoin)
buf.WriteString(endjoin)
if len(wc) > 0 {
buf.WriteString(" WHERE ")
buf.WriteString(strings.Join(wc, " AND "))
}
}
func (s Store) Operations(ctx context.Context, page, limit int, filter Filter) ([]Operation, error) {
var operations []Operation
data := map[string]interface{}{
"offset": (page - 1) * limit,
"limit": limit,
}
// const q = `
// SELECT
// caldera_operations.id,
// caldera_operations.name,
// caldera_operations.description,
// caldera_operations.adversary_profile_id,
// coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
// coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
// coalesce(endpoints.name, '') AS endpoint_hostname,
// caldera_operations.created_at,
// caldera_operations.updated_at
// FROM
// caldera_operations
// LEFT OUTER JOIN
// caldera_operations__arrangements
// ON
// caldera_operations.id = caldera_operations__arrangements.operation_id
// LEFT OUTER JOIN
// endpoints
// ON
// endpoints.id = caldera_operations__arrangements.endpoint_id `
const q = `
SELECT
caldera_operations.id,
caldera_operations.name,
caldera_operations.description,
caldera_operations.adversary_profile_id,
coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
coalesce(endpoints.name, '') AS endpoint_hostname,
caldera_operations.created_at,
caldera_operations.updated_at
FROM
caldera_operations`
buf := bytes.NewBufferString(q)
applyFilter(filter, data, buf)
buf.WriteString(" ORDER BY created_at DESC")
buf.WriteString(" OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY")
fmt.Println(buf.String())
rows, err := sqlx.NamedQueryContext(ctx, s.db, buf.String(), data)
if err != nil {
return nil, base.DBError(err)
}
for rows.Next() {
var a Operation
err := rows.StructScan(&a)
if err != nil {
return nil, base.DBError(err)
}
operations = append(operations, a)
}
return operations, nil
}
the code produces the following sql statement.
SELECT
caldera_operations.id,
caldera_operations.name,
caldera_operations.description,
caldera_operations.adversary_profile_id,
coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
coalesce(endpoints.name, '') AS endpoint_hostname,
caldera_operations.created_at,
caldera_operations.updated_at
FROM
caldera_operations
LEFT OUTER JOIN
caldera_operations__arrangements
ON
caldera_operations.id = caldera_operations__arrangements.operation_id
LEFT OUTER JOIN
endpoints
ON
endpoints.id = caldera_operations__arrangements.endpoint_id WHERE caldera_operations__arrangements.arrangement_id = :arrangement_id ORDER BY created_at DESC OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
The second one which also produces the same sql statement for some reason doesn't work. 2.
var operations []Operation
data := map[string]interface{}{
"offset": (page - 1) * limit,
"limit": limit,
}
const q = `
SELECT
caldera_operations.id,
caldera_operations.name,
caldera_operations.description,
caldera_operations.adversary_profile_id,
coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
coalesce(endpoints.name, '') AS endpoint_hostname,
caldera_operations.created_at,
caldera_operations.updated_at
FROM
caldera_operations
LEFT OUTER JOIN
caldera_operations__arrangements
ON
caldera_operations.id = caldera_operations__arrangements.operation_id
LEFT OUTER JOIN
endpoints
ON
endpoints.id = caldera_operations__arrangements.endpoint_id `
// const q = `
// SELECT
// caldera_operations.id,
// caldera_operations.name,
// caldera_operations.description,
// caldera_operations.adversary_profile_id,
// coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
// coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
// coalesce(endpoints.name, '') AS endpoint_hostname,
// caldera_operations.created_at,
// caldera_operations.updated_at
// FROM
// caldera_operations`
buf := bytes.NewBufferString(q)
applyFilter(filter, data, buf)
buf.WriteString(" ORDER BY created_at DESC")
buf.WriteString(" OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY")
fmt.Println(buf.String())
rows, err := sqlx.NamedQueryContext(ctx, s.db, buf.String(), data)
if err != nil {
return nil, base.DBError(err)
}
for rows.Next() {
var a Operation
err := rows.StructScan(&a)
if err != nil {
return nil, base.DBError(err)
}
operations = append(operations, a)
}
return operations, nil
}
func (s Store) Operations(ctx context.Context, page, limit int, filter Filter) ([]Operation, error) {
var operations []Operation
data := map[string]interface{}{
"offset": (page - 1) * limit,
"limit": limit,
}
const q = `
SELECT
caldera_operations.id,
caldera_operations.name,
caldera_operations.description,
caldera_operations.adversary_profile_id,
coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
coalesce(endpoints.name, '') AS endpoint_hostname,
caldera_operations.created_at,
caldera_operations.updated_at
FROM
caldera_operations
LEFT OUTER JOIN
caldera_operations__arrangements
ON
caldera_operations.id = caldera_operations__arrangements.operation_id
LEFT OUTER JOIN
endpoints
ON
endpoints.id = caldera_operations__arrangements.endpoint_id `
// const q = `
// SELECT
// caldera_operations.id,
// caldera_operations.name,
// caldera_operations.description,
// caldera_operations.adversary_profile_id,
// coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
// coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
// coalesce(endpoints.name, '') AS endpoint_hostname,
// caldera_operations.created_at,
// caldera_operations.updated_at
// FROM
// caldera_operations`
buf := bytes.NewBufferString(q)
applyFilter(filter, data, buf)
buf.WriteString(" ORDER BY created_at DESC")
buf.WriteString(" OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY")
fmt.Println(buf.String())
rows, err := sqlx.NamedQueryContext(ctx, s.db, buf.String(), data)
if err != nil {
return nil, base.DBError(err)
}
for rows.Next() {
var a Operation
err := rows.StructScan(&a)
if err != nil {
return nil, base.DBError(err)
}
operations = append(operations, a)
}
return operations, nil
}
it produces the following sql statement:
SELECT
caldera_operations.id,
caldera_operations.name,
caldera_operations.description,
caldera_operations.adversary_profile_id,
coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
coalesce(endpoints.name, '') AS endpoint_hostname,
caldera_operations.created_at,
caldera_operations.updated_at
FROM
caldera_operations
LEFT OUTER JOIN
caldera_operations__arrangements
ON
caldera_operations.id = caldera_operations__arrangements.operation_id
LEFT OUTER JOIN
endpoints
ON
endpoints.id = caldera_operations__arrangements.endpoint_id WHERE caldera_operations__arrangements.arrangement_id = :arrangement_id ORDER BY created_at DESC OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
So only difference between these codes is that, I use buf.WriteString to include joins. The second one gives the following error:
{"time":"2024-09-10T14:36:31.523814521+05:00","level":"ERROR","msg":"ERROR: missing FROM-clause entry for table \"caldera_operations__arrangements\" (SQLSTATE 42P01)"}
Can someone explain WTf is happening o_o
