How to order query result according to the order of array elements?

Question

I have this query in Postgres 9.4:

select id from question where id = any(
    array_cat(
        ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
        (select array(
            select id from question where id not in 
                (0,579489,579482,579453,561983,561990,562083)
            and status in (1, -1) 
            and created_at > 1426131436 order by id desc offset 0 limit 10 )
        )::integer[]
    )
)

It returns:

   id
--------
 561983
 561990
 562083
 579453
 579482
 579489
 580541
 580542
 580543
 580544
 580545
 580546
 580547
 580548
 580549
 580550
(16 rows)

But it's not in the right order. I need the result ordered according to the result of the sub array:

array_cat(
        ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
        (select array(
            select id from question where id not in 
                (0,579489,579482,579453,561983,561990,562083)
            and status in (1, -1) 
            and created_at > 1426131436 order by id desc offset 0 limit 10 )
        )::integer[]
    )

How can I do that?

Answer 1

Basics:

• PostgreSQL unnest() with element number

Since you are using Postgres 9.4 you can use the new WITH ORDINALITY:

WITH t AS (
   SELECT *
   FROM   unnest('{0,579489,579482,579453,561983,561990,562083}'::int[])
                  WITH ORDINALITY AS t(id, rn)
   )
(
SELECT id
FROM   question
JOIN   t USING (id)
ORDER  BY t.rn
)
UNION ALL
(
SELECT id
FROM   question
LEFT   JOIN t USING (id)
WHERE  t.id IS NULL
AND    status IN (1, -1) 
AND    created_at > 1426131436
ORDER  BY id DESC
LIMIT  10
);

Explain

1. Since you are using the same array twice I prepend the query with a CTE where you provide your array once. unnest() it immediately WITH ORDINALITY get row numbers (rn) according to order of array elements.

2. Instead of stuffing your subquery into an array and transforming it back, use it directly. Much cheaper. The sort order is derived from the id directly.

3. Instead of excluding IDs from the given array with NOT IN (which can be tricky with NULL values) use LEFT JOIN / IS NULL:

   • Select rows which are not present in other table

4. Just append the two parts with UNION ALL. Parentheses are required to have separate ORDER BY on each leg of the UNION ALL query:

   • Sum results of a few queries and then find top 5 in SQL

5. The JOIN to question in the final SELECT is now redundant, I stripped it away.