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)"}
I have 2 codes that produce same sql statement: 1. This code works correctly.
the code produces the following sql statement.
The second one which also produces the same sql statement for some reason doesn't work. 2.
it produces the following sql statement:
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