Keeping Order with mySQL (Mildly OT)

[Anonymous Monk]

I'm working on a project in Perl that would need to have certain items ordered according to user preference (similar to nodelets here.) My initial thought was in remeberance of the days of qbasic--numbering lines in increments of ten and if something was to be added between 20 and 30, I'd add a 25 between them. This obviously has the downfall in that if you messed with your code enough, it would always eventually need to be renumbered.

So I scratched that idea. Even if I used reals, it still would not be as clean as I would like. It would be possible to renumber all elements when the user chose to modify one, but that would be terribly inefficient when the number of total number of elements increased. My second idea would be to set a cron to run at 2AM (or the slowest time of activity for the server) and renumber _all_ elements for _all_ users to keep things clean. (The numbers integral.)

Needless to say, I'm not too happy with any of these ideas. There has got to be a more efficient way! Anyone have any insight onto this topic?

[Masem]

If you don't mind a few extra SQL calls, you can do a pseudo linked-list; that is, have each element have a 'previous' entry that points to the unique ID of the next element in the list, or NULL if this is the first entry. Then you can do a loop over a SQL statement such as:

my @order;
my $sth_1 = $dbh->prepare( "SELECT id FROM TABLE WHERE previous IS NUL
+L" ) or die $DBI->errstr;
my $sth_2 = $dbh->prepare( "SELECT id FROM TABLE WHERE previous IS ?" 
+) or die $DBI->errstr;
$sth_1->execute() or die $DBI->errstr;
if ( $sth_1->rows() > 0 ) {
    my ($id) = $sth_1->fetchrow_array();
    push @order, $id;
    $sth_2->execute( $id ) or die $DBI->errstr;
    while ( $sth_2->rows() > 0 ) {
       my ( $id ) = $sth_2->fetchrow_array();
       push @order, $id;
       $sth_2->execute( $id ) or die $DBI->errstr;
    }
}

(That code could probably be optimized for logic of course.)

-----------------------------------------------------
Dr. Michael K. Neylon - [e-mail] || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

[JayBonci]

There are a few things that I can think of to do, that wouldn't involve much SQL.

The SQL Way:

• Make sure you CREATE INDEX mycleverindexname on mybadasstable (myslickcolumn) This will speed you up a ton, and the space tradeoff is almost always worth it if you're going to be doing a lot of sorting on it.
• Re-ordering them really isn't scalable. That cron job is going to sting if your site gets above X number of users.
Or you can do it the perl way
• I'd suggest keeping a preferences table with a column for this arrangement, such something from a Data::Dumper dump of an array of primary key entries, or even something easily parsed, such as a comma delineated string of Primary Key entries. This "caching" of sorts is very useful, and quite scalable, since you only have to update one record when you need to change it. Plus rearranging it you can do very quickly in perl, rather than having some sort of kludgy re-order logic in SQL.
  
  Basically, keep a string of "34,48,29,32,15" where each one corresponds to your "nodelet" like item, then sort it, rearrange it, hash it, or serve it with a side of cole slaw. Whatever you'd like.

    my @array = split("," $sql_result);
  

Typically, I'd like to think that perl is better for sorting small to moderate result sets. For instance instead of doing a huge "select" with a parameter, and then sorting by a certain value, then doing the "order by" statement when you know that you're only going to get a few results, bite the bullet, get out of your sql (and whatever locks it might have) and do it quickly in perl.

Hope this helps.     --jay

[screamingeagle]

you could keep a column in your "user" table named "sort_order" which stores the display sequence as chosen by the user. so whenever you needed to display the data , all you need to do is sort the data according to "sort_order" rather than the primary key.