Order by association existence prior to created_at

Question

Let's say I have the following:

class Movie < ActiveRecord::Base
  has_many :reviews
end

I'd like to list newly created movies that a user didn't review first before the ones they reviewed.

So it's like merging these 2 queries:

@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

Anyone knows how to do this in one query?

UNION ALL still performs two queries within MySQL (i.e. searches through the data twice) and is not necessary to achieve the desired results. OUTER JOIN spins through the data only once. — gwc, Sep 16 '13 at 12:52
It should be noted that .where("reviews.user_id != ?", user.id) will return all reviews from all users who are NOT the desired user. While that is what is in your query, is that really what you want or do you want where user.id has no reviews for the movie? If the later, see my answer. — gwc, Sep 16 '13 at 12:59
you can get a count of reviews for a movie from the user using group and sum in SQL. Then order by the count ASC to get movie not reviewed first. — tihom, Sep 18 '13 at 09:52

score 3 · Answer 1 · edited May 23 '17 at 10:25

Not familiar with Ruby syntax but you can perform all in query like below let say user id is "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)

or directly join your tables with user condition

(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

score 1 · Answer 2 · answered Sep 18 '13 at 01:19

@movies   = Movie.joins(:reviews)
                 .select("movies.*, reviews.id, SUM(reviews.user_id = #{user.id}) AS counter ")
                 .group("movies.id ")
                 .order("counter ASC, movies.created_at DESC")

This would create counter which is the number of reviews a movie has from the current user.

gwc · Answer 3 · 2013-09-16T13:49:42.997

SQL would be something like:

SELECT movies.*, reviews.*
  FROM movies
  LEFT OUTER
  JOIN reviews
    ON reviews.movie_id = movies.id
   AND reviews.user_id  = :user_id
 ORDER BY IF(reviews.id IS NULL, 0, 1) ASC
         ,movies.created_at DESC

SQLFiddle at http://sqlfiddle.com/#!2/97117/1

Results look something like:

ID  NAME       CREATED_AT                       MOVIE_ID  USER_ID  REVIEW
1   movie 1    September, 16 2013 12:29:26+0000   (null)   (null)  (null)
3   movie 3    September, 16 2013 10:29:26+0000   (null)   (null)  (null)
2   movie 2    September, 16 2013 11:29:26+0000        2        1  movie 2 review

Don't know Ruby, but I think you're looking for something like this:

@movies = Movie.joins('LEFT OUTER JOIN reviews ON reviews.movie_id = movies.id AND reviews.user_id = ?', user.id)
               .order('IF(reviews.movie_id IS NULL, 0, 1) ASC, created_at DESC')

AshwinKumarS · Answer 4 · 2013-09-16T06:17:17.230

A small update :

@movies = Movie.includes(:reviews).where('reviews.movie_id = movies.id AND reviews.user_id = ?', user.id).order('reviews.movie_id ASC, created_at DESC')

using "includes" is the rails active record way of left outer join :)

Apparently it also has some side effects so eagerload can be used.(rails 3.x +)

@movies = Movie.eager_load(:reviews).where('reviews.movie_id = movies.id AND reviews.user_id = ?', user.id).order('reviews.movie_id ASC, created_at DESC')

If condition for review id is not required i guess. Makes it a touch faster for huge data ;)

score 0 · Answer 5 · answered Sep 19 '13 at 20:33

If your reviews table has a movie_id on it, this may work in rails 4. It should execute a single query with a sub select.

movies = Movie.where.not(id: Review.select("movie_id").where(user_id: user.id)).order("counter ASC, movies.created_at DESC")

The automagical subselect is new in rails 4.

score 0 · Answer 6 · answered Sep 22 '13 at 18:42

Note : I don't "speak" ruby, I'll write SQL.

First, my shot at having it in one query :

-- add a boolean column computed from the join
SELECT m.*, (r.id != null) as reviewed
FROM movies m LEFT JOIN reviews r ON r.movieID = m.id AND r.userID = @userID
ORDER BY reviewed ASC, created_at DESC

Next a remark: if your list gets big, you may want to avoid sorting on an unindexed column on each request. You can have one query to fetch the rows, and then have your ruby code sort it (you will still have to merge two list, but only one SQL query) :

-- don't sort on "reviewed"
SELECT m.*, (r.id != null) as reviewed
FROM movies m LEFT JOIN reviews r ON r.movieID = m.id AND r.userID = @userID
ORDER BY created_at DESC

Finally, another remark: you can also try to run two queries which will be more respectful of SQL's cache

-- extract movies : this query is the same for all users. Once cached : all users will get the cached version
SELECT * FROM movies ORDER BY created_at DESC

-- extract the user's reviewed movies. With the right index (userID, movieID), this will only hit the index
SELECT movieID FROM reviews WHERE reviews.userID = @userID

Then in ruby, you should build a mapping reviewed[moviedID] -> {true, false} from the second query, and split the movies list in two parts. If frequentation goes up, this version may actually be faster.

score -1 · Answer 7 · answered Sep 12 '13 at 18:52

-1

You could use an outer join - http://www.w3schools.com/sql/sql_join_left.asp

@movies_1 = Movie.joins('LEFT JOIN reviews ON reviews.movie_id = movies.id').where('reviews.movie_id IS NULL').where("reviews.user_id != ?", user.id).order("created_at")
@movies_2 = Movie.joins('LEFT JOIN reviews ON reviews.movie_id = movies.id').where(where('reviews.movie_id IS NOT NULL')).where("reviews.user_id = ?", user.id).order("created_at")

Or you could use a counter_cache http://guides.rubyonrails.org/association_basics.html and just check which movies have a reviews_count of 0.

answered Sep 12 '13 at 18:52

Edward

3,429
2
27
43

Thanks but I'm not sure if this answers my question. I was looking to combine the queries instead of running 2 separate ones – CodeOverload Sep 12 '13 at 18:55
Then use a counter cache and order by reviews count. If it's zero, then they have no reviews. – Edward Sep 13 '13 at 05:46

Order by association existence prior to created_at

7 Answers7