以下查询适用于本地但不适用于生产:(生产是heroku正在运行postgreSQL,本地我正在运行sqllite数据库) ruby Tutor.joins(:expertises).where(:expertises = {:subject_id = [2,4]}).group("tutors.id").having("COUNT(*) = 2"
ruby
Tutor.joins(:expertises).where(:expertises => {:subject_id => [2,4]}).group("tutors.id").having("COUNT(*) = 2")
SQL
SELECT "tutors".* FROM "tutors" INNER JOIN "expertises" ON "expertises"."tutor_id" = "tutors"."id" WHERE ("expertises"."subject_id" IN (9)) GROUP BY tutors.id HAVING COUNT(*) = 1 ORDER BY rank DESC)
我在生产ActiveRecord :: StatementInvalid时遇到以下错误(PGError:错误:列“tutors.fname”必须出现在GROUP BY子句中或用于聚合函数
我的表中有以下值
id :integer not null, primary key fname :string(255) lname :string(255) school :string(255) major :string(255) year :string(255) bio :text vid :string(255) price :integer phone :string(255) skype :string(255) address :text
当我尝试将查询调整为按所有属性分组时,我收到另一个错误:
ruby
>> Tutor.joins(:expertises).where(:expertises => {:subject_id => [2,4]}).group("tutors.*").having("COUNT(*) = 2")
SQL
SELECT "tutors".* FROM "tutors" INNER JOIN "expertises" ON "expertises"."tutor_id" = "tutors"."id" WHERE ("expertises"."subject_id" IN (2, 4)) GROUP BY tutors.* HAVING COUNT(*) = 2 ORDER BY rank DESC
ActiveRecord :: StatementInvalid:PGError:ERROR:无法识别类型教师的排序运算符HINT:使用显式排序运算符或修改查询.
救命!
嗯,很好的尝试,但星号扩展在GROUP BY子句中不起作用.您需要实际列出所有列.或者在几个月内升级到PostgreSQL 9.1.