SQLAlchemy query distinct values from filtered many to many relationship

Question

I have been struggling to put together the correct query for this and need some help. I have two tables with a many-to-many relationship. I would like to put together a performant query for all distinct values in table B using the filtered values from table A:

association_table = Table(
    "a_b",
    BaseModel.metadata,
    Column("a_id", ForeignKey("a.id"), primary_key=True),
    Column("b_id", ForeignKey("b.id"), primary_key=True),
)


class A(BaseModel):
    __tablename__ = "a"

    id: Mapped[int] = mapped_column(primary_key=True)

    field_a: Mapped[str] = mapped_column(nullable=False)
    field_b: Mapped[str] = mapped_column(nullable=False)
 
    b: Mapped[List["B"]] = relationship(
        secondary=job_tag_association_table, 
        back_populates="a", 
        passive_deletes=True
    )


class B(BaseModel):
    __tablename__ = "b"

    id: Mapped[int] = mapped_column(primary_key=True)

    name: Mapped[str] = mapped_column(nullable=False)

    b: Mapped[List["A"]] = relationship(
        secondary=job_tag_association_table, 
        back_populates="b", 
        passive_deletes=True
    )

I have a pre-filtered query of A objects (sqlalchemy.orm.query.Query) that I am getting from something like A.query.where(A.field_a == 'something'). I do not have access to the internals of this query, but for the purpose of solving this issue, all we need to know that it is equivalent to a filtered query as would be returned by A.query.

I want to join this query with B and get the distinct values of of B.name for the subset of these filtered A objects. Some example of what I am trying to do below:

filtered_a = A.query

[i for i in filtered_a.join(A.b).distinct(B.name)]

The above returns a list of A objects, but I am looking for a list of B objects instead (and it doesn't seem like I can get, as i.b does not seem to work. I am also not sure if the distinct is operating on the single object level, or if it is working globally across all A objects (as would be my intent). Any advice is appreciated - thanks!

Answer 1

Ok I think I have this sorted out. The answer is to use subqueries:

b = B.query.join(
    B.a.of_type(aliased(A, filtered_a.subquery()))
).distinct(B.name)

# List of strings as intended
[i.name for i in b]