Several join filters with the same table

[ghost]

Hi,

I'm creating two join filters for the same table, and when I apply both the created query includes two joins with the same table.

I have two domain models A and B with a one to many relationship (and instance of A can have several instances of B but one instance of B shoud only have one instance of A). I need to list all B instances where to attributes of its A match with the values provided, so the desired query should be similar to SELECT * FROM B JOIN A ON B.a._id = A.id WHERE A.attr_1 = ? and A.attr_2 = ?

I have the following filter for the B model:

`
class BFilter(FilterSet):
attr_1 = graphene.String()
attr_2 = graphene.String(')

@classmethod
def attr_1_filter(cls, _, query, value):
    a_alias = cls.aliased(query, A, name='a_b_attr_1')

    query = query.join(
        a_alias,
        and_(
            B.a_id == a_alias.id
        ),
    )

    filter_ = (a_alias.attr_1 == value)
    return query, filter_

@classmethod
def attr_2_filter(cls, _, query, value):
    a_alias = cls.aliased(query, A, name='a_b_attr_2')

    query = query.join(
        a_alias,
        and_(
            B.a_id == a_alias.id
        ),
    )

    filter_ = (a_alias.attr_2 == value)
    return query, filter_

`

This code is working and there is not any problem with the behavior, but It generate an inefficient query statement like this:

FROM B JOIN A AS a_b_attr_1 ON B.a_id = a_b_attr_1.id JOIN A AS a_b_attr_1 ON B.a_id = a_b_attr_1.id

As you can see the join is duplicated. Based on official reuires join doc 'Identical joins will be skipped by sqlalchemy.', I have used the same alias name (i.e.: a_b) expecting to find similar to:

FROM B JOIN A AS a_b ON B.a_id = a_b.id

Instead, the following error is raised

(sqlite3.OperationalError) ambiguous column name: a_b.attr_1

And the generated query is like:

FROM B JOIN A AS a_b ON B.a_id = a_b.id JOIN A AS a_b ON B.a_id = a_b.id WHERE a_b.attr_1 = ? AND a_b.attr_1 = ?

Do you know if there is any reason for this duplicated join? Is there another way to use the same join to different filters?

Thanks a lot in advance.

[art1415926535]

Yes. SQLAlchemy 1.4 does not skips such joins.
I'll try to fix this bug.