Question

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 generates 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

Answer 1

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 _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.

友情链接