Postgresql order by field

We have been using PostgresSQL in our new product Quiz Stack.

Quiz Stack is a SAAS based product. Which can conduct,manage,analysis quizzes.We are using rails and postgresql as our backend.It’s subdomain app and hence we decide to use postgresql schema based approach for storing/querying data.

While working on one of the feature It was needed to draw some random fixed questions for each user.So each user has different order of questions which is maintain for each specific user.

In Mysql it’s called field function to achieve this ,unfortunately there isn’t anything as of I know in PostgresSql or is it ? 🙂

Well we can obviously write a function which can help you do the same.Let’s the function name be sortbyid.

Following code snippet is a function definition

CREATE OR REPLACE FUNCTION sortByID(anyelement, VARIADIC anyarray) RETURNS bigint AS $$ SELECT n FROM ( SELECT row_number() OVER () AS n, x FROM unnest($2) x) numbered WHERE numbered.x = $1; $$ LANGUAGE 'SQL' IMMUTABLE STRICT;

So here how I get the result from Question table for 3 record with order or id as 2,1,3

select * from question order by sortbyid("id",2,3,1);

In Rails the above would look something like this

Question.order("sortbyid(id,2,1,3)")

I’m not MySQL or PostgreSQL expert but hey that what I found , I know there ton of way to do it some may be even better hence I’m looking forward in comment to find more on information on it.