I tried to make a small SQLite-based project, but could not replicate the same situation. Although I used TAssociationProp.Lazy, it always generated a single SELECT, even without forcing eager fetching mode. Maybe an SQLite driver specific behavior, or something related to the type of primary keys used. Anyway, I could not investigate further.
Back to the main project ...
When using
.CreateAlias('TurmaPeriodo', 'tper', TFetchMode.Eager)
.CreateAlias('tper.Turma', 'turm', TFetchMode.Eager)
.OrderBy('turm.Descricao')
it raises:
and no new SQL in log.
When using FetchEager, it generates this SQL:
SELECT A.id_MatriculaTurma AS A_id_MatriculaTurma, A.id_matricula AS A_id_matricula, A.id_TurmaPeriodo AS A_id_TurmaPeriodo, F.id_TurmaPeriodo AS F_id_TurmaPeriodo, F.Capacidade AS F_Capacidade, F.CapacidadeVirtual AS F_CapacidadeVirtual, F.id_turma AS F_id_turma, G.id_turma AS G_id_turma, G.Sigla AS G_Sigla, G.Descricao AS G_Descricao, G.DescricaoCurta AS G_DescricaoCurta, G.Tipo AS G_Tipo, G.Capacidade AS G_Capacidade, G.CapacidadeVirtual AS G_CapacidadeVirtual, G.Ordem AS G_Ordem, G.id_grau AS G_id_grau, G.id_unidade AS G_id_unidade, G.id_turno AS G_id_turno, F.id_Periodo AS F_id_Periodo, A.id_turmamodalidade AS A_id_turmamodalidade, A.id_creditooperacaomatricula AS A_id_creditooperacaomatricula
FROM aca_matriculasturmas A
LEFT JOIN (aca_matriculas B
LEFT JOIN (aca_alunos C
LEFT JOIN (ger_pessoasfisicas D
INNER JOIN ger_pessoas E ON (E.id_pessoa = D.id_pessoa)) ON (D.id_pessoa = C.id_pessoa)) ON (C.id_aluno = B.id_aluno)) ON (B.id_matricula = A.id_matricula)
LEFT JOIN (aca_periodosturmas F
LEFT JOIN aca_turmas G ON (G.id_turma = F.id_turma)
LEFT JOIN aca_periodos H ON (H.id_Periodo = F.id_Periodo)) ON (F.id_TurmaPeriodo = A.id_TurmaPeriodo)
WHERE H.id_Periodo = :p_0 AND
F.id_TurmaPeriodo = :p_1
ORDER BY G.Descricao Asc
p_0 = "{39FBC222-FA4D-4B4C-8315-88974610B1D7}" (ftString)
p_1 = "{39FBF873-7901-4A80-9DDF-07A82741C9E6}" (ftString)
Note that it generates the JOINs I need for the OrderBy:
LEFT JOIN (aca_periodosturmas F
LEFT JOIN aca_turmas G ON (G.id_turma = F.id_turma)
Without FetchEager (and without the OrderBy), it generates what I believe is normal for Lazy fetching mode:
SELECT A.id_MatriculaTurma AS A_id_MatriculaTurma, A.id_matricula AS A_id_matricula, A.id_TurmaPeriodo AS A_id_TurmaPeriodo, A.id_turmamodalidade AS A_id_turmamodalidade, A.id_creditooperacaomatricula AS A_id_creditooperacaomatricula
FROM aca_matriculasturmas A
LEFT JOIN (aca_periodosturmas B
LEFT JOIN aca_periodos C ON (C.id_Periodo = B.id_Periodo)) ON (B.id_TurmaPeriodo = A.id_TurmaPeriodo)
WHERE C.id_Periodo = :p_0 AND
B.id_TurmaPeriodo = :p_1
p_0 = "{39FBC222-FA4D-4B4C-8315-88974610B1D7}" (ftString)
p_1 = "{39FBF873-7901-4A80-9DDF-07A82741C9E6}" (ftString)
SELECT A.id_TurmaPeriodo AS A_id_TurmaPeriodo, A.Capacidade AS A_Capacidade, A.CapacidadeVirtual AS A_CapacidadeVirtual, A.id_turma AS A_id_turma, A.id_Periodo AS A_id_Periodo
FROM aca_periodosturmas A
WHERE A.id_TurmaPeriodo = :p_0
p_0 = "{39FBF873-7901-4A80-9DDF-07A82741C9E6}" (ftString)
SELECT A.id_turma AS A_id_turma, A.Sigla AS A_Sigla, A.Descricao AS A_Descricao, A.DescricaoCurta AS A_DescricaoCurta, A.Tipo AS A_Tipo, A.Capacidade AS A_Capacidade, A.CapacidadeVirtual AS A_CapacidadeVirtual, A.Ordem AS A_Ordem, A.id_grau AS A_id_grau, A.id_unidade AS A_id_unidade, A.id_turno AS A_id_turno
FROM aca_turmas A
WHERE A.id_turma = :p_0
p_0 = "{39FBEF7A-F5E3-4628-B4A6-7B657F079BBB}" (ftString)
Works fine with .FetchEager, but not with CreateAlias/FetchMode.Eager.
Maybe it's because there's not a ManyValuedAssociation/TList of the entity for "aca_matriculasturmas" mapped in the entity class for "aca_periodosturmas" ?!? .
