Ruby-on-rails – SQL query with groups can run on SQLITE, not running on PostgreSQL

Tutor.joins(:expertises).where(:expertises => {:subject_id => [2,4]}).group("tutors.id").having( "COUNT(*) = 2")

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)

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

>> Tutor.joins(:expertises).where(:expertises => {:subject_id => [2,4]}).group("tutors.*").having ("COUNT(*) = 2")

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

The following query is for local but not for production: (production is heroku is running postgreSQL, I am running a sqllite database locally)

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)

I encountered the following error (PGError: Error: The column “tutors.fname” must appear in the GROUP BY clause when producing ActiveRecord :: StatementInvalid Or used in aggregate functions

My table has the following values

id :integer not null, primary key
fname :string( 255)
lname :string(255)
school :string(255)
major :string(255)
year :string(255)
bio :text< br />vid :string(255)
price :integer
phone :string(255)
skype :string(255)
address :text

When I try to When the query was adjusted to group by all attributes, I received another error:

ruby

>> Tutor.joins(:expertises).where( :expertises => {:subject_id => [2,4]}).group("tutors.*").having("COUNT(*) = 2")

SQL

< p>

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: Unrecognized type teacher’s sorting operator HINT: Use explicit Sort operator or modify the query.

Help!

Well, a good attempt, but the asterisk expansion does not work in the GROUP BY clause. You need to actually list all the columns. Or in a few Upgrade to PostgreSQL 9.1 within a month.