ruby-on-rails - 当且仅当查询不适用于 Rails 和 Postgres 时，如果包含，则全部关闭

Question

我想要的是查询所有仅具有通过 habtm 关联关联的“fra”语言的媒体。目前，此查询返回包含“fra”的 Media 实例，例如[“fra”，“eng”]。我使用 Postgres。

因此，(1) 我想找到恰好包含“x”语言且没有其他语言的媒体，(2) 找到恰好包含“x”和“y”语言的媒体，(3) 找到完全具有“x”、“y”和“z”等语言的媒体。

class Media < ActiveRecord::Base
    has_and_belongs_to_many :audio_language_records, :join_table => "audio_languages_media", :class_name => "Language"
end

class Language < ActiveRecord::Base
  attr_accessor :code
end

我试过这个，但它返回所有包含“fra”的媒体，而不仅仅是“fra”

Media.joins(:audio_language_records).where("languages.code = ? AND languages.code != ?", "fra", "eng")

以下不按预期工作并返回相同的结果

Media.joins(:audio_language_records).where("languages.code IN (?) AND languages.code NOT IN (?)", ["fra"], ["eng"])

Answer 1

因此，您需要找到存在 language.code 为“eng”但在连接表中没有相同 media_id 但不同 language_id 的条目的媒体。

SELECT * FROM media m1
  JOIN audio_languages_media alm1 ON alm1.media_id = m1.id
  JOIN languages l1 ON alm1.language_id = l1.id 
  WHERE NOT EXISTS(
    SELECT 1 FROM audio_languages_media alm2
    WHERE alm1.language_id != alm2.language_id
    AND alm1.media_id = alm2.media_id
  )
  AND l1.code = 'eng';

请告诉我们这是否是正确的数据库查询，以便我们帮助处理 AREL。

编辑:查询何时要查找至少为“eng”和“fra”的媒体

SELECT * FROM media m1
  WHERE(
    SELECT count(*) FROM audio_languages_media alm2
    JOIN languages l2 ON alm2.language_id = l2.id
    WHERE l2.code in ('eng','fra')
    AND alm2.media_id = m1.id
  ) > 1;

编辑:添加@chinshr 的查询

如果您想要只有“eng”和“fra”的媒体

SELECT * FROM media m1
  WHERE(
    SELECT count(*) FROM audio_languages_media alm2
    JOIN languages l2 ON alm2.language_id = l2.id
    WHERE l2.code IN ('eng','fra')
    AND alm2.media_id = m1.id
    AND (
      SELECT count(*) FROM audio_languages_media alm2 
      WHERE alm2.media_id = media.id
    ) = 2
  ) = 2;

可以通过在 IN 数组中添加/删除并调整末尾的计数使其等于 IN 数组中的元素数来针对更多或更少的语言调整此查询。

为了使其可靠地工作，您必须在 audio_languages_media(media_id, language_id) 上有一个唯一的索引；