无法在 has_many 关系中定义 :joins 条件?
我有一个关系表:
create_table "animal_friends", :force => true do |t|
t.integer "animal_id"
t.integer "animal_friend_id"
t.datetime "created_at"
t.datetime "updated_at"
t.integer "status_id", :default => 1
end
将动物与其他动物联系起来。 在 SQL 中检索关联的最佳方法是:
SELECT animals.*
from animals join animal_friends as af
on animals.id =
case when af.animal_id = #{id} then af.animal_friend_id else af.animal_id end
WHERE #{id} in (af.animal_id, af.animal_friend_id)
我找不到一种方法来用它在 Rails 中创建正确的 has_many 关系。 显然,没有办法为 has_many 提供加入条件。
我目前正在使用 finder_sql :
has_many :friends, :class_name => "Animal", :finder_sql => 'SELECT animals.* from animals join animal_friends as af on animals.id = case when af.animal_id = #{id} then af.animal_friend_id else af.animal_id end ' +
'WHERE #{id} in (af.animal_id, af.animal_friend_id) and status_id = #{Status::CONFIRMED.id}'
但这种方法有一个很大的缺点,那就是破坏了 activerecord 的魔力。 例如:
@animal.friends.first
将无限制地执行 finder_sql,获取数千行,然后获取数组的第一行(并损失几宝贵的秒 / req)。
我猜这是 AR 缺少的功能,但我想先确定一下:) 谢谢
I have a relationship table :
create_table "animal_friends", :force => true do |t|
t.integer "animal_id"
t.integer "animal_friend_id"
t.datetime "created_at"
t.datetime "updated_at"
t.integer "status_id", :default => 1
end
linking animals to others. Best way to retreive associations in SQL is :
SELECT animals.*
from animals join animal_friends as af
on animals.id =
case when af.animal_id = #{id} then af.animal_friend_id else af.animal_id end
WHERE #{id} in (af.animal_id, af.animal_friend_id)
And I can't find a way to create a proper has_many relation in rails with this. Apparently, there's no way to provide joining conditions for has_many.
I'm currently using a finder_sql :
has_many :friends, :class_name => "Animal", :finder_sql => 'SELECT animals.* from animals join animal_friends as af on animals.id = case when af.animal_id = #{id} then af.animal_friend_id else af.animal_id end ' +
'WHERE #{id} in (af.animal_id, af.animal_friend_id) and status_id = #{Status::CONFIRMED.id}'
but this method has the great disadvantage of breaking activerecord magic. For instance :
@animal.friends.first
will execute the finder_sql without limit, fetching thousands of rows, then taking the first of the array (and loosing several precious seconds / req).
I guess it's a missing feature from AR, but I'd like to be sure first :)
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您可以使用视图在数据库级别解决此问题,无论如何这都是正确的方法。
如果您不想为有两种关系的朋友进行双重输入,您可以这样做:
您需要一种方法来决定使用哪个 status_id,以防有两个冲突的情况。 我选择了 MIN(status_id) 但这可能不是您想要的。
在 Rails 中,你现在可以这样做:
这是我的想法,未经测试。 另外,您可能需要一些插件来处理 Rails 中的视图(例如“redhillonrails-core”)。
You could solve this on the database level with a view, which would be the correct method anyway.
If you dont want to have double entries for friends with relations both ways you could do this:
You would need a way to decide which status_id to use in case there are two conflicting ones. I chose MIN(status_id) but that is probably not what you want.
In Rails you can do this now:
This is out of my head and not tested. Also, you might need some plugin for view handling in rails (like "redhillonrails-core").
有一个插件可以满足您的要求。
这里有一篇关于它的帖子。
此处还有一个替代方案。
两者都允许您执行连接条件并且仅使用延迟初始化。
所以你可以使用动态条件。 我发现前者更漂亮,但如果您不想安装插件,可以使用后者。
There is a plugin that does what you want.
There is a post about it here.
There is an alternative here.
Both allow you do to joining conditions and are just using lazy initialization.
So you can use dynamic conditions. I find the former prettier, but you can use the latter if you don't want to install plugins.
在活动记录中创建多对多关系的两种方法是 has_and_belongs_to_many 和 has_many :through。 此网站批评了两者之间的差异。 您不必使用这些方法编写任何 SQL。
The two ways to create many to many relationships in active record are has_and_belongs_to_many and has_many :through. This site critiques the differences between the two. You don't have to write any SQL using these methods.