4

I'm getting this error

Mysql::Error: Column 'id' in field list is ambiguous

when using a find method like such: self.prompts.find(:all, :select => 'id')

The models are being called using a has_many :through association, so MySQL complains that there are multiple 'id' columns, since all 3 tables being used have an 'id' column.

I looked this up and understand what is going wrong on the SQL end, but don't know how to resolve it in the ActiveRecord find method, and I'm not confident in my SQL abilities to try rolling my own SQL query. Is there a way to massage the find method into something that'll play well?

edit

Here is the relevant Actor model code:

class Actor < ActiveRecord::Base
  has_many :acts, :dependent => :destroy
  has_many :decisions, :through => :acts, :order => 'created_at'
  has_many :prompts, :through => :decisions, :order => 'id'
John
  • 477
  • 5
  • 13

1 Answers1

7

You need to be more explicit about which id you want to select. For example:

self.prompts.find(:all, :select => 'prompts.id') #prompts is the table name
Pan Thomakos
  • 34,082
  • 9
  • 88
  • 85
  • 1
    Hmm, okay, that got rid of the 'ambiguous' error, but now it's complaining about `Unknown column 'decisions.actor_id' in 'where clause'` Probably because there are two nested has_many :through associations that it's acting on. – John Feb 06 '11 at 06:51
  • I'm guessing the rest of your setup is not right. Read the documentation on has_many and foreign and primary keys. It should help you with your configuration. If your table names and column names don't follow the Rails conventions then things can get difficult but not impossible. – Pan Thomakos Feb 06 '11 at 07:02
  • Took care of it using some advice posted here: [http://railsforum.com/viewtopic.php?id=10890](http://railsforum.com/viewtopic.php?id=10890) Seems to be working ok now. Thanks much. – John Feb 06 '11 at 07:18
  • 1
    Link no longer works. Would love to know how you sorted out getting either "ambiguous" or "unknown column". – JosephK Aug 11 '15 at 10:29