search

mts-ai / FastAPI-JSONAPI

JSON:API for FastAPI
https://mts-ai.github.io/FastAPI-JSONAPI/
MIT License
109 stars 10 forks source link

Fix JOINS by relationships #77

Closed CosmoV closed 9 months ago

CosmoV commented 9 months ago

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 https://github.com/mts-ai/FastAPI-JSONAPI/pull/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)