Ruby on Rails+Postgres场景下查询无关联电影的分类数据方法求助

正确实现方案 #

方案1：ActiveRecord 原生语法（推荐） #

Rails 5及以上版本直接使用left_joins即可：

# 统计无关联电影的分类数量
genres_with_no_movie_count = Genre.left_joins(:movies).where(movies: { id: nil }).count

# 如果要获取这些分类的完整记录，去掉count即可
genres_with_no_movie = Genre.left_joins(:movies).where(movies: { id: nil })

如果是Rails 4及更早版本，替换为includes方法即可：

genres_with_no_movie_count = Genre.includes(:movies).where(movies: { id: nil }).count

还有一种性能更优的NOT EXISTS写法，适合大数据量表查询：

genres_with_no_movie_count = Genre.where.not(id: Movie.select(:genre_id)).count

方案2：修正后的原生SQL #

你之前的SQL存在两处错误：一是表名字段名拼写错误，二是WHERE条件错误将左连接转为了内连接，修正后写法如下：

sql = <<~SQL
  SELECT count(*) FROM genres
  LEFT JOIN movies ON genres.id = movies.genre_id
  WHERE movies.id IS NULL
SQL
genres_with_no_movie_count = ActiveRecord::Base.connection.exec_query(sql).rows[0][0]

原代码问题说明 #

• 原生SQL中错误拼写了genre.id（应为genres.id）、movies.genre（应为movies.genre_id），且WHERE条件筛选的是匹配成功的记录，自然只返回有关联的分类数量
• Genre.joins(:movies)默认生成INNER JOIN语句，只会保留两边表匹配成功的记录，因此统计的也是有关联电影的分类数量

内容的提问来源于stack exchange，提问作者sephiroth