Rails3的ActiveRecord - 撷取具有A和B的所有记录(通过的has_many关系) [英] Rails3 ActiveRecord - Fetching all records that have A and B (through a has_many relationship)
问题描述
我有以下型号的关系:
类文章<的ActiveRecord :: Base的
的has_many:标签,:通过=> :article_tags
结束
类ArticleTag<的ActiveRecord :: Base的
belongs_to的是:标签
belongs_to的:文章
结束
类标签<的ActiveRecord :: Base的
的has_many:文章:通过=> :article_tags
结束
现在我要加载所有的标签既标记A和标记B。
的文章我是pretty的新轨道和它一直以来我没有任何严重的web开发/ SQL工作了几年,但对我的生活中,我想不出如何能构建一个ActiveRecord查询要做到这一点。
一种方法如下做到这一点在本机SQL将是:
选择*从文章的一个
INNER JOIN article_tags为ON at.article_id = a.id
INNER JOIN标签的t ON at.tag_id = t.id
WHERE t.name ='A'
相交
选择一个*从文章一
INNER JOIN article_tags为ON at.article_id = a.id
INNER JOIN标签的t ON at.tag_id = t.id
WHERE t.name ='B'
现在可能不是最有效的SQL,但它的工作原理。时至今日我想不出在SQL更好的解决方案。
更新:进一步的研究使我这个SQL语句:
选择。* FROM article_tags时,文章一,标记TM
WHERE at.tag_id = t.id
AND(t.name ='A'或t.name ='B')
AND a.id = at.vehicle_id
GROUP BY a.id HAVING COUNT(a.id)= 2
这看起来简单(更简洁),SQL,但在没有更有效。不过,我也许可以更方便地使用这个SQL构造的find_by_sqlActiveRecord的查询。
如何最好地做这种查询用ActiveRecord(preferably而不诉诸SQL)任何指导,将大大AP preciated。
解决方案我最终解决我自己的问题
适用范围:标签,拉姆达{|标签,*标签|
标签= tags.unshift(*标记)
加入(:标签)。
其中,(低(tags.name)='+ tags.uniq.collect {| T | t.to_s.downcase}。加入(或更低(tags.name)=')+')。
组(articles.id)。
有(计数(articles.id)=#{} tags.count)
}
所以,现在我可以在我的控制器做到这一点:
@tagged_articles = Article.tagged(A,B,C)
和 @tagged_articles
将包括所有标记所有标记A,B的文章,和'C'。
I have the following model relationships:
class Article < ActiveRecord::Base
has_many :tags, :through => :article_tags
end
class ArticleTag < ActiveRecord::Base
belongs_to :tag
belongs_to :article
end
class Tag < ActiveRecord::Base
has_many :articles, :through => :article_tags
end
Now I want to load all the Articles that are tagged with both tag "A" and tag "B".
I'm pretty new to Rails and it has been a few years since I did any serious web-dev/SQL work but for the life of me I can't figure out how one would construct an ActiveRecord query to do this.
One way to do it in native SQL would be as follows:
SELECT a.* FROM articles a
INNER JOIN article_tags at ON at.article_id = a.id
INNER JOIN tags t ON at.tag_id = t.id
WHERE t.name = 'A'
intersect
SELECT a.* from articles a
INNER JOIN article_tags at ON at.article_id = a.id
INNER JOIN tags t ON at.tag_id = t.id
WHERE t.name = 'B'
Now that might not be the most efficient SQL but it works. At this late hour I can't think of a better solution in SQL.
Update: Further investigation has lead me to this SQL:
SELECT a.* FROM article_tags at, articles a, tags t
WHERE at.tag_id = t.id
AND (t.name = 'A' OR t.name = 'B')
AND a.id = at.vehicle_id
GROUP BY a.id HAVING COUNT(a.id) = 2
This seems like simpler (less verbose) SQL but in no more efficient. However, I can probably more easily construct a "find_by_sql" ActiveRecord query using this SQL.
Any guidance on how to best do this sort of query with ActiveRecord (preferably without resorting to SQL) would be greatly appreciated.
I ended up solving my own problem. I constructed a named scope as follows:
scope :tagged, lambda { |tag, *tags|
tags = tags.unshift(*tag)
joins(:tags).
where("lower(tags.name) = '" + tags.uniq.collect{ |t| t.to_s.downcase }.join("' OR lower(tags.name) = '") + "'").
group("articles.id").
having("count(articles.id) = #{tags.count}")
}
So, now I can do this in my controllers:
@tagged_articles = Article.tagged('A', 'B', 'C')
And @tagged_articles
will include all the articles tagged with all of the tags 'A', 'B', and 'C'.
这篇关于Rails3的ActiveRecord - 撷取具有A和B的所有记录(通过的has_many关系)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!