Mysql2::Error: Column in field list is ambiguous

Question

I have 3 tables drivers, jobs and tip_travel_time. And their associations are like this

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

Now I am using activeadmin and want to combine these two queries together

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

And I am combining them by following Query:

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)

But last query is returning me following error:

Mysql2::Error: Column 'driver_id' in field list is ambiguous: 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 is not null and jobs.started_at is not null) GROUP BY driver_id LIMIT 10000 OFFSET 0

Any idea where its going wrong or anything else I can do to achieve this?

You have `Job` and `TipTravelTime` which both have `driver_id` columns hence the ambiguousness — j-dexx, Dec 04 '15 at 11:15
@j-dexx how can we avoid that and use table names in rails activerecord methods? — Adnan Ali, Dec 04 '15 at 11:19
Looks like it might just be the order at the end, rails will default to order by the id of the driver because you're querying the driver so you might just be able to remove the order clause — j-dexx, Dec 04 '15 at 11:34

score 1 · Answer 1 · answered Dec 04 '15 at 11:15

1

use column name with table name like tip_travel_times.driver_id

your query like that :-

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

answered Dec 04 '15 at 11:15

Abhishek Sharma

6,689
1
14
20

Any rails method to achieve similar query without using SQL? – Adnan Ali Dec 04 '15 at 11:24
try this link http://stackoverflow.com/questions/4912040/ambiguous-column-in-mysql-rails-find-method @AdnanAli – Abhishek Sharma Dec 04 '15 at 11:27

Nitin Srivastava · Answer 2 · 2015-12-04T11:48:05.010

If two table have similar columns, then there will be an ambiguity problem in table joins.

Your query will be

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

Job.joins(:driver).where('users.runsheet_type IN (?,?,?)',Driver::LIQUID, Driver::FRONTLIFT, Driver::REARLIFT).group("jobs.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, jobs.driver_id').order("jobs.driver_id asc")

If you want group by driver_id contained in jobs table then

Driver.joins(:tip_travel_times, :jobs).where('runsheet_type IN (?,?,?)',Driver::LIQUID, Driver::FRONTLIFT, Driver::REARLIFT).group("jobs.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("jobs.driver_id asc")

If you want group by driver_id contained in tip_travel_times table then

Driver.joins(:tip_travel_times, :jobs).where('runsheet_type IN (?,?,?)',Driver::LIQUID, Driver::FRONTLIFT, Driver::REARLIFT).group("tip_travel_times.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, tip_travel_times.driver_id').order("tip_travel_times.driver_id asc")

instead of

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)

Enjoy...

these two queries run fine problem is with third one which is combination of the two — Adnan Ali, Dec 04 '15 at 11:40

score 0 · Answer 3 · answered Dec 04 '15 at 11:33

Try below, Let me know if this help you out? change :drive_id with 'table_name.drive_id'

Driver.joins(:tip_travel_times, :jobs).where('runsheet_type IN (?,?,?)',Driver::LIQUID, Driver::FRONTLIFT, Driver::REARLIFT).group('table_name.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('table_name.driver_id')

Mysql2::Error: Column in field list is ambiguous

3 Answers3