mysql - Rails Mysql2::Error:使用 count 来获取组

Question

当我使用“near”搜索某个地方附近的维修店时，我遇到了组和计数的突然错误。我正在使用地理编码器 gem 对创建时的维修店进行地理编码。

假设我将查询称为:

Repairshop.where(:approved => true).group(:title).count

我得到了正确的哈希结果:{"my title"=> 1}一切都很好。

现在，当我调用城市或州的 group 然后依赖结果时，我收到 Mysql2 错误:

让我分步骤展示:

1. Repairshop.where(:approved => true).near('syracuse', 200)

gives the active record relation with my title repairshop and it works fine in finding with near query.

将其与 group(:city) 链接后，我得到相同的结果，如下所示:

现在，当我尝试像这样链接计数时:

Repairshop.where(:approved => true).near('syracuse', 200).group(:city).count

我收到如下错误:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((43.0481221 - repairshops.latitude) ' at line 1: SELECT DISTINCT COUNT(DISTINCT repairshops.*, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((43.0481221 - repairshops.latitude) * PI() / 180 / 2), 2) + COS(43.0481221 * PI() / 180) * COS(repairshops.latitude * PI() / 180) * POWER(SIN((-76.14742439999999 - repairshops.longitude) * PI() / 180 / 2), 2))) AS distance, MOD(CAST((ATAN2( ((repairshops.longitude - -76.14742439999999) / 57.2957795), ((repairshops.latitude - 43.0481221) / 57.2957795)) * 57.2957795) + 360 AS decimal), 360) AS bearing) AS count_repairshops_all_3958_755864232_all_2_all_asin_sqrt_power_sin_43_0481221_repairshops_latitude_all_pi_180_2_2_cos_43_0481221_all_pi_180_all_cos_repairshops_latitude_all_pi_180_all_power_sin_76_14742439999999_repairshops_longitude_all_pi_180_2_2_as_dis, city AS city FROM repairshops WHERE repairshops.approved = 1 AND (repairshops.latitude BETWEEN 40.153486437783044 AND 45.94275776221696 AND repairshops.longitude BETWEEN -80.10844293387429 AND -72.1864058661257 AND (3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((43.0481221 - repairshops.latitude) * PI() / 180 / 2), 2) + COS(43.0481221 * PI() / 180) * COS(repairshops.latitude * PI() / 180) * POWER(SIN((-76.14742439999999 - repairshops.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 200) GROUP BY repairshops.city ORDER BY distance ASC

Repairshop 模型类如下所示，我已经安装了地理编码器 gem:

class Repairshop < ActiveRecord::Base
    geocoded_by :full_address
    after_validation :geocode


end

在 SQLite 中一切都工作得很好，但在这里它在链接时给出了错误。我没有直接使用 MySQL 语法，所以不明白问题出在哪里。

注意:我正确设置了 MySQL2，并使用本地服务器进行开发。 group(:field).count 适用于与位置无关的其他字段

非常感谢您的帮助。

Answer 1

Geocoder gem 似乎正在以与#group 不兼容的方式重写 select 子句。使用子选择可能是一个解决方案:

Repairshop.where(
  id: Repairshop.where(
    :approved => true
  ).near(
    'syracuse', 200
  ).select(:id)
).group(
  :city
).count