< Prev: Tables, fields and predicates
Up: Basics
Next: Database Connection >

Basics: SQL escape hatch

Contents

Fieldf and Predicatef

  contents

Now that you know what a Field and a Predicate are, there will come a time where you need to write your own Field or Predicate that isn't supported by this package. Examples include calling niche functions, casting a datatype according to your SQL dialect, or just the hundreds of ways SQL can be written that I didn't provision for.

That is where the Fieldf and Predicatef functions come in. They let you cast arbitrary expressions into valid Fields and Predicates that you can use inside the query builder. 

/* Fieldf and Predicatef Function Signatures */
func Fieldf(format string, values ...interface{}) CustomField
func Predicatef(format string, values ...interface{}) CustomPredicate
// CustomField and CustomPredicate implement the Field and Predicate interfaces
// respectively

u := tables.USERS()
// examples below use Postgres dollar placeholders $1, $2, $3 etc

Select(Fieldf("CAST(? AS TEXT)", u.USER_ID))
// query: SELECT CAST(users.user_id AS TEXT)
// args: []

Select(Fieldf(
    "concat_lower_or_upper(a := ?, uppercase := ?, b := ?)",
    "hello world", true, u.EMAIL,
))
// query: SELECT concat_lower_or_upper(a := $1, uppercase := $2, b := users.email)
// args: ["hello world", true]

Where(Predicatef(
  "? IN (?) OR EXISTS(?)",
  u.USER_ID, []int{1,2,3}, SelectOne().From(u).Where(u.USER_ID.EqInt(37)),
))
// query: WHERE users.user_id IN ($1, $2, $3) OR EXISTS(SELECT 1 FROM public.users WHERE users.user_id = $4)
// args: [1, 2, 3, 37]

As you can see Fieldf and Predicatef function similar to the Printf function, except the only format placeholder is the ? question mark. The values are then expanded, one by one, into each ? question mark placeholder. The important thing to know is that it recognizes its own types. So if you passed in a Field or Predicate to the function, it would render the Field or Predicate accordingly. It also recognizes Queries, which is how you can embed subqueries into your fields and predicates. Otherwise if it receives a primitive type or a type it does not recognize, it simply throws in the appropriate SQL placeholder into the query (Postgres → $1, $2, etc, MySQL → ?, ?, etc) and adds the value into the list of args.

Fieldf/Predicatef Slice expansion
Slices, no matter the type, are expanded into a comma-separated list of placeholders. The contents of the slice are then added to the list of args. This lets you do neat things in Postgres, like catering to both the IN (value1, value2, ...) and = ANY(ARRAY[value1, value2, ...]) expressions. As mentioned in https://stackoverflow.com/a/34627688, the following expressions are equivalent: 
SELECT * FROM foo WHERE id = ANY (ARRAY[1, 2, 3]);
SELECT * FROM foo WHERE id IN (1, 2, 3);
This is how you'd express them in sq: 
SelectAll().From(foo).Where(Predicatef("? = ANY(ARRAY[?])", foo.ID, []int{1,2,3}))
SelectAll().From(foo).Where(Predicatef("? IN (?)", foo.ID, []int{1,2,3}))
// query: SELECT * FROM foo WHERE foo.id = ANY(ARRAY[$1, $2, $3]) args: [1, 2, 3]
// query: SELECT * FROM foo WHERE foo.id IN ($1, $2, $3) args: [1, 2, 3]
Same slice, but formatted differently. In fact, all Fields in sq already support the IN expression in the exact same way. If you look in sq's source code, you will find that most helper functions are simply wrappers around Fieldf and Predicatef. Now that you know it, you can write your own.

Building a complex query

  contents

Here is a real query I had to write in a project, and a demonstration of how how easy Fieldf makes it to build complex queries. I needed to copy over an existing table row into a new row, but modify the year in the timestamp column to a specific year (that was user-specified). It was an upsert, which meant that if the new row already exists I just had to modify the timestamp. I found a stackoverflow answer showing how to set the year in a timestamp, which looks like this: 

SET date_column = date_column +
  MAKE_INTERVAL(
    YEARS := <target_year> - EXTRACT(YEAR FROM date_column)::INTEGER
  )
This was how I adapted that expression into my query (SQL, followed by Go). 
INSERT INTO public.periods AS p1 (cohort, stage, milestone, start_at, end_at)
SELECT
    $1,
    p2.stage,
    p2.milestone,
    p2.start_at + MAKE_INTERVAL(YEARS := $2::INT - EXTRACT(YEAR FROM p2.start_at)::INT),
    p2.end_at + MAKE_INTERVAL(YEARS := $3::INT - EXTRACT(YEAR FROM p2.end_at)::INT)
FROM
    public.periods AS p2
    JOIN public.forms AS f ON f.period_id = p2.period_id
    WHERE f.form_id = $4
LIMIT 1
ON CONFLICT (cohort, stage, milestone)
DO UPDATE SET start_at = EXCLUDED.start_at, end_at = EXCLUDED.end_at;

targetYear := "2020"
formID := 1
p1, p2 := tables.PERIODS().As("p1"), tables.PERIODS().As("p2")
f := tables.FORMS().As("f")
_, err := sq.InsertInto(p1).
    Columns(p1.COHORT, p1.STAGE, p1.MILESTONE, p1.START_AT, p1.END_AT).
    Select(sq.
        Select(
            sq.String(targetYear),
            p2.STAGE,
            p2.MILESTONE,
            sq.Fieldf(
                "? + MAKE_INTERVAL(YEARS := ?::INT - EXTRACT(YEAR FROM ?)::INT)",
                p2.START_AT, targetYear, p2.START_AT,
            ),
            sq.Fieldf(
                "? + MAKE_INTERVAL(YEARS := ?::INT - EXTRACT(YEAR FROM ?)::INT)",
                p2.END_AT, targetYear, p2.END_AT,
            ),
        ).
        From(p2).
        Join(f, f.PERIOD_ID.Eq(p2.PERIOD_ID)).
        Where(sq.Predicatef("? = ?", f.FORM_ID, formID)).
        Limit(1),
    ).
    OnConflict(p1.COHORT, p1.STAGE, p1.MILESTONE).
    DoUpdateSet(
        p1.START_AT.Set(sq.Excluded(p1.START_AT)),
        p1.END_AT.Set(sq.Excluded(p1.END_AT)),
    ).
    Exec(DB, 0)

< Prev: Tables, fields and predicates
Up: Basics
Next: Database Connection >