Hi everybody,
I'm getting a problem with the many-to-many through relationship. I made the
"normal" version and the "through" version, and I thought that I would get
the same result, and thats not happening.
Array.new Disciplina.all(Disciplina.professor_disciplinas.professor.id => 1 )
Array.new Disciplina.all(Disciplina.professores.id => 1 )
These instructions should mean the same, and make the same (or almost) SQL.
That's my code:
#TABLES:
#
#disciplina
# id_disciplina,
# nome
#professor
# id_professor
#
#professor_disciplina
# id_professor
# id_disciplina
class Disciplina
include DataMapper::Resource
storage_names[:default] = "disciplina"
property :id, Serial, :field => 'id_disciplina'
property :nome, String
has n, :professor_disciplinas, 'ProfessorDisciplina', :child_key
=> [ :id_disciplina ]
has n, :professores, 'Professor', :through =>
:professor_disciplinas, :via => :professor
end
class Professor
include DataMapper::Resource
storage_names[:default] = "professor"
property :id, Serial, :field => 'id_professor'
has n, :professor_disciplinas, 'ProfessorDisciplina', :child_key
=> [ :id_professor ]
has n, :disciplinas, :through => :professor_disciplinas
end
class ProfessorDisciplina
include DataMapper::Resource
storage_names[:default] = "professor_disciplina"
belongs_to :professor , 'Professor' , :child_key => [
:id_professor ], :key => true
belongs_to :disciplina, 'Disciplina', :child_key => [
:id_disciplina ], :key => true
end
# Nice SQL
Array.new Disciplina.all(Disciplina.professor_disciplinas.professor.id => 1 )
# Bad SQL (database error and weird stuff)
Array.new Disciplina.all(Disciplina.professores.id => 1 )
The generated SQL for each instruction:
-- Nice SQL
SELECT "disciplina"."id_disciplina", "disciplina"."nome"
FROM "disciplina"
INNER JOIN "professor_disciplina" ON "disciplina"."id_disciplina" =
"professor_disciplina"."id_disciplina"
INNER JOIN "professor" ON "professor_disciplina"."id_professor" =
"professor"."id_professor"
WHERE "professor"."id_professor" = 1
GROUP BY "disciplina"."id_disciplina", "disciplina"."nome" ORDER BY
"disciplina"."id_disciplina"
-- Bad SQL
SELECT "disciplina"."id_disciplina", "disciplina"."nome"
FROM "disciplina"
INNER JOIN "professor_disciplina" ON "disciplina"."id_professor" =
"professor_disciplina"."id_professor" -- should be disciplina.id_disciplina
INNER JOIN "professor_disciplina" "professor_disciplina_1" ON
"professor_disciplina"."id_disciplina" =
"professor_disciplina_1"."id_disciplina" -- why another join?
INNER JOIN "professor" ON "professor_disciplina_1"."id_professor" =
"professor"."id_professor"
WHERE "professor"."id_professor" = 1
GROUP BY "disciplina"."id_disciplina", "disciplina"."nome"
ORDER BY "disciplina"."id_disciplina"
The bad SQL got a weird join and is using a wrong id.
What am I doing wrong? I think that maybe it's something with the :via
option.
Thanks in advance,
------------------------------------------------------
Syntax Highlighted version of the code:
Ruby Code
http://dpaste.com/533933/
Generated SQL
http://dpaste.com/533934/
--
You received this message because you are subscribed to the Google Groups
"DataMapper" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/datamapper?hl=en.
