Skip to content

Fix JOINS by relationships  #77

@CosmoV

Description

@CosmoV

There is an error when processing filters on relationships at intersection of join chains. Added a test case representing the problem

In this example the following sql query is built

SELECT alpha.id, alpha.beta_id, alpha.gamma_id
FROM alpha
         JOIN beta AS beta_1 ON beta_1.id = alpha.beta_id
         JOIN beta_gamma_binding AS beta_gamma_binding_1 ON beta_1.id = beta_gamma_binding_1.beta_id
         JOIN gamma AS gamma_1 ON gamma_1.id = beta_gamma_binding_1.gamma_id
         JOIN delta AS delta_1 ON delta_1.id = gamma_1.delta_id
         JOIN gamma AS gamma_2 ON gamma_2.id = alpha.gamma_id

         --- here the error, condition "on" should use gamma_2
         JOIN delta AS delta_2 ON delta_2.id = gamma_1.delta_id
WHERE delta_1.name = 'delta_1'
  AND delta_2.name = 'delta_2'

It should be a slq query like this

SELECT alpha.id, alpha.beta_id, alpha.gamma_id
FROM alpha
         JOIN beta AS beta_1 ON beta_1.id = alpha.beta_id
         JOIN beta_gamma_binding AS beta_gamma_binding_1 ON beta_1.id = beta_gamma_binding_1.beta_id
         JOIN gamma AS gamma_1 ON gamma_1.id = beta_gamma_binding_1.gamma_id
         JOIN delta AS delta_1 ON delta_1.id = gamma_1.delta_id
         JOIN gamma AS gamma_2 ON gamma_2.id = alpha.gamma_id

          --- correct "on" condition
         JOIN delta AS delta_2 ON delta_2.id = gamma_2.delta_id
WHERE delta_1.name = 'delta_1'
  AND delta_2.name = 'delta_2'

I've corrected that point in the PR #78

Replaced this query construction logic

select(Alpha)
    .join(aliased(Beta), Alpha.beta)
    .join(aliased(Gamma), Beta.gammas)
    .join(aliased(Delta), Gamma.delta)
    .join(aliased(Gamma), Alpha.gamma)
    .join(aliased(Delta), Gamma.delta)

with this one

select(Alpha)
    .join(beta_1 := aliased(Beta), Alpha.beta)
    .join(gamma_1 := aliased(Gamma), beta_1.gammas)
    .join(delta_1 := aliased(Delta), gamma_1.delta)
    .join(gamma_2 := aliased(Gamma), Alpha.gamma)
    .join(delta_2 := aliased(Delta), gamma_2.delta)

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions