I am currently making a website that runs on Ruby on Rails. I am facing some issues while I was trying to join two tables, Rates and Locations, that I have with two different attributes name.
id rater_id rateable_id
id title body user_id
FROM rates, locations
WHERE rates.rater_id = locations.user_id AND rates.rateable_id = locations.id
@join_rating = Rate.joins(:locations).where("rates.rateable_id = locations.id AND rates.rater_id = locations.id")
@all_rating = @all_rating.where(rater_id: @user)
@count_all_rating = @all_rating.count
A few points:
When in ActiveRecord you're starting a statement with the
Rate class, it means the result is going to be a collection of
Rate objects. So if you're trying to show locations, you should start with a
@locations_user_rated = Location.joins('INNER JOIN rates').where('rates.rater_id' => @user.id).where("rates.rateable_id = locations.id AND rates.rater_id = locations.id")
By the way, note that there is difference between
Location.joins('INNER JOIN rates') and
Location.joins(:rates). When you provide a string, Active Record will use it as-is. When you provide a symbol, ActiveRecord will look for the association called
has_many :rates or
has_and_belongs_to_many :rates and use the foreign key definition of the association to know how to perform the where clause appropriately. This is very convenient typically, but note that you are deviating from the Rails default conventions, so to make it work you'll need to set the associations correctly.
You can find more about how to customize associations here, and BenanaNeil's answer below is nicely pursuing this direction.