Rails 4.2.5.1应用迁移Heroku PG后GROUP BY城市统计异常问题

Hey there! I totally get the frustration of database-specific quirks breaking your code when deploying to Heroku. Let's break down why this is happening and how to fix it so you get the city-name-to-count hash you need.

Why the Difference Between MySQL and PostgreSQL? #

MySQL has a more relaxed approach to GROUP BY—it lets you omit non-aggregated columns from the clause, even if you're selecting them. It just picks an arbitrary value for those columns behind the scenes.

PostgreSQL, though, strictly follows SQL standards: any non-aggregated column in your SELECT must be included in the GROUP BY clause. When you added created_at to the GROUP BY to fix the error, you told PostgreSQL to group results by both city and created_at, which is why your hash ended up with [city_name, timestamp] keys instead of just city names.

The Fix: Focus Your Scope on City Only #

Your goal is to count records per city, so you don't need to include created_at in either the SELECT or GROUP BY—unless you're filtering by date (which is a WHERE clause job, not GROUP BY).

Example of the Problematic Scope

If your original scope looked something like this (which worked in MySQL but broke in PG):

scope :city_facets, -> { group(:city).count }

PostgreSQL throws an error because Rails might be implicitly selecting all columns (including created_at) when you call group, but only grouping by city.

Corrected Scope

Adjust your scope to explicitly select only the city column before grouping and counting:

scope :city_facets, -> { select(:city).group(:city).count }

This tells PostgreSQL exactly what you're grouping by and what you want to return—no extra columns, so no need to include created_at in GROUP BY.

If You Need to Filter by Created At

If you're using created_at to filter results (e.g., only count records from the last 30 days), keep that in a WHERE clause, not GROUP BY:

scope :city_facets, ->(start_date) {
  where("created_at >= ?", start_date)
    .select(:city)
    .group(:city)
    .count
}

This way, created_at is just used to narrow down the records you're counting, not to group them.

Verify the Result #

After making this change, calling Model.city_facets should return a hash like:

{"New York"=>15, "London"=>8, "Tokyo"=>22}

Which is exactly the city-to-count data you need.

Pro Tip for Future Development #

To avoid these database-specific surprises, try using PostgreSQL in your local development environment too. It'll help catch issues like this before you deploy to Heroku!

内容的提问来源于stack exchange，提问作者Saurav Prakash