Support subqueries in joins

[seijikun]

In #55 you decided not to support subqueries in joins until someone has a good usecase. I think I might be able to provide that now; with the famous greatest-n-per-group problem:

Following example:
I have a list of contracts. Every contract is between me and a customer. The customer's address (where the invoice should be sent, e.g.) changes over time. Each CustomerAddress has a date specifying the starting point (as a date) from which on it is valid.

erDiagram
    Contract {
        id UUID
        customer_id UUID
    }
    Customer {
        id UUID
    }
    Customer ||--o{ Contract : "customer_id"
    CustomerAddress {
        id UUID
        customer_id UUID
        valid_from DATE
    }
    CustomerAddress ||--o{ Customer : "id"

Loading

A typical query in this setup is to retrieve a list of active contracts, joined with the customers and their currently active address.
For sqlite, this would look something like this:

SELECT * FROM "Contract"
INNER JOIN "Customer" ON "Customer"."id" = "Contract"."customer_id"
INNER JOIN (
	SELECT CustomerAddress.* FROM CustomerAddress
	WHERE valid_from <= current_date AND customer_id = ?
	GROUP BY customer_id
	HAVING valid_from = max(valid_from)
) addrjoin ON addrjoin.customer_id = Contract.customer_id

As soon as #2179 lands, this would make sea-orm so much more powerful, because that would allow:

#[derive(Debug, Serialize, Deserialize, ToSchema, FromQueryResult, DerivePartialModel)]
#[sea_orm(entity = "crate::db::Contract")]
pub struct Contract {
	pub id: Uuid,
	#[sea_orm(nested)]
	pub customer: CustomerWithCurrentAddr
}

#[derive(Debug, Serialize, Deserialize, ToSchema, FromQueryResult, DerivePartialModel)]
#[sea_orm(entity = "crate::db::Customer")]
pub struct CustomerWithCurrentAddr {
	pub id: Uuid,
	#[sea_orm(nested)]
	pub current_address: CustomerAddress
}

#[derive(Debug, Serialize, Deserialize, ToSchema, FromQueryResult, DerivePartialModel)]
#[sea_orm(entity = "crate::db::CustomerAddress")]
pub struct CustomerAddress {
	pub id: Uuid
}

// Get a list of all contract
// directly left joined with the respective contract's customer
// directly left joined with the customer's respective currently valid address
let res: Vec<Contract> = db::Contracts::find()
	.inner_join(db::Customers)
	.inner_join_subquery( // somewhat pseudocode
		JoinType::InnerJoin,
		// subquery
		Query::select()
			.columns(db::CustomerAddresses)
			.where(...)
			.group_by(db::customer_addresses::Column::CustomerId)
			.having(...)
			.as("subquery"),
		// on condition
		Expr::col(("subquery", db::customer_addresses::Column::CustomerId))
			.eq(db::customers::Column::Id)
	)
	.into_partial_model()
	.all(db)
	.await?;

[Sytten]

I was also looking for that, kinda of annoying that it can't be done

[an-tran511]

I think you can use sea-query to achieve this