OrderBy does not escape column name in PostgreSQL

[parnic]

This may be a duplicate of #546, happy to add these comments there if so.

OrderBy(columnName) fails to escape the column name properly in a PostgreSQL database.

Table of sports games with a column holding the timestamp of the game (nullable) in a column named when. Code:

    games, err := models.Games(
        qm.WhereIn(fmt.Sprintf("%s in ?", models.GameColumns.ID), idList...),
        qm.OrderBy(models.GameColumns.When)).AllG()

generates query (example):

SELECT * FROM "games" WHERE ("id" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)) ORDER BY when;
[1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1359 1369]

but I expected to see:

SELECT * FROM "games" WHERE ("id" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)) ORDER BY "when";
[1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1359 1369]

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v3.4.0

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

Schema of my "games" table (excluded some foreign keys for simplicity):

CREATE TYPE "games_status" AS ENUM ('not_started','in_progress','final','forfeit');
CREATE TYPE "games_game_type" AS ENUM ('exhibition','regular_season','playoff');
CREATE TABLE IF NOT EXISTS "games" (
  "id" serial,
  "facility_id" int DEFAULT NULL,
  "when" timestamp DEFAULT NULL,
  "created_at" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "external_id" int DEFAULT NULL,
  "status" "games_status" NOT NULL DEFAULT 'not_started',
  "division_id" int NOT NULL,
  "game_type" "games_game_type" NOT NULL DEFAULT 'regular_season',
  "overtime" boolean NOT NULL DEFAULT '0',
  PRIMARY KEY ("id")
);

Further information. What did you do, what did you expect?

[aarondl]

Let's ensure that the PR for #546 alleviates this pain.

[parnic]

@stephenafamo this isn't done yet. Does closing it signify that it isn't going to be done?

edit: after digging through the PRs associated with this, it looks like the format to get parameterized column names now is:

models.MyModels(qm.OrderBy("?", models.MyModelColumns.ID))

The readme doesn't look to have been updated, so that would be helpful if possible. :)

edit 2: but parameterizing with a sort criteria doesn't work:

models.MyModels(qm.OrderBy("? DESC", models.MyModelColumns.ID))

doesn't throw any errors, but also doesn't sort descending.

[stephenafamo]

So, I tried this out myself and I can confirm that doing this does not work.

This is actually because you cannot use parameters in an ORDER BY or GROUP BY clause.
By extension, it means that adding parameterized modifiers to qm.OrderBy is redundant.

I'll reopen this issue in the meantime.

[parnic]

Okay, that explains some weirdness I'm seeing in my project. Thanks!