Melvin Ch'ng Melvin Ch'ng - 7 months ago 23
SQL Question

How to manually join two different table with different attribute name in Ruby on Rails controller

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.

Rates:

id rater_id rateable_id
(and a few more attributes in this table)

Locations:
id title body user_id
(and a few more attributes in this table)

Here is the query that I am trying to do in SQL.

SELECT *
FROM rates, locations
WHERE rates.rater_id = locations.user_id AND rates.rateable_id = locations.id


I have read the official active record documents that provided by rubyonrails.org. I have tried doing these, but it does not work. Here is the code that I am trying to implant in
app\controllers\users_controller.rb


@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


@join_rating
, is trying to join the attributes with different names.

@all_rating
, is trying to filter which location to show using the user ID

@join_rating
, is trying to calculate the total numbers of locations that are rated by the user

Assume that everything is setup correctly and the only error is in the query that I am trying to do, how should I rewrite the statement so that I am able to show the locations that the user has rated using
@all_rating
.

Thank you!

Answer

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 Location class.

@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.