Build a table which has a Field of User_ids, and then querying for a particular User_Id

Question

I need some help building a table and then getting data from that table in Rails 3.

Here's the break down:

Models - 3 models involved here they are:

• Thread has many participants
• Participants belong to thread
• Users

Activity table:

id | thread_id | participants

Example records would look something like:

1 | 300 | 3,1,5,67,13
2 | 333 | 3,12
3 | 433 | 1,12
4 | 553 | 1,12, 67

Where participants, is a list of user_ids, if there is a better way to store the user_ids please let me know. I haven't built this yet.

After I populate the activity table. I then want to be able to query along the lines of: Select all Activity records where the participant_id of 67 is included in the participants field.

I hope the above is clear, if not please let me know. Ideas? Thoughts? Suggestions.

Thanks

Answer 1

While it's tempting to store multiple values in a column, it always ends up with someone getting hurt. You're better off building a join table to relate the models.

For example you could do this:

class DiscussionThread < ActiveRecord::Base
  has_many :participations
  has_many :participants, :through => :participations
end

class Participation < ActiveRecord::Base
  belongs_to :discussion_thread
  belongs_to :participant, :class_name => "User", :foreign_key => :user_id
end

class User < ActiveRecord::Base
  has_many :participations
  has_many :dicussion_threads, :through => :participations
end

That gives you three tables:

table: discussion_threads
columns: id

table: participations
columns: id | discussion_thread_id | user_id

table: users
columns: id

To find the threads in which a user is participating, just do:

@user.discussion_threads

And to find the users participating in a thread:

@discussion_thread.participants

Note: Thread is a reserved word in Ruby, so I've renamed it DiscussionThread

EDIT

mind showing an example of how to serialize an array of ids and then query against them?

You awaken in the middle of the night, and under the power of a strange compulsion you go to your computer and create this migration:

rails g model Abomination horror_ids:text

and model:

class Abomination < ActiveRecord::Base
  serialize :horror_ids
end

You test it to make sure it can store an array:

@abomination = Abomination.create(:horror_ids=>[2,33,42])
@abomination.horror_ids # => [2,33,42]

So what? You know that behind the scenes Rails converts it to YAML, which looks like this:

---\n
- 2\n
- 33\n
- 42\n

Again compelled by that wierd urging, you wonder "How could I search for a particular id stored in this column?". Well, it's just a string, right? You know how to find that in a text field:

cthulhu = 33
Abomination.where(["horror_ids LIKE '%- ?\n%'",cthulhu]).first

With increasing dread, you realize someone might stumble across this and think it was actually a good idea. It must be destroyed! But you are unable to type rm -rf *, instead the strange force makes you consider the quirks that a future mindless follower of Cthulhu developer might need to know, such as

@abomination = Abomination.create
@abomination.horror_ids # => nil

@abomination = Abomination.create(:horror_ids=>[])
@abomination.horror_ids # => []

@abomination = Abomination.create(:horror_ids=>"any string value can go here")
@abomination.horror_ids # => "any string value can go here"

And the fact that serialized data can get corrupted when the column size is too small to accommodate it all.

You make one last ditch effort to kick out the power cord, but it is too late, the gibbering, insane consciousness that has taken control of you posts the code on StackOverflow for the whole world to see. Finally you collapse into a troubled sleep. The next day, realizing what you've perpetrated, you give up coding forever and become an accountant.

Moral

Don't do this