RoR: How to Change fields on Join statement generated by Active Record's sum function

I'm doing a sum using the Sum function provided by RubyOnRails' Active Record as follows:

s=DatosMateria.sum('inscritos',:conditions=> "datos_materia.ano=2005 AND materias.codigo=2394",:include=>"materias")

it returns 0 and genera开发者_运维问答tes me the following SQL statement:

SELECT sum('datos_materia'.inscritos) AS  sum_inscritos FROM 'datos_materia' LEFT OUTER JOIN 'materias' ON materias.codigo=datos_materia.id WHERE..

I need it to join on fields materias.codigo=datos_materia.materia_codigo instead of materias.codigo=datos_materia.id (materias.codigo and datos_materia.id are both primary keys and they're not the same type nor associated keys.)

The involved models are shown below:

 
class DatosMateria < ActiveRecord::Base
    set_table_name 'datos_materia'
    has_many :materias,:foreign_key => 'codigo'
end


class Materia < ActiveRecord::Base
    set_primary_key 'codigo'
    belongs_to :datosMateria, :foreign_key=> 'materia_codigo'
end

Edit: After reworking this post I've come to the conclusion that your relationship between Materia and DatosMateria are wrong. It looks like you've got belongs_to and has_many backward. You mention that materias.codigo and datos_materia.id are both primary keys. That you want to join on the materia primary key implies that materia should have many datos_materia, and not the other way around as your assocations are defined.

In short: the description of your associations and problem, it looks like were trying to save the primary key of the one side of a one one to many relation ship as a foreign key on the many side. This is not how relational databases work. The way they usually go is the many side of a one to many relationship will store the primary key of the associated record as a foreign key.

It looks like a few things have gotten lost in translation. So here's a better explanation of what's wrong with your relationships.

Rails expects foreign keys to be named "#{foreign_class}_id", and will store the id associated foreign class. The foreign key is always found on the belongs_to side of things.

With the relationships defined in the question.

All association helper methods called on DatosMateria or an instance of it will join on datos_materia.id = materias.codigo.

Where the foreig\n_key is expected to be materias.codigo. But materias.codigo the primary key, so how can a materia be linked to a datos_materia?

@datos_materia.materias.create

Will create a new Materia record with @datos_materia.id stored in the Materia's codigo's column. Where as establishing the relationship the other way around will create the new materia with @datos_materia.materia_codigo stored in the materia's codigo column.

Materia.create(:datos_materia => @datos_materia)

Assocation helper methods called on Materia or an instance of it will join on datos_materia.id = materias.materia_codgio

There is no simple fix. You will need to redefine your models and completely rework your tables. Assuming you were looking to set up that one materia has many datos_materia. Here are the correctly defined relationships you were going for:

class DatosMateria < ActiveRecord::Base
  set_table_name 'datos_materia'
  belongs_to :materias, :foreign_key => :materia_codigo
end

class Materia < ActiveRecord::Base
  has_many :datos_materias, :foreign_key => :materia_codigo
end

However one you've defined your relationship properly your Sum will work as it should.