构建具有User_ids的字段表，并然后查询针对特定USER_ID [英] Build a table which has a Field of User_ids, and then querying for a particular User_Id

问题描述

我需要一些帮助建立一个表，从该表然后让数据在Rails 3中。

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

下面是向下突破：

模式 - 3机型这里涉及到它们是：

Models - 3 models involved here they are:

• 在主题有许多参与者
• 参与者属于线程
• 用户

活动表：

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

，参与者，是user_ids名单，如果有更好的方式来存储user_ids请让我知道。我还没有建立这个呢。

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.

在我填充的活动表。然后，我希望能够沿着线路查询： 选择其中67 participant_id包括在参与所有领域的活动记录。

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.

感谢

推荐答案

虽然这是很有诱惑力的存储在一列多个值，它总是结束了别人受到伤害。你最好建立一个连接表关系模型。

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.

例如，你可以这样做：

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

这是给你三个表：

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

注：发是一个保留字在Ruby中，所以我给它改名 DiscussionThread

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

修改

心态呈现出如何序列ID的数组，然后对他们查询的例子吗？的

您醒来在半夜，然后在一个陌生的强制的权力，你去你的计算机，并创建这个迁移：

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

和模型：

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]

还等什么？你知道幕后的Rails将其转换为YAML，这看起来是这样的：

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

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

再由奇怪的催促下被迫，你想知道我怎么可以搜索存储在该列中的特定的ID？。好吧，这只是一个字符串，对不对？你知道如何寻找，在一个文本字段：

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

随着越来越多的恐惧，你意识到有人可能偶然发现这一点，并认为这实际上是一个好主意。它必须被销毁！但您无法键入的rm -rf * ，而不是怪力，使你考虑的怪癖，未来的<击>邪神盲目跟随开发者可能需要要知道，如

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.

您做一个最后的努力踢了电源线，但为时已晚，该职位上的计算器code整个世界看到采取控制你的叽叽喳喳，疯狂的意识。最后，你崩一家陷入困境的睡眠。第二天，实现你所犯下的，你放弃了永远的编码，并成为一名会计师。

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.

道德

别这样

这篇关于构建具有User_ids的字段表，并然后查询针对特定USER_ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！