您如何在mysql或rails中执行此操作 [英] How do you do this in mysql or rails

问题描述

假设您有一个posts表和一个tag表，它们都由post_tags表关联。

Say you have a posts table and a tags table, and both are related by a post_tags table.

so

个帖子具有ID /主题/正文列

posts has id/subject/body columns

标签具有ID /名称

post_tags具有ID / post_id / tag_id

post_tags has id/post_id/tag_id

在rails术语中，我有一个Post模型，该模型具有许多通过AssetTags生成的标签。

in rails terminology, I have a Post Model that has many Tags through AssetTags.

我正在尝试查询具有2个特定标签的帖子。
，所以如果有一个rails标记和一个mysql标记，我希望查询返回一个仅包含这两个标记的帖子。

I'm trying to query for a post that has 2 specific tags. so if there is a rails tag and a mysql tag, I want a query that returns a post that only has those two tags.

有意义吗？

有什么方法可以通过activerecord（我正在使用搜索逻辑）或mysql吗？

Any way to do this with activerecord (I'm using search logic) or mysql?

推荐答案

此SQL返回包含两个标签的帖子。

This SQL returns the posts that contain both tags.

select 
  p.* 
from 
  posts p
  ,asset_tags atg1
  ,asset_tags atg2
  ,tags t1
  ,tags t2
where
  p.id = atg1.post_id
and t1.id = atg1.tag_id
and t1.tag = 'MySQL' 
and p.id = atg2.post_id
and t2.id = atg2.tag_id
and t2.tag = 'Rails'
;

关于通过Active record进行操作，另一种方法是查询每个标签，然后&得到的数组得到两者的交集。

As for doing it via Active record, an alternative would be to query for each of the tags and then & the resulting arrays to get the intersection of the two.

这篇关于您如何在mysql或rails中执行此操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！