Updating sort_index attr on a collection of models
Hey there!
I've got a collection of models (Product) and each product has an attribute called sort_index that allows the store owner to set a specific order for their products. The UI allows them to drag/drop them around to set the order. Because of that, I need to update the index of all of the products. Is there a better way to handle this? This solution obviously doesn't scale well. Also, when thinking about throwing pagination to the mix, it falls apart because then we can't rely on the index of each product in the list (item 1 on page 2 should not have a sort index of 0 for example)
Here's a gif of the UI: https://cl.ly/jpad
With that in mind, how might you all implement the API side of things?
Hey Sean,
Interesting problem, I remember features like this from using Joomla when ordering modules, it's a handy feature to have! While I haven't personally implemented something like this yet, I think the way I would approach this is to limit how many items I allow the user to sort, so it becomes similar to a "hot items" or "promoted items" feature. The shop owner probably doesn't really need to manually sort all of their products, and after the first few can go to a normal sort by ID, product name, price, etc...
So you could setup your products table to have your sort_index columns, any products that have a value in the sort_index are considered the "hot items" - any products without will just be regular products. You could then setup a scope, something like scope :hot_items, -> { where.not(sort_index: nil) } that would return all the products that have a value in the sort_index. Since you'd limit how many items can be sorted like this, you really shouldn't run into scaling issues.
As for your pagination issue, you have a few approaches to this. With my suggestion above, you could just display two separate product displays on the same page, one for the hot items, another for the remainder. Or you could use something like Kaminari's array pagination which you could then do two queries to build your final products list object... first query for the hot items, then a second query for the remainder and merge the two queries into a single array of products.
I'm drawing a bit of a blank for an alternative method, hopefully others will chime in with some other ideas!
Great point on sorting a subset @Jacob, although I would imagine you'd possibly need separate sorting indexes for each of those potentially if the same items are in both groups.
@Sean, when you say "obviously isn't scalable" what do you mean by that? You're making a single update for only the affected records which doesn't sound like it will have significant problems. If you drag an item to a different position (say 3 foward) you need to update that record and the other 3 that you moved it past. That's very quick to simply change a column in a few records and should be done all at once in a single database transaction.
A useful gem for this is acts_as_list which will handle all the queries for you and let's you just worry about calling the right methods when a user drags an item. I've used it anytime I need draggable items and it works great. You can also check out how they handle the update queries to adjust the indexes if you want to roll your own.
One question I'd have is how you envision the UI to work when you want to drag an item across pages? Something like how iOS and Android let you hover on the side of the screen to change pages?
Thanks for the replies @chris, @jacob!
I've thought about having just a subset of orderable items as well @jacob. Another fun fact is that products can belong to stores, sales, and collections (think like a pinterest board), and they all have a unique sort index through their join tables (store_product.sort_index)! That's all working great so far.
By not scalable I mean that I don't want to render all 100, 200, 500, products that a store might have so that they can drag and drop to reorder things. A subset like featured products might work, but I'd have to sell that to the store owners and to my boss.
Also, I haven't even thought about dragging a product across pages.. I'll check out acts_as_list gem and think about it a bit!
Great suggestion on acts_as_list Chris, I'll have to save that one!
I wonder if you could do some sort of infinate scroll to keep from having to deal with multiple pages? Although you're still going to have to end up implimenting some sort of search / filter feature - it may save you from having to deal with dragging your selection across paginated results...
@jacob, yeah, that's exactly what I'm thinking! I believe that acts_as_list might make this a lot easier since I can deal with only the dragged item and let aal deal with the complicated logic for now. There's just not enough hours in the day!
Ah yeah that makes sense. From a usability standpoint, it would feel impossible to drag one of those cards through a big list.
This got me thinking about how Shopify does this. It appears that you can choose the global sort order and if you do a manual sort you get this:
If you choose to sort Manually, you can click any of the products shown to drag it to a new position in the list. The collection will be displayed on your online store under the Featured heading.
That solves the problem of it being awful to try to order only a few products out of say 500. The ones you care about sorting to the top you probably want as "featured" which makes perfect sense.
This kind of gives you the best of both worlds. Simplicity in functionality but the ability to order the ones you do care about. Might be a decent option for solving that problem.
Alright, so I think I'm good here! I was pretty close to having it done manually, but acts_as_list got me the rest of the way there. Now, on dragend I'm getting the current index of the item in the list, and sending an update request for that product only. From there acts_as_list is taking care of updating the rest for me. This could work with infinite scrolling, and I think that whenever the user is filtering the product list, I'll just disable those drag/drop buttons until I figure out a way to extract the actual index within the scope of the whole list.
Sounds like it turned out nicely! And that makes perfect sense for the infinite scroll pagination.
As for when the list is filtered... how about sending both the ID of the item you're moving and the item you moved it before. Then server-side you can look up the position of the second record and then insert just before that. That might work for that case?
Of course, it's always going to be a little weird when looking at a filtered list since the top may not be the real top of the list and that might be confusing to users.
Yeah, I thought about that too, or grabbing before and behind. The gem is pretty quicky but it'll do in a pinch! Thanks for all the help!