Make a query to both "id" or "slug" with a single variable

Question

I have a table "articles" where there're "id" and "slug" among other things. On an html page I have a list of links to articles. A link can contain either "id" or "slug" in it.

But if in a URL there's only a number, it doesn't still mean that it's an id -- therefore, casting to int to determine whether or not it's slug or id, won't work.

  /articles/my_article
  /articles/35 
  /articles/666 --> still may be slug

I have this sql query:

import (
  "github.com/jackc/pgx/v4"
  //.........
)


// [..........]


vars := mux.Vars(req)
q1 := `
  SELECT
    ar.id,
    [.........]
  FROM
    articles AS ar
  WHERE ar.slug = $1 OR ar.id = $1`

ar := Article{}
row := db.QueryRow(context.Background(), q1, vars["id_or_slug"])
switch err := row.Scan(&ar.Id, /*[.......]*/); err {
case pgx.ErrNoRows:
  wrt.WriteHeader(http.StatusNotFound)
  wrt.Write([]byte("article not found"))
case nil:
  // good, article found

I get:

ERROR: operator does not exist: bigint = text (SQLSTATE 42883)

Do `strconv.ParseInt` in Go, if not valid integer result will be `0` and you can ignore the error, pass that int as second argument and use `WHERE ar.slug = $1 OR ar.id = $2`. — mkopriva, Apr 22 '20 at 15:24
... or create your own cast in postgres: https://stackoverflow.com/a/10307443/965900 — mkopriva, Apr 22 '20 at 15:28
I've read it and re-read it, I don't think I've missed anything, unless your question implies that there's a case where one article has id=666 and another article has slug='666' which is bad design but still will work, you just need to decide on the priority of the attributes and order the where conditions accordingly. — mkopriva, Apr 22 '20 at 15:33
from the two suggestions in the first two comments what specifically do you find is incorrect and won't work as you intend? — mkopriva, Apr 22 '20 at 15:36

score 0 · Answer 1 · answered Apr 23 '20 at 05:41

You can "attempt" to convert the value to an integer and if the conversion fails just ignore the error and provide an id value known to not be present in the db.

Doing the conversion with Go:

slug := mux.Vars(req)["id_or_slug"]

// option 1:
id, err := strconv.ParseInt(slug, 10, 64)
if err != nil {
    id = -1 // provide a value that you're certain will not be present in the db
}

// option 2:
// if id 0 is good enough, you can skip error checking
// and use the following instead of the above.
id, _ := strconv.ParseInt(slug, 10, 64)

query := `SELECT ... FROM articles AS a
WHERE a.slug = $1
OR a.id = $2`

row := db.QueryRow(query, slug, id)

Doing the conversion with postgres: (the following postgres snippet was taken from here. )

-- first create a postgres function that will do the conversion / cast
create or replace function cast_to_int(text, integer) returns integer as $$
begin
    return cast($1 as integer);
exception
    when invalid_text_representation then
        return $2;
end;
$$ language plpgsql immutable;

... and then utilizing that in go:

slug := mux.Vars(req)["id_or_slug"]

query := `SELECT ... FROM articles AS a
WHERE a.slug = $1
OR a.id = cast_to_int($1::text, -1)` // use the postgres function in the go query string

row := db.QueryRow(query, slug)

Make a query to both "id" or "slug" with a single variable

1 Answers1