Sort Order / Weighting System

Posted: Wed Jun 13, 2012 3:30 pm

All-

I've been tasked with creating a media gallery on a similar level (not nearly as complex) to the project I started as a side hobby but I'm running up against a brick wall here.

I need to create a weighting system by which the user can assign a value to a given media file so that the gallery can sort and display in the user defined sort order.

First.. is there a specific name for this type of weighting system? I've searched all over using terms like "php weight system" or "user defined sort order php" and cannot find anything specific enough other than Weight watchers and the like.

Second... rather than have an "up" / "down" arrow that the user would have to click through moving the entry one place at a time, I'm thinking more along the lines of displaying the weight integer in a text field so that the user can specify the exact position in the set the media should be placed.

Other info:

Dataset "shouldn't" be more than 5,000 records
Dataset does NOT follow a sub tree hierarchy, media files have a category # assigned to them so the sort order could follow one continues number scheme from top to bottom.

-----------------------------------------

My line of thinking:

1: Create a column called "order" and assign it a value based on the last weight + 1 upon media insert into gallery.

2: Given users input on the weight, grab all records from that position down and store them in an array. Assign the new position to this record and iterate over the stored array to update the weights + 1. (not very sure how efficient this would be)

Are there better / more efficient ways you've accomplished this type of scenario?

Posted: Wed Jun 13, 2012 4:10 pm

I did pretty much exactly as you are suggesting when I needed sorting capabilities for Content Plus for a project. Adding a `sorder` field to categories and pages, and then a text field to add the weight when creating categories and pages. Then when I call results I use something like

ORDER BY sorder ASC, title ASC

On a side note I also added a `related` field for categories, to use for sub-categories; where 0 is a "main" category, and any other number is the $cat id of the parent category. Worked pretty well and fairly easy to modify.

Although I didn't add anything fancy to help calculate the weight as it would depend on some of the form selections; which would need to be updated dynamically. For my purposes I always knew the position I wanted when creating content or cats...

Posted: Thu Jun 14, 2012 10:12 am

So basically... using a top to bottom numbering scheme (because the gallery doesn't need sub directories) for the "sorder"... using the approach I mentioned in the first post should fit the bill?

example:

1: gallery contains 500 media items.
2: media item 501 is inserted but I want it to be 2nd in the sorder.
3: query all media files where sorder >= 2 AND media_id != 501 then store them in an array.
4: update 501 = 2
5: iterate over the array updating each media file to reflect new sorder as current sorder + 1

#5 Is where I'm concerned. It seems to me that it would be highly inefficient to iterate over an array and make a call to the database for each item to update the field. Is there another way to update all those items with one call to the database?

Maybe something like this...

UPDATE example_table
SET sorder= sorder + 1
WHERE sorder >= 2 AND media_id != 501

Posted: Thu Jun 14, 2012 11:47 am

Ya I see where that could get a little complicated. For my purposes I sorted by 2 columns so that I could have multiple pages with the same sort order value, but you do lose some element of control with ordering that way. When I have multiple pages with the same sort value it then sorts by title next.

I agree that 500 queries to update all other media isn't the greatest solution. Took a quick look at the fixweight function used for blocks and doesn't look like there is any alternate solution there.

Just thinking aloud, I suppose you could add support for fractional values in the sorder field... or maybe a second sorting column?

ORDER BY sorder ASC, sorder2 ASC

Where sorder2 is only populated if there is content that already has that sorting weight? But I'm sure that would complicate things, possibly too much.... Certainly open to ideas from others too Smile

Posted: Thu Jun 14, 2012 1:26 pm

Appreciate the insight spastic, I'm going to have to tinker with this for awhile to see what works and what doesn't work. I didn't realize this sort of thing would as complicated as it appears to be.

Thanks again bud!

-----

Edit***

Code:

$db->sql_query('UPDATE '.$prefix.'_test


            SET sorder = sorder + 1


            WHERE sorder >= 3');

The above code works pretty dadgum fast and seems very effective at getting the job done. Seems like it will do the trick. It also paves the way to move the item to any place in the order based on the users discretion.

Posted: Fri Jun 15, 2012 2:43 pm

Here is the method I'm using for this particular instance and am posting here for future readers.

$myPlace = 'A database select to get my "current" position in the order'
$value = 'User submitted value as to where this item should be placed in the order'

Code:

if($value > $myPlace){


      $siblingRecord = $myPlace + 1;


      $db->sql_query('UPDATE '.$prefix.'_cbhp_media


            SET sorder = sorder - 1


            WHERE sorder BETWEEN '.$siblingRecord.' AND '.$value);   


   }else{


      $siblingRecord = $myPlace - 1;


      $db->sql_query('UPDATE '.$prefix.'_cbhp_media


            SET sorder = sorder + 1


            WHERE sorder BETWEEN '.$value.' AND '.$siblingRecord);


   }


   


            


   $db->sql_query('UPDATE '.$prefix.'_cbhp_media


            SET sorder = '.$value.'


            WHERE media_id = '.$media_id);

This method does not leave gaps in the ordering so that all items are numbered sequentially.

Posted: Fri Jun 15, 2012 4:57 pm

Very nice, I'm glad you got it "sorted" Smile

out of curiosity what happens to position #1, does it eventually become a negative number?

Posted: Fri Jun 15, 2012 9:54 pm

Touché

As for #1... I'm not sure I follow you.

Each item has it's position in the order beginning with 1 and ending at however many items are in the gallery based on which was uploaded first.

Example:

1
2---
3 |
4 |--
5 |
6---
7
8

If we want to move 7 into the number 2 slot, we first need to know our own place in the order. We then compare the two in order to figure if we are going up or moving down. Once we know, then we choose our sibling in the direction we are going and call an update statement to update all the records between our siblings position and the position i'm going... to be added by 1 (I'm moving up) or subtracted by 1 (I'm moving down). This leaves us with a gap which we fill with the record that is being moved.

Position 1 should always be 1.

Posted: Fri Jun 15, 2012 10:56 pm

ok that makes sense and a little different than what I thought it was doing at first look. nicely done.