Joins from aliased tables: add a helper method or document my workaround

[Expurple]

My simplified use case

Let's define a country table, a city table that references it, and a trip table that references two cities: the source and the destination. Then, for a given trip.id, I want to select source_country_name and destination_country_name in a single ergonomic query. This can be achieved by using two parallel join chains, with table aliases added for disambiguation.

For quite a while, I was stuck trying to compile this:

use sea_orm::JoinType::LeftJoin;
use sea_orm::*;
use sea_query::*;

// There are also equivalent aliases for the destination.
let source = Alias::new("source");
let source_country = Alias::new("source_country");

trip::Entity::find()
  .select_only()  
  // There are also equivalent method calls for the destination. They use City2 instead of City1.
  .column_as(Expr::col((source_country.clone(), country::Column::Name)), "source_country_name")
  .join_as(LeftJoin, trip::Relation::City1.def(), source)
  .join_as(LeftJoin, /* ??? relation from source.country_id to country ??? */, source_country)
  
  // Other irrelevant method calls are omitted.

My current workaround

Eventually, I figured out that I can define

let source_to_country_relation = {
  let mut rel = city::Relation::Country.def();
  rel.from_tbl = rel.from_tbl.alias(source.clone());
  rel
};

and fill the hole with that:

  .join_as(LeftJoin, source_to_country_relation, source_country)

Proposed solutions

Please add and document some hepler method that can be quicky discovered via IDE autocompletion or docs and express my intent concisely:

  .join_as(LeftJoin, city::Relation::Country.def().from_alias(source), source_country)
  
  // or
  
  .join_from_as(LeftJoin, source, city::Relation::Country.def(), source_country)
  
  // or something similar

Once we've set on the desired API, maybe I can even implement it, this seems to be easy. If we see that the implementation will take a while or won't happen at all, let's at least mention my workaround in the docs.

On a side note, I'd really appreciate if I could achieve the same thing with find_also_related()/find_also_linked() instead of join()/join_as(). That's really the idiomatic sea-orm way of doing joins, IMO. And it's less verbose, especially find_also_linked(). But seems like we need basic #1028 first, before talking about using aliases there.

Approximate migration code for playing around

use sea_orm_migration::prelude::*;

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
  async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
    manager
      .create_table(
        Table::create()
          .table(Country::Table)
          .col(ColumnDef::new(Country::Id).integer().not_null().auto_increment().primary_key())
          .col(ColumnDef::new(Country::Name).string().not_null())
          .to_owned(),
      )
      .await?;
    manager
      .create_table(
        Table::create()
          .table(City::Table)
          .col(ColumnDef::new(City::Id).integer().not_null().auto_increment().primary_key())
          .col(ColumnDef::new(City::CountryId).integer().not_null())
          .foreign_key(
            ForeignKey::create()
              .from(City::Table, City::CountryId)
              .to(Country::Table, Country::Id),
          )
          .to_owned(),
      )
      .await?;
    manager
      .create_table(
        Table::create()
          .table(Trip::Table)
          .col(ColumnDef::new(Trip::Id).integer().not_null().auto_increment().primary_key())
          .col(ColumnDef::new(Trip::SourceId).integer().not_null())
          .col(ColumnDef::new(Trip::DestinationId).integer().not_null())
          .foreign_key(
            ForeignKey::create()
              .from(Trip::Table, Trip::SourceId)
              .to(City::Table, City::Id),
          )
          .foreign_key(
            ForeignKey::create()
              .from(Trip::Table, Trip::DestinationId)
              .to(City::Table, City::Id),
          )
          .to_owned(),
      )
      .await
  }

  async fn down(&self, _: &SchemaManager) -> Result<(), DbErr> {
    unimplemented!();
  }
}

#[derive(DeriveIden)]
pub enum Country {
  Table,
  Id,
  Name,
}

#[derive(DeriveIden)]
pub enum City {
  Table,
  Id,
  CountryId,
}

#[derive(DeriveIden)]
pub enum Trip {
  Table,
  Id,
  SourceId,
  DestinationId,
}

[tyt2y3]

That's seems to be pretty interesting, however sorry I don't have a full grasp of the proposal. Would you mind opening a PR with some testcases?

[Expurple]

I didn't propose an exact design, just suggested two approximate solutions and asked for your opinion. If you want me to choose, I prefer the first one. Here's a PR with implementation and a basic doctest: #2146