Page 1 of 1

Reordering System

Posted: Sat Apr 04, 2009 9:08 am
by Bruno De Barros
For quite a while now I've been dwelling with this problem:
How to provide the ability to get a list of items and reorder them at your will?
Let me explain. Imagine you have a database table with categories. You want to provide them in different orders on the front-end. For example, you want to allow the administrators to reorder the order that the categories are shown in the navigation bar, but you also want to allow them to reorder how they will be shown in a dropdown jumplist. Not the best example, I know, but bear with me. :)

Right now, my idea for this example is:
The Categories Table (ID, NAME, DESCRIPTION)
An Order Items Table (ORDER_IDENTIFIER, ARG, ORDER)

And I'd have for example:
(ID, NAME, DESCRIPTION)
1 | Category 1 | Just a test category.
2 | Category 2 | With the ordering, this should be in first place
3 | Category 3 | This goes to the middle.

(ORDER_IDENTIFIER, ARG, ORDER)
tabs | 1 | 3
tabs | 2 | 1
tabs | 3 | 2
dropdown | 1 | 2
dropdown | 2 | 3
dropdown | 3 | 1

I hope the example is clear enough. Basically, I have 3 categories, but I have two ways to order them. I can order them for display as tabs in the navigation bar, or for the dropdown list in the website.

Now I use a JOIN in which when the ARG of the order is equal to the ID of the category and the ORDER_IDENTIFIER is equal to what I'm trying to get (for example, let's say I'm trying to get the tabs), and then when they're joined, I know the ORDER of that category.

What do you think of this idea is what I'm wondering. I know that I could just make a TAB_ORDER and a DROPDOWN_ORDER field in the Categories table, but I want to create an ordering system that is completely abstracted from whatever I'm trying to order. For example, in that case, all it would take would be creating three extra order items:
top3 | 1 | 1
top3 | 2 | 3
top3 | 3 | 2

And now I just get the categories' orders for the top3, and I have their new order. I don't know if I'm making myself clear.

So far, the big disadvantages of this method that I could find are:
-> Without a record in the order items table, I can't obtain the categories. If I add a Category 4, for example, without creating any order items for it, it won't show up when I get Category 4. I really have no idea how to sort this one out.
-> It's a JOIN statement, obviously. I was thinking that maybe a ORDERS field could be made which kept the orders for a certain category in an Array. (eg. serialize a PHP array and store it in an ORDERS field). But that would not only make the DB compatible only with PHP, but it would also mean serializing/unserializing the orders every time I want to get them. Or I could instead make my own format for the orders, but it would still not be good, because I'd be making it exclusive now to my application. So the orders table seemed to me like the best way to go about doing this.
What do you think?

Re: Reordering System

Posted: Sat Apr 04, 2009 10:09 am
by allspiritseve
I think you're on the right track. I would be tempted to make another table that held locations (tabs, dropdown) and use the table with the sorting field as a relationship table. That way, adding a category won't make the category show up anywhere, but if you give it a location (by adding a row in the relationship table) then it will. You could set a default of 0 for the sort, so everything would be initially ordered by insert time.

On the front end, I'd recommend jQuery sortable for rearranging items in a list:

http://docs.jquery.com/UI/Sortable

You should be able to tie that to the database with a quick ajax call.

Re: Reordering System

Posted: Sat Apr 04, 2009 10:16 am
by Bruno De Barros
@allspiritsteve that sounds interesting. Wouldn't that mean that I'd have to create a record in the relationships table for each of the locations? That would be the same thing as what I had, with the exception that now the locations would be separated. Maybe with an extra field on the table that held all the locations, which would indicate what table they belonged to.

For example, I could have
tags
dropdown
top3
ALL of them with a field that would indicate that those locations are only for categories. That way, when creating a category, I'd just have to create an order item for each of the locations for categories, to make sure they'd automatically be displayed.

EDIT: Currently I was looking at a DragNDrop plugin, let me see if I can find it... Yep.
http://www.isocra.com/2008/02/table-dra ... ry-plugin/

It seems quite good, and it's easy to make it work with PHP to store the results.

Re: Reordering System

Posted: Sat Apr 04, 2009 10:35 am
by allspiritseve
Bruno De Barros wrote:Wouldn't that mean that I'd have to create a record in the relationships table for each of the locations?
No, it'd mean that you'd have to create a record in the relationship table for each category+location. It is very similar to what you had, but more normalized... and because the location is in it's own separate table, you can attach other content to it as well.
Bruno De Barros wrote:EDIT: Currently I was looking at a DragNDrop plugin, let me see if I can find it... Yep.
http://www.isocra.com/2008/02/table-dra ... ry-plugin/

It seems quite good, and it's easy to make it work with PHP to store the results.
Well, sure, if you use tables for layout then that works great. I prefer lists... either way, JQuery Sortable uses Draggable and Droppable just like that plugin does.

Re: Reordering System

Posted: Sat Apr 04, 2009 10:59 am
by Bruno De Barros
allspiritseve wrote:No, it'd mean that you'd have to create a record in the relationship table for each category+location. It is very similar to what you had, but more normalized... and because the location is in it's own separate table, you can attach other content to it as well.
Yes, that's what I meant, sorry :P I meant to say that for each category I'd have to create a record that'd link the category+location, so if I had 5 different locations for one table, that means that I'd have to insert 5 new order items for each category I created.
allspiritseve wrote: Well, sure, if you use tables for layout then that works great. I prefer lists... either way, JQuery Sortable uses Draggable and Droppable just like that plugin does.
Mhm, I see what you mean, lists would be good as well. Actually, I'd say lists might be an even better way to do it, if you just want the ordering. But if you want to add some extra features (I'm thinking datagrid functionality), tables could be used as well. I'm probably gonna develop both of the solutions, just so that the system already provides a way to reorder everything without, sort of like example usage, or something. :)

Re: Reordering System

Posted: Tue Apr 07, 2009 9:00 pm
by credit123
Oh! very good posting. Thank you!
maison de credit