Pg-promise: Support for ordering by IN clause

[OrKoN]

Hey,

I have the following query:

  const data = await db.any(`SELECT * ... WHERE id IN ($1^)`, pgp.as.csv(ids.map(i => i.id)));

It would be great if the lib supported a corresponding ORDER BY statement so that I could order by the order of ids in the IN clause. I am not sure if it is easily possible so this is just a topic for discussion.

P.S. I have not found anything related to it in the WIKI

[vitaly-t]

I'm not quite clear on this. ORDER BY is part of your query string, so what stops you from using it there?

[OrKoN]

That it is ugly in PostgreSQL. See http://stackoverflow.com/questions/6822651/postgresql-order-by-values-in-in-clause

[vitaly-t]

Ok, but in that example it is static, right? Which means you can just specify it in the query string.

Or are you asking about some sort of a dynamic ORDER BY? If so, then please provide an example for that.

[OrKoN]

Yes, it would be great if the library provided a shortcut for this:

    ORDER BY
     CASE id
      ${ids.map((item, i) => 'WHEN \'' + item.id + '\' THEN ' + i).join('\n')}
     END

This kind of ORDER BY clause is always based on what is passed as $1^.

I am not sure how this could look like in the library though.

[vitaly-t]

By the way, in your original example you are using the old syntax. Here's the new one:

const data = await db.any(`SELECT * ... WHERE id IN ($1:csv)`, [ids.map(i => i.id)]);

[OrKoN]

Perhaps?

const data = await db.any(`SELECT * ... WHERE id IN ($1:csv) ORDER BY $1:orderBy`, [ids.map(i => i.id)]);

[vitaly-t]

According to the general syntax, there can be anything for the ORDER BY, it is impossible to generalize.

For your task you should just generate a string that will contain the list of all the WHEN/THEN statements, and then insert it.

There are many ways to do it, and they are all simple.