Rails 4 respond_with include association with WHERE

Question

I'm using rails respond_with to send a JSON response to the client, and I'm trying to figure out how to use the includes option in respond_with along with a where clause in my association

Here are my models:

class User < ActiveRecord::Base
   has_many :ratings
   has_many :movies, through: :ratings
end

class Rating < ActiveRecord::Base
   belongs_to :user
   belongs_to :movie
end

class Movie < ActiveRecord::Base
   has_many :ratings
   has_many :users, through: :ratings
end

In my controller action, I have:

def create
   movies = Movie.order("RANDOM()").limit(3)
   respond_with(movies, include: :ratings)
   // I'd like to do something like 
   // respond_with(movies, include: :ratings WHERE user: current_user)

end

However, this is responding with ALL the ratings for those three movies. I want to restrict it the ratings of just that particular user

You could add a where statement on the movies variable - something like `movies = Movie.where(user_id: @user.id).order...` — abbott567, May 29 '15 at 14:28
@abbott567, that won't work. There is no `user_id` column on the `Movie` model in the asker's schema. That query will throw an exception. — Jacob Brown, May 29 '15 at 14:31

Jacob Brown · Accepted Answer · 2015-05-29T15:54:35.023

0

You could do this:

def create
  movies = Movie.order("RANDOM()").limit(3)
  # EDIT
  # movies = movies.merge(current_user.movies).includes(:ratings)
  movies = movies.joins(:ratings).merge(current_user.ratings).includes(:ratings)
  respond_with(movies)
end

Although that doesn't make much sense in a create action.

NOTE

The movies query above will generate the following SQL (2 commands; note that some of your fields will differ since I'm using bare versions of your models):

SELECT DISTINCT "movies"."id" FROM "movies" 
INNER JOIN "ratings" ON "ratings"."movie_id" = "movies"."id"
WHERE "ratings"."user_id" = ?  ORDER BY RANDOM() LIMIT 3

SELECT "movies"."id" AS t0_r0, "movies"."name" AS t0_r1, "movies"."created_at" AS t0_r2, 
"movies"."updated_at" AS t0_r3, "ratings"."id" AS t1_r0, "ratings"."user_id" AS t1_r1, 
"ratings"."movie_id" AS t1_r2, "ratings"."created_at" AS t1_r3, "ratings"."updated_at" AS t1_r4 
FROM "movies" INNER JOIN "ratings" ON "ratings"."movie_id" = "movies"."id" 
WHERE "ratings"."user_id" = ? AND "movies"."id" IN (1) ORDER BY RANDOM() [["user_id", 1]]

edited May 29 '15 at 15:54

answered May 29 '15 at 14:26

Jacob Brown

7,221
4
30
50

That doesn't work - I'm still getting all the ratings for a particular movie, but I only want that user's rating for that particular movie – user2635088 May 29 '15 at 15:20
I'm really confused - although this should work, I'm only getting the fields of the Movies table and not the joined Ratings table – user2635088 May 29 '15 at 15:44
@user2635088, have you tried this query in the console, and confirmed that it isn't returning the fields? You might need to modify the `respond_with` args; I'm not really familiar with `Responder` and how it generates a JSON response. I just checked and the query is working appropriately for a small test; I'll post the generated SQL above. – Jacob Brown May 29 '15 at 15:51
Yes this is what it the query looks like in the console: `SELECT "movies"."id" AS t0_r0, "movies"."name" AS t0_r1, "movies"."image" AS t0_r2, "ratings"."id" AS t1_r0, "ratings"."user_id" AS t1_r1, "ratings"."design_id" AS t1_r2, "ratings"."value" AS t1_r3, "ratings"."created_at" AS t1_r4, "ratings"."updated_at" AS t1_r5 FROM "movies" INNER JOIN "ratings" ON "ratings"."design_id" = "movies"."id" WHERE "movies"."id" = $1 AND "ratings"."user_id" = $2 [["id", 4], ["user_id", 1]]` And i've confirmed that a rating exists for user_id = 1 and movie_id = 4 – user2635088 May 29 '15 at 15:52
When I do `movies.as_json(include: :ratings)`, it looks like it works fine. Maybe you just need to do `respond_with(movies, include: :ratings)` as you had at first? (Though like I said I don't know much about `respond_with`.) – Jacob Brown May 29 '15 at 15:57
If you create a second user who has rated the same movie, the then you run `respond_with(movies, include: :ratings)`, it gives the ratings of the second user as well. I just want the ratings of the first user – user2635088 May 29 '15 at 16:00
I wasn't able to replicate that, and it looks like your SQL wouldn't do that. Just for testing, could you try a) ensuring that you have copied the query in my `create` method exactly and b) replacing the `respond_with` command with `render json: movies.as_json(include: :ratings)`? – Jacob Brown May 29 '15 at 16:10

Rails 4 respond_with include association with WHERE

1 Answers1