3

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!

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you. It works perfectly. You made a typo in the where clause. But I am able to catch it.
Thanks. Let me know which typo so I can update the answer.
('rates.rater_id' => @user.id).where("rates.rateable_id = locations.id AND rates.rater_id = locations.user_id") <<< user_id
Updated: @locations_user_rated = Location.joins('INNER JOIN rates').where('rates.rater_id' => @user).where("rates.rateable_id = locations.id") <<< this is the correct answer. Please use this
I ran into a problem, read this. http://stackoverflow.com/questions/36962306/sqlite3-and-postgres-heroku-ruby-on-rails-query-issues It is not a good way to use where to state the ON clause. Should use ON instead. Working answer for PG: @locations_user_rated = Location.joins('INNER JOIN rates ON rates.rateable_id = locations.id').where('rates.rater_id' => @user)
|
-1

I highly recommend taking advantage of ActiveRecord's has_many, belongs_to, and has_many through: functionality.

If you set up a model for each of these tables, with the correct relationships:

class User < ActiveRecord::Base
  has_many :ratings, foreign_key: :rater_id
  has_many :rated_locations, through: ratings, class_name: Location.name, source: :rater
end

class Rating < ActiveRecord::Base
  belongs_to :rater, class_name: User.name
  belongs_to :location
end

class Location < ActiveRecord::Base
  has_many :ratings
end

Then to access the locaitons that a user has rated, you just call

user.rated_locations
Improve this answer

3 Comments

I will look into this method. I had those defined in models, but I am not familiar how to use. Thank you again!
@MelvinCh'ng I highly recommend using this method in your codebase. It is one of the most powerful things that Ruby on Rails provides developers. The app's code will end up being considerably more legible and and controllable. This guide should help you learn everything you need to know: guides.rubyonrails.org/association_basics.html
I can't use this method as I am using ratyrate gem. It will conflict with the model. I tried, but it gave me more issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.