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.
7 comments On Postgresql order by field
Is this faster than doing 3 requests to the DB? I know it always seems best to minimise requests but that appears to be 2 SELECTS per item in the array on a quick scan.
Is there any way we can achieve this without firing 2 queries per item, I can achieve this using programming language.Is there any other way?
CREATE OR REPLACE FUNCTION field(text, variadic text[])
RETURNS int AS $$
SELECT i
FROM generate_subscripts($2,1) g(i)
WHERE $1 = $2[i]
UNION ALL
SELECT 0
LIMIT 1
$$ LANGUAGE sql STRICT;
via http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html
Are you wanting a random order or are you talking about presenting it a fixed order– basically give me id 1, then id 3, then 2, etc?
in the first case (random), I’d say to just order it by random. in the second case, I’d create a generic function like this:
CREATE FUNCTION array_search(needle ANYELEMENT, haystack ANYARRAY)
RETURNS INT AS $$
SELECT i
FROM generate_subscripts($2, 1) AS i
WHERE $2[i] = $1
ORDER BY i
$$ LANGUAGE sql STABLE;
then run something like this:
select * from question q
order by array_search(q.id,'{1,3,2,6,9}’::integer[])
Hi,
I want record should be in [2,1,3] order, second if there are more records in then other records are in order,like in [4,5,6…] etc
Did you try the suggestion I posted? (with the function array_search?
Give it a try and let me know how it works (or doesn’t)
This is great! Yes it requires two queries but it’s far less expensive memory-wise when you have to, say, spit a large number of rows into a PDF. Cut my memory footprint by 90% being able to page this and preserve the order of the results. Thank you!