问题描述
我正在尝试通过联接数据库中的两个表进行查询.该查询完全可以在localhost(由SQLite3驱动的数据库)中运行，但是当将其推送到heroku服务器(带有postgres)时，它将不再起作用.我尝试了几种方法，但都可以，但是所有方法都可以在本地使用.
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.
在我的控制器中，我有
@user = User.find(params[:id])
我正在尝试通过使用不同的方法来查询以下语句并返回相同的结果.以下是我尝试过的不同方法.它们全都可以与SQLite3完美配合，但不能与Postgres在Heroku上配合使用.
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)(假设当前用户ID = 2)
@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")
我发现@user是导致问题的原因.因此，我将其替换为User.find(params[:id]).但是，服务器拒绝接收它.我在Rails网站上读到，只要它可以在任何SQL中运行，它就可以在Heroku(Postgres)上运行.但这不是事实.
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.
下面是我从Heroku服务器收到的日志.
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...
. .^(^显示导致问题的where子句)
. .^ (^ shows the where clause that is causing the problem)
Postgres和SQLite的WHERE子句在语法上有什么区别?
What is the syntax difference between Postgres and SQLite for the WHERE clause?
更新:我发现它需要on子句.如何在此处添加on子句?
UPDATE: I found out that it requires on clause. How do I add an on clause here?
推荐答案
找到答案这里.我发现它需要ON子句，并且不要使用WHERE语句来指定它.改为使用ON子句.
Answer found here. I found out that it requires ON clause and DO NOT use WHERE statement to specify it. Use ON clause instead.
这篇关于SQLite3和Postgres/Heroku Ruby on Rails查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！