Lost on complex query.

[DenuxPlays]

Hey,
I am currently trying to represent this sql query:

SELECT t.
	FROM "transaction" t
			 LEFT JOIN "account" a1 ON t.source = a1.id
			 LEFT JOIN "account" a2 ON t.destination = a2.id
			 LEFT JOIN "user_account" ua1 ON a1.id = ua1.account_id
			 LEFT JOIN "user_account" ua2 ON a2.id = ua2.account_id
	WHERE (a1.owner = :user_id OR ua1.user_id = :user_id) OR (a2.owner = :user_id OR ua2.user_id = :user_id);

How can I do this so that I can return an Select<Entity>?

This is the closest I came with the documentation:

Self::find()
			.join_as(JoinType::LeftJoin, transaction::Relation::Account1.def(), Alias::new("a1"))
			.join_as(JoinType::LeftJoin, transaction::Relation::Account2.def(), Alias::new("a2"))
			.join_as(
				JoinType::LeftJoin,
				user_account::Relation::Account.def().on_condition(|_left, right| {
					Expr::tbl(right, user_account::Column::AccountId)
						.equals(Expr::tbl(Alias::new("a1"), account::Column::Id))
						.into_condition()
				}),
				Alias::new("ua1"),
			)
			.join_as(
				JoinType::LeftJoin,
				user_account::Relation::Account.def().on_condition(|_left, right| {
					Expr::tbl(right, user_account::Column::AccountId)
						.equals(Expr::tbl(Alias::new("a2"), account::Column::Id))
						.into_condition()
				}),
				Alias::new("ua2"),
			)
			.filter(
				Condition::any()
					.add(Expr::tbl(Alias::new("a1"), account::Column::Owner).equals(user_id))
					.add(Expr::tbl(Alias::new("ua1"), user_account::Column::UserId).equals(user_id))
					.add(Expr::tbl(Alias::new("a2"), account::Column::Owner).equals(user_id))
					.add(Expr::tbl(Alias::new("ua2"), user_account::Column::UserId).equals(user_id)),
			).into()

[DenuxPlays]

Maybe I have to overythink my data model.
If I have to build such complex queries then there isn't something right.
But still if someone can help me and explain to me how this is possible it would be great :)

[tyt2y3]

That's pretty close. I think you don't need the on_conditions if I am not wrong.

[DenuxPlays]

That's pretty close. I think you don't need the on_conditions if I am not wrong.

Okay but what should I use instead of Expr::tbl?
As this method does not exist (I am using the sea_query::Expr If that's the right one)