Correlated subqueries

[bbqsrc]

Motivation

It is sometimes necessary to refer to a field from the surrounding query in a subquery. Here's some SQL as an example

SELECT t.* FROM transactions t
INNER JOIN transaction_users tu on t.id = tu.transaction_id
WHERE EXISTS (
    SELECT * FROM transaction_users itu
    INNER JOIN users iu on itu.user_id = iu.id
    WHERE itu.transaction_id = t.id
      AND iu.role = 'potato'
);

It is not clear to me how to, if it is already possible, to provide the t.id to the EXISTS subquery using sea-query.

[ikrivosheev]

@bbqsrc hello! Thank you for the issue. Can you show your rust code? How do you build query?

[bbqsrc]

I don't really have any Rust beyond the below for that above query:

impl Entity {
    fn do_it() -> Select<Self> {
        Self::find()
            .join(JoinType::InnerJoin, Relation::TransactionUsers.def())
            .join(JoinType::InnerJoin, super::transaction_users::Relation::Users.def())
            .filter(Expr::exists(
               // Here lies the problem
            ))
    }
}

I want to be able to refer to the column from transactions within the subquery with the comment above. Perhaps aliases can help?

[ikrivosheev]

@bbqsrc, you need something like this: Expr::col((Alias::new("t"), Alias::new("id"))). It returns ColumenRef and you can use it in queries.