Sort array (resultset) according to a predefined array

Question

I am fetching data from an SQL database given a list of primary keys; the data returned is not always in the same order and I need to re-order them to preserve the original array index.

For example :

var ids = [2, 12, 5, 3, 10];
var query = 'SELECT * FROM tbl_foo WHERE id IN (' + ids.join(',') + ')';

db.query(query, null, function (result) {

   console.log(result.rows);
   // [ { id: 2, ... },
   //   { id: 3, ... },
   //   { id: 5, ... },
   //   { id: 10, ... },
   //   { id: 12, ... } ]

});

Note: nevermind the SQL injection warnings, this is an example; KISS.

How would I preserve that order, or would I efficiently order the resultset in the given original one?

Thanks!

Clodoaldo Neto · Accepted Answer · 2014-07-04 15:07:15Z

2

unnest the ids array along with an index into a set. Then inner join that set to the target table using the generated index to order. In this example t is the target table generated by generate_series. SQL Fiddle

with t as (
    select id
    from generate_series(1, 20) s (id)
),
a as (
    select
        unnest(array[2, 12, 5, 3, 10]) as id,
        generate_series(1, array_length(array[2, 12, 5, 3, 10], 1)) as i
)
select t.*
from
    t
    inner join
    a using (id)
order by a.i

The above is a self contained example. In you code you will use the existent table in place of t. In javascript use String.replace() to pass the ids array so you don't have to mess with string building

var ids = [2, 12, 5, 3, 10];
var query = '\
    with a as ( \
        select \
            unnest(array[%ids]) as id, \
            generate_series(1, array_length(array[%ids], 1)) as i \
    ) \
    select t.* \
    from \
        t \
        inner join \
        a using (id) \
    order by a.i \
'.replace(%ids, ids);

edited Jul 4, 2014 at 15:07

answered Jul 4, 2014 at 14:47

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clodoaldo Neto Over a year ago

@Yanick Updated with simplified real javascript code

Collectives™ on Stack Overflow

Sort array (resultset) according to a predefined array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related