CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3database-designactiverecordsql-order-by
David Tuite
David Tuite

Reputation: 22643

Change the order of records depending on the user?

Imagine I have a list of todos which are visible by many members of a team. I would like to enable each team member to maintain their own todo list order depending on which todos they think are the most important.

How do I store this ordering at the database level?

Upvotes: 1

Views: 764

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Your database model should look something like this:

enter image description here

This is a pretty standard many-to-many relationship with the "link" table in the middle, but with one twist: the ORDER is part of the alternate key {USER_ID, ORDER} (denoted by U1 in the diagram above) but not part of the primary key. This is what makes it per-user order.

(Disclaimer: I'm not familiar with Ruby on Rails - this answer is purely from the database modelling perspective.)

Upvotes: 1

Chris Bailey
Chris Bailey

Reputation: 4136

You're going to need a separate model which is associated with users and todos. E.g. (assuming that your existing models are called User and Todo:

app/models/todo_orders.rb

class TodoOrders.rb < ActiveRecord::Base

  belongs_to :user
  belongs_to :todo

  validates_uniqueness_of :order_num
end

db/migrate/yyyymmddhhmmss_create_todo_orders.rb

def up
  t.integer :user:id
  t.integer :todo_id
  t.integer :order_num
end

You will need some additional logic in your models to pull the right orders and associate them with your todo items. e.g.

app/models/todo.rb

attr_accessor :cur_order

app/models/user.rb

has_many :todo_orders

def get_todos
  todo_orders.includes(:todo).map do |todo_order|
    todo = todo_order.todo
    todo.cur_order = todo_order.order_num
    todo
  end
end

Thus calling get_todos on a user object will return a list of Todos with the order the are to be displayed stored in their cur_order attribute

Upvotes: 2

MrTheWalrus
MrTheWalrus

Reputation: 9700

I would store the order as a float (probably called 'position' or something similar) in a join table that connects users and todos. Then when you grab the list, you can specify that order in the relation. One of the major reasons to use :has_many :through relationships is when you want to store data about the association itself (rather than about either of the associated models).

Upvotes: 4

Related Questions