Ambiguous column in MySQL / Rails search method

Question

I get this error

Mysql :: Error: column "id" in the list of fields is ambiguous

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

Models are called using has_many: through an association, so MySQL complains that there are several id columns, since all three tables used have an id column.

I examined this and realized that it was wrong on the SQL side, but I don’t know how to resolve it in the find ActiveRecord method, and I'm not sure of my SQL abilities to try to overturn my own SQL query. Is there a way to massage the search method into something what will play well?

change

Here is the corresponding code for the Actor model:

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

Answer 1

You need to be more explicit which identifier you want to select. For instance:

  self.prompts.find (: all,: select => 'prompts.id') #prompts is the table name