Use of nested joins in CollectionLambda

[dmtvanzanten]

I noticed that adding simply adding the join_relationships on top-level when using a query that generates a CollectionLambda will not result in the correct response. This is because the join_relationships of the top-level query will not include the joins required for the sub-query generated by the CollectionLambda.
Currently, the visit_CollectionLambda method of the AstToSqlAlchemyOrmVisitor does not add any join clauses when nested parameters are used.

For example: parent/any(p:p/subl1_of_parent/subl2_of_subl1/attr eq 1) will not work.

The following implementation of the visit_CollectionLambda will fix this.

def visit_CollectionLambda(self, node: ast.CollectionLambda):
    ":meta private:"
    owner_prop = self.visit(node.owner) # This is the attibute that is subjected to 'iterator' executing this lambda
    collection_model = inspect(owner_prop).property.entity.class_

    if node.lambda_:
        # For the lambda, we want to strip the identifier off, because
        # we will execute this as a subquery in the wanted model's context.
        subq_ast = utils.expression_relative_to_identifier(
            node.lambda_.identifier, node.lambda_.expression
        )
        subq_transformer = self.__class__(collection_model)
        subquery_filter = subq_transformer.visit(subq_ast)
    else:
        subquery_filter = None

    if isinstance(node.operator, ast.Any):
        exists = owner_prop.any(subquery_filter)
    else:
        # For an ALL query, invert both the filter and the EXISTS:
        if node.lambda_:
            subquery_filter = ~subquery_filter
        exists = ~owner_prop.any(subquery_filter)

    join_head = None
    joined = set()
    for j in subq_transformer.join_relationships:
        cls = j.mapper.class_
        if cls in joined:
            continue
        if join_head is None:
            join_head = orm.join(collection_model, cls)
        else:
            join_head = orm.join(join_head, cls)
        joined.add(cls)
    exists = exists.select_from(join_head)
    return exists