Imported from n-r-w/squirrel (
docs/squirrel/SKILL.md). Install upstream withnpx skills add n-r-w/squirrel --skill squirrel. Copyright stays with the author.
refundedExists := sq.Exists(
sq.Select("1").
From("orders r").
Where(sq.And{
sq.Expr("r.user_id = u.id"),
sq.Eq{"r.state": "refunded"},
}),
)
statusCase := sq.Case().
When(sq.Expr("u.status = ?", "active"), "active").
Else("inactive")
selectQuery := sq.Select("u.id", "u.name").
Column(sq.Alias(sq.Coalesce(0.0, sq.Expr("os.total_amount")), "total_amount")).
Column(sq.Alias(sq.Coalesce(0, sq.Expr("os.paid_count")), "paid_count")).
Column(sq.Alias(statusCase, "status_label")).
Column(sq.Alias(refundedExists, "has_refunds")).
Column(sq.Alias(sq.Count(sq.Expr("o.id")), "orders_count")).
From("users u").
LeftJoin("order_stats os ON os.user_id = u.id").
LeftJoin("orders o ON o.user_id = u.id").
Where(sq.And{
sq.Range("u.age", 20, 45),
sq.Or{
sq.Eq{"u.status": "active"},
sq.Like{"u.email": "%example.com"},
},
}).
Search("example.com", "u.email").
GroupBy("u.id", "u.name", "os.total_amount", "os.paid_count", "u.status").
Having(sq.Expr("COUNT(o.id) >= ?", 1)).
OrderBy("total_amount DESC", "u.id").
Limit(3).
Offset(0)
query := sq.With("order_stats").As(orderStats).
Select(selectQuery).
PlaceholderFormat(sq.Dollar)
sql, args, err := query.ToSql()
```
</example>
<example name="advanced-select-with-cte-and-joins">
```
activeUsers := sq.Select("id", "name", "email", "status", "age", "department_id").
From("users_all").
Where(sq.Eq{"status": []string{"active", "pending", "inactive"}})
orderTotals := sq.Select("o.user_id").
Column(sq.Alias(sq.Sum(sq.Expr("o.amount")), "sum_amount")).
Column(sq.Alias(sq.Count(sq.Expr("o.id")), "orders_count")).
Column(sq.Alias(sq.Min(sq.Expr("o.amount")), "min_amount")).
Column(sq.Alias(sq.Max(sq.Expr("o.amount")), "max_amount")).
Column(sq.Alias(sq.Avg(sq.Expr("o.amount")), "avg_amount")).
From("orders_all o").
GroupBy("o.user_id")
groupSub := sq.Select("ug.user_id").
From("user_groups_all ug").
Where(sq.Eq{"ug.group_id": 1})
bannedSub := sq.Select("ug.user_id").
From("user_groups_all ug").
Where(sq.Eq{"ug.group_id": 2})
groupJoin := sq.Select("ug.user_id").
From("user_groups_all ug").
Where(sq.Eq{"ug.group_id": 1}).
Prefix("JOIN (").
Suffix(") ug_filter ON ug_filter.user_id = au.id")
statusCase := sq.Case("au.status").
When(sq.Expr("?", "active"), 1).
When(sq.Expr("?", "pending"), 2).
Else(0)
displayName := sq.ConcatExpr(
"CONCAT(",
sq.Expr("au.name"),
", ' <', ",
sq.Expr("au.email"),
", '>')",
)
refundedExists := sq.Exists(
sq.Select("1").
From("orders_all r").
Where(sq.Expr("r.user_id = au.id")).
Where(sq.Eq{"r.state": "refunded"}),
)
noChargebacks := sq.NotExists(
sq.Select("1").
From("orders_all cb").
Where(sq.Expr("cb.user_id = au.id")).
Where(sq.Eq{"cb.state": "chargeback"}),
)
deptSub := sq.Select("d.id").
From("departments_all d").
Where(sq.Expr("d.id = au.department_id"))
orderByColumns := map[int]string{
1: "au.status",
2: "au.name",
3: "au.id",
}
orderByConds := []sq.OrderCond{
{ColumnID: 1, Direction: sq.Asc},
{ColumnID: 2, Direction: sq.Asc},
{ColumnID: 3, Direction: sq.Desc},
}
selectQuery := sq.Select().
PrefixExpr(sq.Expr("/* select-all-constructs */")).
Options("DISTINCT ON (au.status)").
Alias("au", "pref").Columns("id", "name").
Column(sq.Alias(displayName, "display_name")).
Column(sq.Alias(sq.Coalesce("n/a", sq.Expr("e.address")), "email_label")).
Column(sq.Alias(statusCase, "status_rank")).
Column(sq.Alias(refundedExists, "has_refunds")).
Column(sq.Alias(noChargebacks, "no_chargebacks")).
Column(sq.Alias(sq.Coalesce(0.0, sq.Expr("ot.sum_amount")), "sum_amount")).
Column(sq.Alias(sq.Coalesce(0, sq.Expr("ot.orders_count")), "orders_count")).
Column(sq.Alias(sq.Expr("ot.min_amount"), "min_amount")).
Column(sq.Alias(sq.Expr("ot.max_amount"), "max_amount")).
Column(sq.Alias(sq.Expr("ot.avg_amount"), "avg_amount")).
From("active_users au").
Join("order_totals ot ON ot.user_id = au.id").
InnerJoin("orders_all o ON o.user_id = au.id").
LeftJoin("emails_all e ON e.user_id = au.id").
RightJoin("departments_all d ON d.id = au.department_id").
CrossJoin("groups_all g").
JoinClause(groupJoin).
Where(sq.And{
sq.Range("au.age", 20, 35),
sq.Or{
sq.EqNotEmpty{"au.status": "active", "au.name": ""},
sq.Eq{"au.status": "pending"},
},
sq.Not(sq.Expr("au.status = ?", "inactive")),
sq.Eq{"au.id": []int64{1, 2, 3, 4}},
sq.Eq{"g.id": 1},
sq.NotEq{"au.email": "blocked@example.com"},
sq.Eq{"au.department_id": deptSub},
sq.In("au.id", groupSub),
sq.NotIn("au.id", bannedSub),
sq.Like{"au.email": "%@example.com"},
sq.NotLike{"au.email": "%@spam.com"},
sq.ILike{"au.name": "%a%"},
sq.NotILike{"au.name": "%zzz%"},
sq.Equal(sq.Select("1"), 1),
sq.NotEqual(sq.Select("1"), 2),
sq.Greater(sq.Select("2"), 1),
sq.GreaterOrEqual(sq.Select("2"), 2),
sq.Less(sq.Select("1"), 2),
sq.LessOrEqual(sq.Select("1"), 1),
refundedExists,
noChargebacks,
}).
Search("example.com", "au.email", "au.name").
GroupBy(
"au.id",
"au.name",
"au.email",
"au.status",
"e.address",
"ot.sum_amount",
"ot.orders_count",
"ot.min_amount",
"ot.max_amount",
"ot.avg_amount",
).
Having(sq.Expr("COUNT(o.id) >= ?", 1)).
OrderByCond(
orderByColumns,
orderByConds,
sq.OrderByCondOption{ColumnID: 2, NullsType: sq.OrderNullsLast},
).
Limit(10).
Offset(0)
query := sq.With("active_users").As(activeUsers).
Cte("order_totals").As(orderTotals).
Select(selectQuery).
PlaceholderFormat(sq.Dollar)
sql, args, err := query.ToSql()
```
</example>
<example name="insert-update-with-returning-and-cte">
```go
insertProducts := sq.Insert("products").
Columns("name", "stock", "price", "active").
Values("Widget", 10, 25.5, true).
Values("Gadget", 3, 15.0, true).
Values("Legacy", 1, 5.0, false).
Suffix("RETURNING id, name, stock").
PlaceholderFormat(sq.Dollar)
sql, args, err := insertProducts.ToSql()
require.NoError(t, err)
rows, err := pool.Query(ctx, sql, args...)
require.NoError(t, err)
t.Cleanup(rows.Close)
productIDs := make(map[string]int64)
for rows.Next() {
var id int64
var name string
var stock int
err := rows.Scan(&id, &name, &stock)
require.NoError(t, err)
productIDs[name] = id
}
require.NoError(t, rows.Err())
require.Len(t, productIDs, 3)
insertSales := sq.Insert("sales").
Columns("product_id", "qty").
Values(productIDs["Widget"], 4).
Values(productIDs["Widget"], 2).
Values(productIDs["Gadget"], 1).
PlaceholderFormat(sq.Dollar)
sql, args, err = insertSales.ToSql()
```
</example>
<example name="update-with-cte-and-from-select">
```go
salesAgg := sq.Select("product_id", "SUM(qty) AS qty_sold").
From("sales").
GroupBy("product_id")
updateQuery := sq.Update("products p").
Set("stock", sq.Expr("p.stock - s.qty_sold")).
FromSelect(salesAgg, "s").
Where("s.product_id = p.id").
Where(sq.Eq{"p.active": true}).
Suffix("RETURNING p.id, p.stock").
PlaceholderFormat(sq.Dollar)
sql, args, err = updateQuery.ToSql()
```
</example>
<example name="update-returning-as-intermediate-cte">
```go
candidate := sq.Select("id").
From("delivery_messages").
Where("state = ?", "ready").
OrderBy("id").
Limit(1).
Suffix("FOR UPDATE SKIP LOCKED")
picked := sq.Select("id").
From("candidate")
updated := sq.Update("delivery_messages dm").
Set("state", "claimed").
From("picked p").
Where("dm.id = p.id").
Suffix("RETURNING dm.id, dm.state")
query := sq.With("candidate").As(candidate).
Cte("picked").As(picked).
Cte("updated").As(updated).
Select(
sq.Select("id", "state").
From("updated").
OrderBy("id"),
).
PlaceholderFormat(sq.Dollar)
sql, args, err := query.ToSql()
```
</example>
<example>
```go
insertArchive := sq.Insert("archived_products").
Prefix("/* archive */").
Columns("name", "stock", "price", "active").
Select(
sq.Select("name", "stock", "price", "active").
From("products").
Where(sq.Eq{"active": false}),
).
PlaceholderFormat(sq.Dollar)
sql, args, err = insertArchive.ToSql()
```
</example>
</examples>