User Sortable Entries

Question

There are n related widgets for each user in my webapp. Each widget is represented in the database in the Widgets table. Users can sort their widgets, they will never have more than a few dozen widgets, and they will often sort widgets.

I did not deal with database details, which are inextricable for them very often. What is a good strategy to order them? At first I thought that a simple "sortIndex" column would work fine, but then I began to wonder how to initialize this value. Apparently, this should be a unique value, and it should be greater or less than any other sorting index. However, I do not want to check all other sorting indexes for this user every time I create a new widget. This seems unnecessary.

Perhaps I could have a default sort index of "lower priority"? But then how do I distinguish them? I suppose I could use the create date flag, but then what if the user wants to insert a widget in the middle of all these lower priority widgets?

What is the standard way to deal with such things?

Answer 1

The best way for user editable editing is to store id in a linked list :

 user_id widget_id prev_widget_id ---- ---- ---- 1 1 0 1 2 8 1 3 7 1 7 1 1 8 3 2 3 0 2 2 3 

This will make 5 widgets for user 1 in the following order: 1, 7, 3, 8, 2 ; and 2 for user 2 in the following order: 3, 2

You must make the UNIQUE indexes on (user_id, widget_id) and (user_id, prev_widget_id) .

To get widgets in a given order, you can query, for example, this in Oracle :

 SELECT w.* FROM ( SELECT widget_id, level AS widget_order FROM widget_orders START WITH user_id = :myuser AND prev_widget_id = 0 CONNECT BY user_id = PRIOR user_id AND prev_widget_id = PRIOR widget_id ) o JOIN widgets w ON w.widget_id = o.widget_id ORDER BY widget_order 

To update the order, you will need to update no more than 3 lines (even if you move the entire block of widgets).

SQL Server and PostgreSQL 8.4 implement this function using recursive CTE s:

 WITH -- RECURSIVE -- uncomment the previous line in PostgreSQL q AS ( SELECT widget_id, prev_widget_id, 1 AS widget_order FROM widget_orders WHERE user_id = @user_id UNION ALL SELECT wo.widget_id, wo.prev_widget_id, q.widget_order + 1 FROM q JOIN wo.widget_orders wo ON wo.user_id = @user_id AND wo.prev_widget_id = q.widget_id ) SELECT w.* FROM q JOIN widgets w ON w.widget_id = q.widget_id ORDER BY widget_order 

See this blog post on how to implement this functionality in MySQL :

• Sort Lists

Answer 2

If you have users sorting widgets for your personal tastes, you want to create a lookup table, for example:

 create table widgets_sorting ( SortID int primary key, UserID int, WidgetID int, SortIndex int ) 

Then, to sort custom widgets:

 select w.* from widgets w inner join widgets_sorting s on w.WidgetID = s.WidgetID inner join users u on s.UserID = u.UserID order by s.SortIndex asc 

Thus, all you have to do for new users is to add new rows to the widgets_sorting table. Make sure you specify the foreign key constraint and index for both the WidgetID columns and the UserID columns.

These lookup tables are indeed the best way to resolve the many-to-many relationships that are common to this kind of personalized listing. I hope this indicates that you are in the right direction!

Answer 3

I like to use a two-table approach, which can be a bit confusing, but if you use ORM like ActiveRecord it is easy, and if you write some clever code, it can be manageable.

Use one table to bind the user to sorting and one table to bind the widget, position, and sort. Thus, it’s much clearer what is happening, and you can use a SQL connection or a separate query to retrieve various data from different tables. Your structure should look like this:

 //Standard user + widgets table, make sure they both have unique IDs CREATE TABLE users; CREATE TABLE widgets; //The sorting tables CREATE TABLE sortings ( id INT, //autoincrement etc, user_id INT ) CREATE TABLE sorting_positions ( sorting_id INT, widget_id INT, position INT ) 

Hope this makes sense if you are still confused, comment on this post and I will write you the base code.

Jamie

Answer 4

If you mean that each user assigns their own sort order to widgets, then Eric's answer is correct. Presumably, you should give the user a way to assign a sort value. But if the number is modest, as you say, then you can just give it a screen that lists all the widgets, and either let it enter the order number or display them in order, and the up and down buttons next to them if you want to be fantasy, give him a way to drag.

If the order is the same for all users, the question will be, Where does this order come from? If it’s arbitrary, just assign a sequence number when creating new widgets.