我在这里找到一些主题的SO，但我仍然找不到正确的设置为我的查询。
这是查询，这对我很好地localhost：
@cars = Car.find_by_sql('SELECT cars.*,COUNT(cars.id) AS counter FROM cars LEFT JOIN users ON cars.id=users.car_id GROUP BY cars.id ORDER BY counter DESC')
但在Heroku上给我上面的错误 – GROUP BY子句或在聚合函数中使用。
然后我在某处读，我应该指定表中的所有列，所以我试过这样：
@cars = Car.find_by_sql('SELECT cars.id,cars.name,cars.created_at,cars.updated_at,COUNT(cars.id) AS counter FROM cars LEFT JOIN users ON cars.id=users.car_id GROUP BY (cars.id,cars.updated_at) ORDER BY counter DESC')
但这不工作在localhost也不在Heroku …
什么应该是正确的查询配置？
我想你正在尝试聚合和分组在同一列。这取决于你想要什么数据。以这样做：
SELECT cars.name,COUNT(cars.id) AS counter FROM cars LEFT JOIN users ON cars.id=users.car_id GROUP BY cars.name,cars.updated_at ORDER BY counter DESC
或者你想计数所有也许？然后像这样：
SELECT cars.id,COUNT(*) AS counter FROM cars LEFT JOIN users ON cars.id=users.car_id GROUP BY cars.id,cars.updated_at ORDER BY counter DESC
