Rails 协会 has_one 最新记录 [英] Rails Associations has_one Latest Record
问题描述
我有以下模型:
class Section < ActiveRecord::Base
belongs_to :page
has_many :revisions, :class_name => 'SectionRevision', :foreign_key => 'section_id'
has_many :references
has_many :revisions, :class_name => 'SectionRevision',
:foreign_key => 'section_id'
delegate :position, to: :current_revision
def current_revision
self.revisions.order('created_at DESC').first
end
end
其中 current_revision
是最近创建的 revision
.是否可以将 current_revision
转换为关联,以便我可以执行诸如 Section.where("current_revision.parent_section_id = '1'")
之类的查询?或者我应该在我的数据库中添加一个 current_revision
列,而不是尝试以虚拟方式或通过关联来创建它?
Where current_revision
is the most recently created revision
. Is it possible to turn current_revision
into an association so I can perform query like Section.where("current_revision.parent_section_id = '1'")
? Or should I add a current_revision
column to my database instead of trying to create it virtually or through associations?
推荐答案
我知道您想获取每个部分的最后修订版本的 parent_section_id = 1 的部分;
I understand you want to get the sections where the last revision of each section has a parent_section_id = 1;
我有类似的情况,首先,这是 SQL(请将类别视为您的部分,将帖子视为修订,将 user_id 视为 parent_section_id -对不起,如果我没有将代码移至您的需要,但我必须去):
I have a similar situation, first, this is the SQL (please think the categories as sections for you, posts as revisions and user_id as parent_section_id -sorry if I don't move the code to your need but I have to go):
SELECT categories.*, MAX(posts.id) as M
FROM `categories`
INNER JOIN `posts`
ON `posts`.`category_id` = `categories`.`id`
WHERE `posts`.`user_id` = 1
GROUP BY posts.user_id
having M = (select id from posts where category_id=categories.id order by id desc limit 1)
这是 Rails 中的查询:
And this is the query in Rails:
Category.select("categories.*, MAX(posts.id) as M").joins(:posts).where(:posts => {:user_id => 1}).group("posts.user_id").having("M = (select id from posts where category_id=categories.id order by id desc limit 1)")
这是有效的,它很丑,我认为最好的方法是剪切"查询,但是如果您有太多的部分,在循环它们时会出现问题;您也可以将此查询放入静态方法中,而且,您的第一个想法是,在部分表中有一个 revision_id 将有助于优化查询,但会放弃规范化(有时需要),您将不得不在为该部分创建新修订时更新此字段(因此,如果您打算在庞大的数据库中进行大量修订,如果服务器速度较慢,这可能是个坏主意...)
This works, it is ugly, I think the best way is to "cut" the query, but if you have too many sections that would be a problem while looping trough them; you can also place this query into a static method, and also, your first idea, have a revision_id inside of your sections table will help to optimize the query, but will drop normalization (sometimes it is needed), and you will have to be updating this field when a new revision is created for that section (so if you are going to be making a lot of revisions in a huge database it maybe would be a bad idea if you have a slow server...)
更新我回来了嘿嘿,我正在做一些测试,看看这个:
UPDATE I'm back hehe, I was making some tests, and check this out:
def last_revision
revisions.last
end
def self.last_sections_for(parent_section_id)
ids = Section.includes(:revisions).collect{ |c| c.last_revision.id rescue nil }.delete_if {|x| x == nil}
Section.select("sections.*, MAX(revisions.id) as M")
.joins(:revisions)
.where(:revisions => {:parent_section_id => parent_section_id})
.group("revisions.parent_section_id")
.having("M IN (?)", ids)
end
我创建了这个查询并使用了我的表(希望我为参数命名,它与之前的 Rails 查询相同,但我更改了查询以进行优化);当心小组;包含使其在大型数据集中成为最佳选择,抱歉我找不到与 has_one 建立关系的方法,但我会这样做,但也会重新考虑您在开头提到的领域.
I made this query and worked with my tables (hope I named well the params, it is the same Rails query from before but I change the query in the having for optimization); watch out the group; the includes makes it optimal in large datasets, and sorry I couldn't find a way to make a relation with has_one, but I would go with this, but also reconsider the field that you mention at the beginning.
这篇关于Rails 协会 has_one 最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!