How to do a Select * followed by a join

[bwintertkb]

Hi all,

First of all, thank you for developing this great library! I've got a question about building iterative sql queries using sea-orm. Basically I want to do a join with another table, I followed the tutorial on the your site and the my code compiles but it's not actually performing the join and instead just select the table I want to join from.

This is an example query that is produced

SELECT
"table1.col1"
"table1.col2"
"table1.col3"
FROM
  "table1"
  JOIN "table2" ON "table1"."col1" = "table2"."col1"
LIMIT
  1

The issue I am having is it is only returning the data from table1 and not concatenating the columns where the condition for table1 and table2 is met.

I execute the query using the following code

Entity::find()
            .from_raw_sql(Statement::from_string(DatabaseBackend::Postgres, query.to_owned()))
            .all(&self.connection)
            .await?

Which returns a Vec<Model>. Is this the correct way to do such a thing? Also, how can I build a sql statement using an Entity as the base which looks like SELECT * from "table1".

Please let me know if you need anymore info from me.

Thanks in advance!

[billy1624]

Hey @bwintertkb, welcome!! Sorry for the delay.

What you're trying to do is selecting related models: https://www.sea-ql.org/SeaORM/docs/basic-crud/select/#find-related-models

Let me know if you still have questions :)

[isgj]

Hey, probably this is asked also other times (sorry) but the methods find_{also|with}_related accept a related entity. How can can we specify the relation when the models are like

 pub struct Model { //referred as model1
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: Uuid,
}

pub struct Model { // referred as model2
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: Uuid,
    pub creator_id: Uuid, // points to the first model
    pub assignee_id: Option<Uuid>, // points to the first model too
}

Are the following scenarios possible with Seaorm:

• load the second model with it's creator (join model1 on model2.creator_id = model1.id) ?
• load the second model with it's asignee (join model1 on model2.assigne_id = model1.id) ?
• load the second model with it's creator and assignee (join model1 on model2.creator_id = model1.id join model1_2 on model2.asignee_id = model1_2.id) ?

[billy1624]

Hey @isgj, welcome! You need Linked trait for multiple join paths between a pair of entities. https://www.sea-ql.org/SeaORM/docs/relation/chained-relations/

[isgj]

Thanks, I got the first 2 scenarios. You cannot eager load more than 1 related/linked.

[billy1624]

Hey @isgj, related discussion can be found at https://github.com/SeaQL/sea-orm/discussions/1044. Feel free to join :)