mysql - Activerecord 查询接口(interface)。计数、连接和分组

Question

我试图将所有电影按喜欢的数量排序(即使它们还没有喜欢)。我有以下有效的 sql 语句:

      SELECT *, COUNT(case when movie_votes.like = 1 then 1 end) AS count_likes 
      FROM movies 
      LEFT OUTER JOIN movie_votes  
      ON movie_votes.movie_id = movies.id 
      GROUP BY movie_id 
      ORDER BY count_likes;

我想在我的 Controller 中实现它。到目前为止我已经:

      Movie.select("*, COUNT(case when 'movie_votes'.'like'=1 then 1 end) AS         count_likes").joins("LEFT OUTER JOIN 'movie_votes' ON 'movies'.'id'='movie_votes'.'movie_id'").group("'movie_id'")

从控制台输出:

     SELECT *, COUNT(case when 'movie_votes'.'like'=1 then 1 end AS count_likes FROM `movies` LEFT OUTER JOIN 'movie_votes' ON 'movie'.'id'='movie_votes'.'movie_id' GROUP BY 'movie_id'

并且 Mysql2::Error:您的 SQL 语法有错误；我在语法上做错了什么吗？

Answer 1

对于那些可能觉得这有用的人:

    scope :order_by_likes_or_hates, lambda {|type| 
        select("*, COUNT(case when movie_votes.like=#{type} then 1 end) AS count_likes").joins("LEFT OUTER JOIN movie_votes ON movies.id=movie_votes.movie_id").group("movie_votes.movie_id").order("count_likes DESC")}

如果有人可以提供更清晰的版本，请提供。