3

I have two models, let's say:

Post:
  blog_id: integer
  external_id: integer

Comment:
  blog_id: integer
  external_reference_id: integer

And have some items in my db:

Post.create external_id: 10, title: "Test 1", blog_id: 1
Post.create external_id: 10, title: "Test 2", blog_id: 2
Post.create external_id: 10, title: "Test 3", blog_id: 3

Comment.create external_reference_id: 10, title: "Comment 1.1", blog_id: 1
Comment.create external_reference_id: 10, title: "Comment 1.2", blog_id: 1

Comment.create external_reference_id: 10, title: "Comment 2.1", blog_id: 2
Comment.create external_reference_id: 10, title: "Comment 2.2", blog_id: 2

I need to relate post and comments using the external_ reference, that is:

class Post < ActiveRecord::Base
    has_many :comments, foreign_key: :external_reference_id, primary_key: :external_id
end

class Comments < ActiveRecord::Base
    belongs_to :post, foreign_key: :external_reference_id, primary_key: :external_id
end

What I need is to "scope" the relation between the two on blog_id to be the same.

By this I mean a scenario where I have different blogs on different server/databases, and I'm collecting all the data in a master server. I cannot preserve the original ID, so I store the id as external_id. This means that the Post with external_id: 1 and blog_id: 4 will have all the Comments that have external_reference_id: 1 and blog_id: 4.

I may have lot of comments with the same external_id but only that with the same blog_id really matches.

Surfing the internet, I found a couple of hint, the most popular opinions are something like this:

class Post < ActiveRecord::Base
  has_many :comments, -> (object){ where("comments.blog_id = #{object.blog_id}") }, foreign_key: :external_reference_id, primary_key: :external_id
end

This solution correctly generate the query when doing Post.first.comments:

SELECT COUNT(*) FROM "comments" WHERE "comments"."external_reference_id" = ? AND (comments.blog_id = 1) [["external_reference_id", 10]]

But it fails miserably when trying something more esoteric like Post.joins(:comments).count giving:

NoMethodError: undefined method `blog_id' for #<ActiveRecord::Associations::JoinDependency::JoinAssociation

So far my solution is then:

class Post < ActiveRecord::Base
  has_many :comments, -> (object){ where(object.respond_to?(:blog_id) ? "comments.blog_id = #{object.blog_id}" : 'comments.blog_id = posts.blog_id') }, foreign_key: :external_reference_id, primary_key: :external_id
end

Which works fine, but looks a little bit over-complicated in my opinion. Is there a better way to achieve this?

Enrico Carlesso
  • 6,818
  • 4
  • 34
  • 42
  • Can you explain more about `What I need is to "scope" the relation between the two on blog_id to be the same.`. The same as what, for example? – Max Williams Mar 18 '14 at 17:34
  • What I mean is that a comment belongs to a post only if they match the blog_id field. Assume I have different blogs in separate servers/database and I'm importing all the data in my master server. I cannot preserve ids, so I save the original id in external_id for posts, and the original post_id in external_reference_id for contents. So the post for a comment is the post having external_id matching the external_reference_id and having the same blog_id. I know it may not be the better implementation but that's my data now. – Enrico Carlesso Mar 18 '14 at 19:11
  • Ah, you should add that to the OP as it's useful info. – Max Williams Mar 19 '14 at 09:11
  • I had the same issue and found the response here: http://stackoverflow.com/questions/13893429/how-to-write-scope-with-belongs-to-object – Américo Duarte Aug 18 '16 at 01:41

1 Answers1

0

You can add :conditions to has_many and belongs_to, eg

class Post < ActiveRecord::Base
  has_many :comments, foreign_key: :external_reference_id, primary_key: :external_id, :conditions => ["comments.blog_id = posts.blog_id"]
end

class Comments < ActiveRecord::Base
  belongs_to :post, foreign_key: :external_reference_id, primary_key: :external_id, :conditions => ["comments.blog_id = posts.blog_id"]
end

I've not tested this but give it a go.

Max Williams
  • 32,435
  • 31
  • 130
  • 197
  • That's one of the things I've tried, but it does not work when relations are called: `Comment.first.post` makes a query only in post table, so trying to have `comments.blog_id = posts.blog_id` breaks the query: `SELECT "posts".* FROM "posts" WHERE "posts"."external_id" = ? AND (comments.blog_id = posts.blog_id) ORDER BY "posts"."id" ASC LIMIT 1 [["external_id", 10]]` – Enrico Carlesso Mar 19 '14 at 10:49