Ruby on Rails ActiveRecord/Include/Associations can't get my query to work

Posted by Cypher on Stack Overflow See other posts from Stack Overflow or by Cypher
Published on 2010-04-16T06:23:18Z Indexed on 2010/04/16 8:43 UTC
Read the original article Hit count: 369

I just started learning Rails and I'm just trying to set up query via associations. All the queries I try to write seem to be doing bizzare things and end up trying to query two tables parsed together with an '_' as one table. I have no clue why this would ever happen
My tables are as follows:

schools:          id  name
variables:        id  name         type
var_entries:      id  variable_id  entry
school_entries:   id  school_id    var_entry_id

my rails association tables are

$local = {
        :adapter => "mysql",
        :host => "localhost",
        :port => "3306".to_i,
        :database => "spy_2",
        :username =>"root",
        :password => "vertrigo"
}  

class School < ActiveRecord::Base
  establish_connection  $local
  has_many :school_entries
  has_many :var_entries, :through => school_entries
end

class Variable < ActiveRecord::Base
  establish_connection  $local
  has_many :var_entries
  has_many :school_entries, :through => :var_entries
end

class VarEntry < ActiveRecord::Base
  establish_connection $local
  has_many_and_belongs_to :school_entries
  belongs_to :variables
end

class SchoolEntry < ActiveRecord::Base
  establish_connection  $local
  belongs_to :school
  has_many :var_entries
end

I want to do this sql query:

SELECT school_id, variable_id,rank
FROM school_entries, variables, var_entries, schools 
WHERE var_entries.variable_id = variables.id 
AND school_entries.var_entry_id = var_entries.id 
AND schools.id = school_entries.school_id
AND variables.type = 'number';

and put it into Rails notation: here is one of my many failed attempts

schools = VarEntry.all(:include => [:school_entries, :variables], :conditions => "variables.type = 'number'")

the error:
'const_missing': uninitialized constant VarEntry::Variables (NameError)

if i remove variables

schools = VarEntry.all(:include => [:school_entries, :variables], :conditions => "type = 'number'")

the error is:
Mysql::Error: Unkown column 'type' in 'where clause': SELECT * FROM 'var_entries' WHERE (type=number) (ActiveRecord::StatementInvalid)

Can anyone tell me where I'm going horribly wrong?

© Stack Overflow or respective owner

Related posts about ruby-on-rails

Related posts about activerecord