mysql - 按存在于 created_at 之前的关联排序

Question

假设我有以下内容:

class Movie < ActiveRecord::Base
  has_many :reviews
end

我想列出用户在评论之前没有先评论的新创建的电影。

所以这就像合并这两个查询:

@reviewed_movies   = Movie.joins(:reviews)
                          .where("reviews.user_id != ?", user.id)
                          .order("created_at DESC")

@unreviewed_movies = Movie.joins(:reviews)
                          .where("reviews.user_id = ?", user.id)
                          .order("created_at DESC")

@movies   = @reviewed_movies.all + @unreviewed_movies.all

有人知道如何在一个查询中执行此操作吗？

Answer 1

不熟悉 Ruby 语法，但您可以执行所有查询，如下所示，假设用户 ID 为“3”

(SELECT * FROM `movies` m  LEFT JOIN  reviews r ON (m.id =r.movie_id)
WHERE user_id =3 ORDER BY created_at DESC)
UNION ALL
(SELECT * FROM `movies` 
LEFT JOIN  reviews r ON (m.id =r.movie_id)
WHERE user_id !=3 ORDER BY created_at DESC)

或者直接根据用户条件加入你的表

(SELECT * FROM `movies` m  
LEFT JOIN  reviews r ON (m.id =r.movie_id AND user_id =3)
ORDER BY created_at DESC)
UNION ALL
(SELECT * FROM `movies` 
LEFT JOIN  reviews r ON (m.id =r.movie_id AND user_id !=3 )
ORDER BY created_at DESC)

Mysql Union

Using union and order by clause in mysql

Rails 3 ActiveRecord: UNION

Union of 2 active record relation object in rails 3