CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Deekor
Deekor

Reputation: 9499

Query where not in join table

I have an Image model and a User model. I join them together with a HABTM relationship called likes. Also an Image belongs to an User

I am trying to find all images that dont belong to an user and havent been liked by that user. I can't quite seem to come up with the where conditions.

This is what I have:

@image = Image.where("user_id != :user_id AND image_id NOT IN :likes", {:user_id => session[:user].id, :likes => session[:user].likes}).first

Here are my Image and User models:

class Image < ActiveRecord::Base
# attr_accessible :title, :body
 belongs_to :user
 has_and_belongs_to_many :likes, :class_name => 'User'
end

class User < ActiveRecord::Base
 # attr_accessible :title, :body
 has_many :submitted_images, :class_name => 'Image'
 has_and_belongs_to_many :likes, :class_name => 'Image'
end

Can someone help my come up with this query? Thanks!

Edit

The Syntax error I am getting:

PG::Error: ERROR:  syntax error at or near "3"
LINE 1: ... FROM "images"  WHERE (user_id != 1 AND image_id NOT IN 3,5) LIMIT...
                                                         ^
: SELECT  "images".* FROM "images"  WHERE (user_id != 1 AND image_id NOT IN 3,5) LIMIT 1

If you look at my images_users AKA likes table you can see where the 3,5 is coming from in my error:

My Table

Upvotes: 0

Views: 965

Answers (1)

Manoj Monga
Manoj Monga

Reputation: 3083

First thing, you should use some meaningful name your associations. For example you could use has_and_belongs_to_many :appreciators, :class_name => 'User' or something similar, that makes more sense. Though your query is not gonna be affected by this. :)

Anyways, What I can suggest for your current setup is:

@image = Image.where("user_id != :user_id AND id NOT IN (:likes)", {:user_id => session[:user].id, :likes => session[:user].likes}).first

Upvotes: 1

Related Questions