I am trying to query by joining two tables from my database. The query works prefectcly in localhost (database powered by SQLite3), but when it push it to heroku server (with postgres), it does not work anymore. I tried a few methods and it works, but all of them works on local.
In my controller, I have
@user = User.find(params[:id])
I am trying to query the following statement by using different methods and return the same result. Below are the different methods I have tried. All of them works prefectly with SQLite3, but not on Heroku with Postgres.
@locations_user_rated = Location.joins('INNER JOIN rates').where("rates.rateable_id = locations.id AND rates.rater_id =?" , 2) (Assume current user ID = 2)
@locations_user_rated = Location.joins('INNER JOIN rates').where("rates.rateable_id = locations.id AND rates.rater_id =?" , User.find(params[:id]))
@locations_user_rated = Location.joins('INNER JOIN rates').where("rates.rateable_id = locations.id AND rates.rater_id =?" , @user)
@locations_user_rated = Location.joins('INNER JOIN rates').where('rates.rater_id' => @user).where("rates.rateable_id = locations.id")
I found out that the @user is the one that causing the issue. So I replaced it with User.find(params[:id]). However, the server refused to take it.
I read on the rails website that as long as it works in any of the SQL, it should work on Heroku (Postgres). But this is not the case here.
Below is the logs that I received from Heroku server.
2016-05-01T01:52:41.674598+00:00 app[web.1]: (1.3ms) SELECT COUNT(*) FROM "locations" INNER JOIN rates WHERE (rates.rateable_id = locations.id AND rates.rater_id =2) 2016-05-01T01:52:41.674760+00:00 app[web.1]: Completed 500 Internal Server Error in 10ms (ActiveRecord: 5.8ms)
2016-05-01T01:52:41.675453+00:00 app[web.1]: ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR: syntax error at or near "WHERE"
2016-05-01T01:52:41.675454+00:00 app[web.1]: LINE 1: SELECT COUNT(*) FROM "locations" INNER JOIN rates WHERE (rat...
. .^ (^ shows the where clause that is causing the problem)
What is the syntax difference between Postgres and SQLite for the WHERE clause?
UPDATE: I found out that it requires on clause. How do I add an on clause here?
