ruby on rails-在范围内使用union

I am using rails 3.0.3. I have got 4 tables.

我正在使用rails 3.0.3。我有4张桌子。

class Artwork
  has_and_belongs_to_many :tag_styles
  has_and_belongs_to_many :tag_subjects
  has_and_belongs_to_many :tag_arttypes
end

class TagArttype
  set_table_name :tag_arttypes
  has_and_belongs_to_many :artworks
end

class TagStyle
  set_table_name :tag_styles
  has_and_belongs_to_many :artworks
end

class TagSubject
  set_table_name :tag_subjects
  has_and_belongs_to_many :artworks
end

All these three tag_tables got a name attribute. how can i make an Artwork scope which accepts a word and puts it in the place of 'Abstract' with the following sql:

所有这三个tag_tables都有一个name属性。我如何制作一个接受一个单词的艺术作品范围，并用以下sql将其放在'Abstract'的位置：

select a.* from artworks as a, tag_arttypes as ta, artworks_tag_arttypes as ata where a.id = ata.artwork_id and ta.id = ata.tag_arttype_id and ta.name = 'Abstract' union
 select a.* from artworks as a,tag_styles as ta,artworks_tag_styles as ata where a.id = ata.artwork_id and ta.id = ata.tag_style_id and ta.name = 'Abstract'union
 select a.* from artworks as a,tag_subjects  as ta,artworks_tag_subjects  as ata where a.id = ata.artwork_id and ta.id = ata.tag_subject_id and ta.name = 'Abstract';

Thanks!

谢谢！

2 个解决方案

#1

Its bit late but this might be helpful for someone. Finally, found the rails way of doing it.

它有点晚，但这可能对某人有帮助。最后，找到了rails的方式。

scope :by_tags, lambda { |tag|
  includes(:tag_styles,:tag_subjects,:tag_arttypes).where('tag_styles.name LIKE ? OR tag_arttypes.name LIKE ? OR tag_subjects.name LIKE ?',"%#{tag}","%#{tag}","%#{tag}").
 select("DISTINCT artworks.*")
}

#2

Its quite simple, if i am correctly understanding your question. Something like below would work :

如果我正确理解你的问题，它很简单。下面的东西会起作用：

scope :filter, lambda {|name| 
"
 (select a.* from artworks as a, tag_arttypes as ta, artworks_tag_arttypes as ata where a.id = ata.artwork_id and ta.id = ata.tag_arttype_id and ta.name = '#{name}') union
 (select a.* from artworks as a,tag_styles as ta,artworks_tag_styles as ata where a.id = ata.artwork_id and ta.id = ata.tag_style_id and ta.name = '#{name}')  union
 (select a.* from artworks as a,tag_subjects  as ta,artworks_tag_subjects  as ata where a.id = ata.artwork_id and ta.id = ata.tag_subject_id and ta.name = '#{name}')
"
}

#1