Multi column greater than

[jauco]

Postgres has a nice shorthand for multi column greater than expressions:

SELECT *
FROM table
WHERE (a,b) > (2,4);

Which is the same as saying:

SELECT *
FROM table
WHERE a > 2 OR (a = 2 AND b > 4)

I can't get this to work using postgres.js with dynamic columns and values:

This works:

const result = await sql`
SELECT *
FROM table
WHERE (a,b) > (2,4);
`

This also works:

const result = await sql`
SELECT *
FROM table
WHERE (${sql("a", "b")}) > (2,4);
`

This also works:

const result = await sql`
SELECT *
FROM table
WHERE (${sql("a", "b")}) > (${2},${4});
`

This breaks:

const result = await sql`
SELECT *
FROM table
WHERE (${sql("a", "b")}) > (${sql([2,4])});
`

with the following error:

error: Uncaught TypeError: str.replace is not a function
  return '"' + str.replace(/"/g, '""').replace(/\./g, '"."') + '"'
                   ^
    at escape (https://deno.land/x/postgresjs@v3.3.4/src/types.js:215:20)
    at https://deno.land/x/postgresjs@v3.3.4/src/types.js:211:22
    at Array.map (<anonymous>)
    at escapeIdentifiers (https://deno.land/x/postgresjs@v3.3.4/src/types.js:211:13)
    at Object.select [as fn] (https://deno.land/x/postgresjs@v3.3.4/src/types.js:140:12)
    at Builder.build (https://deno.land/x/postgresjs@v3.3.4/src/types.js:72:17)
    at stringifyValue (https://deno.land/x/postgresjs@v3.3.4/src/types.js:110:38)
    at stringify (https://deno.land/x/postgresjs@v3.3.4/src/types.js:101:16)
    at build (https://deno.land/x/postgresjs@v3.3.4/src/connection.js:225:20)
    at execute (https://deno.land/x/postgresjs@v3.3.4/src/connection.js:170:7)

[porsager]

Yeah, I've been playing around with that myself, and there are some fixes needed to properly support it.

[porsager]

Oh wait.

Just do like this ;)

sql`
  select *
  from test
  where (${ sql(['a', 'b']) }) > (${ 2 }, ${ 4 })
`

[jauco]

Well, that doesn’t work if you don’t know the amount of columns beforehand right?

I have an app where the user can sort by one ore more columns and then I use keyset based pagination.

[porsager]

Right, so that's order by and not where ;)

For that you could do like this:

const ordering = [{ column: 'age' }, { column: 'city', direction: 'desc' }]

const result = await sql`
  select * from users
  order by ${ ordering.map((x, i) =>
    sql`${ i ? sql`,` : sql``} ${ sql(x.column) } ${ x.direction === 'desc' ? sql`desc` : sql`asc` } `
  )}
`

[jauco]

The order by wasn't the problem. That works. The where is needed for keyset pagination. (see: https://use-the-index-luke.com/no-offset ). I have an array of columns, I can order by them. But to do pagination I now also have an array of values for each of those columns to select the row to start from. I can provide the array of columns on the left hand side of the comparison query, but I can't insert the array of values on the righthand side.

[porsager]

Sorry, I got ya, didn't read properly ;)

I guess you could glean a solution from the order sample though ;)

const result = await sql`
  select * from users
  where (${ sql(['a', 'b']) }) > (${ [2, 4].map((x, i) => sql`${ i ? sql`,` : sql``}${ x }`) })
`

[jauco]

That works in the where query as well? Cool! Thanks!

[porsager]

It should, but didn't test 😋