Thread: Auto Ordering

Hi All,

I have a table of questions  to be displayed to the user.

Table: questions
question_id SERIAL
page int
order int
question varchar(100)
answers text

The field order is how the questions are sorted.  How can I change change
question 5 to be number one and have all the other questions re-order
themselves?

Thanks in advance.
Shawn

-------
Shawn T. Walker      <--->   shawn@netcrafters.com

In the example you give, you could do the changes with two UPDATE
commands:

1) UPDATE questions SET order=0 WHERE order=5;
2) UPDATE questions SET order=order+1 WHERE order<5;

It becomes more tricky when you try and move a question to a position
other than the first one (e.g. question #6 to move to position #3 and all
other questions to be shifted accordingly).

This would take three UPDATEs:

1) UPDATE questions SET order=0 WHERE order=6;
2) UPDATE questions SET order=order+1 WHERE order>=3 and order<6;
3) UPDATE questions SET order=3 WHERE order=0;

Perhaps other posters have more efficient or shorther methods?

Two things to bear in mind:

a) You may experience problems with UPDATEs of the 'SET order=order+1'
type if you have declared a unique index on order
b) I think order is a reserved keyword (it's used in ORDER BY clauses) so
you may want to rename that field.

HTH,

Regards,

Stuart.

On Wed, 27 Oct 1999, Shawn T. Walker wrote:

> Hi All,
>
> I have a table of questions  to be displayed to the user.
>
> Table: questions
> question_id SERIAL
> page int
> order int
> question varchar(100)
> answers text
>
> The field order is how the questions are sorted.  How can I change change
> question 5 to be number one and have all the other questions re-order
> themselves?
>
> Thanks in advance.
> Shawn
>
> -------
> Shawn T. Walker      <--->   shawn@netcrafters.com
>
>
>
>
>
> ************
>

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: rison@biochem.ucl.ac.uk

hi...

> Table: questions
> question_id SERIAL
> page int
> order int
> question varchar(100)
> answers text
>
> The field order is how the questions are sorted.  How can I change change
> question 5 to be number one and have all the other questions re-order
> themselves?
>


first, get rid of that SERIAL declaration, you'll have more trouble than
you need getting your shuffling done otherwise...second, rename field order to
something it won't balk at (eg order_of)... ok..

 you could create a trigger function that would check to see if there
are any records with the new question_id and if so, increment them by 1. this
would create the following cascade:

5 becomes 1. is there another record with 1? yes? then change it to 2.

1 becomes 2. is there another record with 2? yes? then change it to 3.

you get the picture.

or, perhaps simpler: write a pl/pgsql function that takes the oid of the record
(question_id = X) to be altered and what it will be altered to (call it
final_id) that increments records with question_id < X and > final_id
final_id then alters record with OID to final_id. 3 lines of code required (need
one to return a value);

drop function switcharoo(OID, int4,int4);
CREATE function switcharoo(OID, int4, int4) returns int2 as '
DECLARE
  target alias for $1;
  initial_id alias for $2;
  final_id alias for $3;
BEGIN
  update questions set question_id = question_id + 1 where
            question_id < initial_id and
             question_id + 1> final_id;
  update questions set question_id = final_id where oid = target;
 return 1;
END;' language 'plpgsql';


i'd do the latter. its simpler. but cascading triggers are so
gosh-darn neat. (btw, i tested this.. it works quite nice.. just remember to
always append "order by question_id" to your selects to get them in the
proper order)

--
Aaron J. Seigo
Sys Admin

hi..

> Table: questions
> question_id SERIAL
> page int
> order int
> question varchar(100)
> answers text
>
> The field order is how the questions are sorted.  How can I change change
               ^^^^^^^^^^^
oops.. misread this in my haste... ignore my comment re: the serial field and
s/question_id/order/g in switcharoo(oid,int4,int4)

--
Aaron J. Seigo
Sys Admin

At 20:41 +0200 on 27/10/1999, Stuart Rison wrote:


> In the example you give, you could do the changes with two UPDATE
> commands:
>
> 1) UPDATE questions SET order=0 WHERE order=5;
> 2) UPDATE questions SET order=order+1 WHERE order<5;
>
> It becomes more tricky when you try and move a question to a position
> other than the first one (e.g. question #6 to move to position #3 and all
> other questions to be shifted accordingly).
>
> This would take three UPDATEs:
>
> 1) UPDATE questions SET order=0 WHERE order=6;
> 2) UPDATE questions SET order=order+1 WHERE order>=3 and order<6;
> 3) UPDATE questions SET order=3 WHERE order=0;

Here is an alternative method of thinking which I used in the past - it
depends on other factors whether this is good or not. If only the order of
the questions is important, and not the actual number, then you can use
fractions. You can use a floating point field, or a fixed point (numeric)
one, or just an int field that normally gets the numbers 100, 200, 300.

Changing order then becomes very easy:

  UPDATE questions SET the_order=50 WHERE the_order=600;

Will change questions 100,200,300,400,500,600,700
To 50,100,200,300,400,500,700.

From time to time, though, you will have to renumber your questions, to
make sure you don't run out of fraction precision. You can do that with
something like:

SELECT the_order
INTO TABLE temp_numbers
FROM questions
ORDER BY the_order;

CREATE SEQUENCE new_seq INCREMENT 100 START 100;

UPDATE questions
SET the_order = nextval( 'new_seq' )
WHERE questions.the_order = temp_numbers.the_order;

DROP SEQUENCE new_seq;
DROP TABLE temp_numbers;

The idea is to do the renumbering in batch, and have a small penalty in
"real time".

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma