Mysql2::错误:字段列表中的列不明确

Question

我有 3 个表 drivers、jobs 和 tip_travel_time。而他们的联想是这样的

Class Job < ActiveRecord::Base
  belongs_to :driver, primary_key: :username
end

Class Driver < ActiveRecord::Base
  has_many :jobs, foreign_key: :driver_id, primary_key: :username
  has_many :tip_travel_times,foreign_key: :driver_id
end

class TipTravelTime < ActiveRecord::Base
  belongs_to :driver
end

现在我正在使用 activeadmin 并想将这两个查询组合在一起

TipTravelTime.joins(:driver).where('users.runsheet_type IN (?,?,?)',Driver::LIQUID, Driver::FRONTLIFT, Driver::REARLIFT).group(:driver_id).select('ceil(avg(time_difference)/60) as average_time_in_minutes, driver_id').order(:driver_id)

Job.joins(:driver).where('users.runsheet_type IN (?,?,?)',Driver::LIQUID, Driver::FRONTLIFT, Driver::REARLIFT).group(:driver_id).where('completed_at is not null and started_at is not null').select('ceil(AVG(completed_at - arrived_at)/60) as average_completion_time_in_minutes, driver_id').order(:driver_id)
enter code here

我通过以下查询组合它们:

Driver.joins(:tip_travel_times, :jobs).where('runsheet_type IN (?,?,?)',Driver::LIQUID, Driver::FRONTLIFT, Driver::REARLIFT).group(:driver_id).where('jobs.completed_at is not null and jobs.started_at is not null').select('ceil(AVG(jobs.completed_at - jobs.arrived_at)/60) as average_completion_time_in_minutes, ceil(avg(tip_travel_times.time_difference)/60) as average_time_in_minutes, jobs.driver_id').order(:driver_id)

但最后一个查询返回以下错误:

Mysql2::Error:字段列表中的“driver_id”列不明确:SELECT COUNT(*) AS count_all，driver_id AS driver_id FROM users INNER JOIN tip_travel_times ON tip_travel_times.driver_id = users.id INNER JOIN jobs ON jobs.driver_id = users.username WHERE users.type IN ('Driver') AND (runsheet_type IN ('liquid','frontlift','rearlift')) AND (jobs.completed_at 不为空且 jobs.started_at 不为空) GROUP BY driver_id LIMIT 10000 OFFSET 0

知道它哪里出错了或者我能做些什么来实现这个目标吗？

Answer 1

将列名与表名一起使用，例如tip_travel_times.driver_id

你的查询是这样的:-

SELECT COUNT(*) AS count_all, 
tip_travel_times.driver_id AS driver_id 
FROM users.....