StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
2168528
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
4
CommunityOwnedDate
2010-02-01T18:50:00.020
CreationDate
2010-01-30T16:23:25.310
FavoriteCount
0
LastActivityDate
2016-08-27T14:44:51.483
LastEditDate
2016-08-27T14:44:51.483
LastEditorUserId
48659
OwnerUserId
224129
ParentId
2168442
PostTypeId
2
Score
254
ViewCount
0
LastEditorDisplayName
text
Body
There are several kinds of many-to-many relationships; you have to ask yourself the following questions: <ul> <li>Do I want to store additional information with the association? (Additional fields in the join table.)</li> <li>Do the associations need to be implicitly bi-directional? (If post A is connected to post B, then post B is also connected to post A.)</li> </ul> That leaves four different possibilities. I'll walk over these below. For reference: <a href="http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html" rel="noreferrer">the Rails documentation on the subject</a>. There's a section called “Many-to-many”, and of course the documentation on the class methods themselves. <h2>Simplest scenario, uni-directional, no additional fields</h2> This is the most compact in code. I'll start out with this basic schema for your posts: <pre><code>create_table "posts", :force => true do |t| t.string "name", :null => false end </code></pre> For any many-to-many relationship, you need a join table. Here's the schema for that: <pre><code>create_table "post_connections", :force => true, :id => false do |t| t.integer "post_a_id", :null => false t.integer "post_b_id", :null => false end </code></pre> By default, Rails will call this table a combination of the names of the two tables we're joining. But that would turn out as <code>posts_posts</code> in this situation, so I decided to take <code>post_connections</code> instead. Very important here is <code>:id => false</code>, to omit the default <code>id</code> column. Rails wants that column everywhere except on join tables for <code>has_and_belongs_to_many</code>. It will complain loudly. Finally, notice that the column names are non-standard as well (not <code>post_id</code>), to prevent conflict. Now in your model, you simply need to tell Rails about these couple of non-standard things. It will look as follows: <pre><code>class Post < ActiveRecord::Base has_and_belongs_to_many(:posts, :join_table => "post_connections", :foreign_key => "post_a_id", :association_foreign_key => "post_b_id") end </code></pre> And that should simply work! Here's an example irb session run through <code>script/console</code>: <pre><code>>> a = Post.create :name => 'First post!' => #<Post id: 1, name: "First post!"> >> b = Post.create :name => 'Second post?' => #<Post id: 2, name: "Second post?"> >> c = Post.create :name => 'Definitely the third post.' => #<Post id: 3, name: "Definitely the third post."> >> a.posts = [b, c] => [#<Post id: 2, name: "Second post?">, #<Post id: 3, name: "Definitely the third post.">] >> b.posts => [] >> b.posts = [a] => [#<Post id: 1, name: "First post!">] </code></pre> You'll find that assigning to the <code>posts</code> association will create records in the <code>post_connections</code> table as appropriate. Some things to note: <ul> <li>You can see in the above irb session that the association is uni-directional, because after <code>a.posts = [b, c]</code>, the output of <code>b.posts</code> does not include the first post.</li> <li>Another thing you may have noticed is that there is no model <code>PostConnection</code>. You normally don't use models for a <code>has_and_belongs_to_many</code> association. For this reason, you won't be able to access any additional fields.</li> </ul> <h2>Uni-directional, with additional fields</h2> Right, now... You've got a regular user who has today made a post on your site about how eels are delicious. This total stranger comes around to your site, signs up, and writes a scolding post on regular user's ineptitude. After all, eels are an endangered species! So you'd like to make clear in your database that post B is a scolding rant on post A. To do that, you want to add a <code>category</code> field to the association. What we need is no longer a <code>has_and_belongs_to_many</code>, but a combination of <code>has_many</code>, <code>belongs_to</code>, <code>has_many ..., :through => ...</code> and an extra model for the join table. This extra model is what gives us the power to add additional information to the association itself. Here's another schema, very similar to the above: <pre><code>create_table "posts", :force => true do |t| t.string "name", :null => false end create_table "post_connections", :force => true do |t| t.integer "post_a_id", :null => false t.integer "post_b_id", :null => false t.string "category" end </code></pre> Notice how, in this situation, <code>post_connections</code> does have an <code>id</code> column. (There's no <code>:id => false</code> parameter.) This is required, because there'll be a regular ActiveRecord model for accessing the table. I'll start with the <code>PostConnection</code> model, because it's dead simple: <pre><code>class PostConnection < ActiveRecord::Base belongs_to :post_a, :class_name => :Post belongs_to :post_b, :class_name => :Post end </code></pre> The only thing going on here is <code>:class_name</code>, which is necessary, because Rails cannot infer from <code>post_a</code> or <code>post_b</code> that we're dealing with a Post here. We have to tell it explicitly. Now the <code>Post</code> model: <pre><code>class Post < ActiveRecord::Base has_many :post_connections, :foreign_key => :post_a_id has_many :posts, :through => :post_connections, :source => :post_b end </code></pre> With the first <code>has_many</code> association, we tell the model to join <code>post_connections</code> on <code>posts.id = post_connections.post_a_id</code>. With the second association, we are telling Rails that we can reach the other posts, the ones connected to this one, through our first association <code>post_connections</code>, followed by the <code>post_b</code> association of <code>PostConnection</code>. There's just one more thing missing, and that is that we need to tell Rails that a <code>PostConnection</code> is dependent on the posts it belongs to. If one or both of <code>post_a_id</code> and <code>post_b_id</code> were <code>NULL</code>, then that connection wouldn't tell us much, would it? Here's how we do that in our <code>Post</code> model: <pre><code>class Post < ActiveRecord::Base has_many(:post_connections, :foreign_key => :post_a_id, :dependent => :destroy) has_many(:reverse_post_connections, :class_name => :PostConnection, :foreign_key => :post_b_id, :dependent => :destroy) has_many :posts, :through => :post_connections, :source => :post_b end </code></pre> Besides the slight change in syntax, two real things are different here: <ul> <li>The <code>has_many :post_connections</code> has an extra <code>:dependent</code> parameter. With the value <code>:destroy</code>, we tell Rails that, once this post disappears, it can go ahead and destroy these objects. An alternative value you can use here is <code>:delete_all</code>, which is faster, but will not call any destroy hooks if you are using those.</li> <li>We've added a <code>has_many</code> association for the reverse connections as well, the ones that have linked us through <code>post_b_id</code>. This way, Rails can neatly destroy those as well. Note that we have to specify <code>:class_name</code> here, because the model's class name can no longer be inferred from <code>:reverse_post_connections</code>.</li> </ul> With this in place, I bring you another irb session through <code>script/console</code>: <pre><code>>> a = Post.create :name => 'Eels are delicious!' => #<Post id: 16, name: "Eels are delicious!"> >> b = Post.create :name => 'You insensitive cloth!' => #<Post id: 17, name: "You insensitive cloth!"> >> b.posts = [a] => [#<Post id: 16, name: "Eels are delicious!">] >> b.post_connections => [#<PostConnection id: 3, post_a_id: 17, post_b_id: 16, category: nil>] >> connection = b.post_connections[0] => #<PostConnection id: 3, post_a_id: 17, post_b_id: 16, category: nil> >> connection.category = "scolding" => "scolding" >> connection.save! => true </code></pre> Instead of creating the association and then setting the category separately, you can also just create a PostConnection and be done with it: <pre><code>>> b.posts = [] => [] >> PostConnection.create( ?> :post_a => b, :post_b => a, ?> :category => "scolding" >> ) => #<PostConnection id: 5, post_a_id: 17, post_b_id: 16, category: "scolding"> >> b.posts(true) # 'true' means force a reload => [#<Post id: 16, name: "Eels are delicious!">] </code></pre> And we can also manipulate the <code>post_connections</code> and <code>reverse_post_connections</code> associations; it will neatly reflect in the <code>posts</code> association: <pre><code>>> a.reverse_post_connections => #<PostConnection id: 5, post_a_id: 17, post_b_id: 16, category: "scolding"> >> a.reverse_post_connections = [] => [] >> b.posts(true) # 'true' means force a reload => [] </code></pre> <h2>Bi-directional looped associations</h2> In normal <code>has_and_belongs_to_many</code> associations, the association is defined in both models involved. And the association is bi-directional. But there is just one Post model in this case. And the association is only specified once. That's exactly why in this specific case, associations are uni-directional. The same is true for the alternative method with <code>has_many</code> and a model for the join table. This is best seen when simply accessing the associations from irb, and looking at the SQL that Rails generates in the log file. You'll find something like the following: <pre><code>SELECT * FROM "posts" INNER JOIN "post_connections" ON "posts".id = "post_connections".post_b_id WHERE ("post_connections".post_a_id = 1 ) </code></pre> To make the association bi-directional, we'd have to find a way to make Rails <code>OR</code> the above conditions with <code>post_a_id</code> and <code>post_b_id</code> reversed, so it will look in both directions. Unfortunately, the only way to do this that I know of is rather hacky. You'll have to manually specify your SQL using options to <code>has_and_belongs_to_many</code> such as <code>:finder_sql</code>, <code>:delete_sql</code>, etc. It's not pretty. (I'm open to suggestions here too. Anyone?)
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POMany-to-many relationship with the same model in rails?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USSteve Folly
UserOwnerUserId
1. USStéphan Kochen
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POMany-to-many relationship with the same model in rails?
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.