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

Question

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 1

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 ON rates.rateable_id = locations.id').where('rates.rater_id' => @user)

And if your ActiveRecord associations are well defined, you could simply do:

@locations_user_rated = Location.joins(:rates).where('rates.rater_id' => @user)

"Well defined" simply means you'll need to do something like the following. Note that I am not sure I understand your model relationships correctly. I assume below that every location has multiple rates, and that the reason your Rate model has the field called rateable_id instead of a location_id is because you want :rateable to be polymorphic. This means you probably also have a rateable_type field in rates table.

class Location < ActiveRecord::Base
  has_many :rates, as: :rateable
end

class Rate < ActiveRecord::Base
  belongs_to :rateable, polymorphic: true
end

If this polymorphism is not the case, things should actually be simpler, and I highly recommend that you follow Rails's conventions and simply name the relationship field location_id on your Rate model instead of rateable_id. Then you can do:

class Location < ActiveRecord::Base
  has_many :rates
end

class Rate < ActiveRecord::Base
  belongs_to :location
end

If still you are not convinced about the field name, you can customize things and do:

class Location < ActiveRecord::Base
  has_many :rates, foreign_key: :rateable_id
end

class Rate < ActiveRecord::Base
  belongs_to :location, foreign_key: :rateable_id
end

You can find more about how to customize associations here, and here.