How to define a one-to-one relationship on a table.

[bestgopher]

[billy1624]

Hey @bestgopher, does this help?

https://www.sea-ql.org/SeaORM/docs/relation/one-to-one

[bestgopher]

No.

[bestgopher]

I want to build one-to-one relationship between id and pid.

[billy1624]

Okay, self referencing one-to-one relation

[bestgopher]

It will stackoverflow.
fatal runtime error: stack overflow error: test failed, to rerun pass '--lib'

[billy1624]

Does this make sense?

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "metadata")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub uuid: Uuid,
    #[sea_orm(column_name = "type", enum_name = "Type")]
    pub ty: String,
    pub key: String,
    pub value: String,
    pub bytes: Vec<u8>,
    pub date: Option<Date>,
    pub time: Option<Time>,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "Entity",
        from = "Column::Type",
        to = "Column::Key",
    )]
    SelfRef
}

#[derive(Debug)]
pub struct SelfRefLinked;

impl Linked for SelfRefLinked {
    type FromEntity = Entity;

    type ToEntity = Entity;

    fn link(&self) -> Vec<RelationDef> {
        vec![
            Relation::SelfRef.def(),
        ]
    }
}

impl ActiveModelBehavior for ActiveModel {}

#[cfg(test)]
mod tests {
    use super::*;
    use sea_orm::*;
    use pretty_assertions::assert_eq;

    #[test]
    fn linked_1() {
        let model = Model {
            uuid: Uuid::default(),
            ty: "ty".to_owned(),
            key: "key".to_owned(),
            value: "value".to_owned(),
            bytes: vec![1, 2],
            date: None,
            time: None,
        };

        assert_eq!(
            model
                .find_linked(SelfRefLinked)
                .build(DbBackend::MySql)
                .to_string(),
            [
                r#"SELECT `metadata`.`uuid`, `metadata`.`type`, `metadata`.`key`, `metadata`.`value`, `metadata`.`bytes`, `metadata`.`date`, `metadata`.`time`"#,
                r#"FROM `metadata`"#,
                r#"INNER JOIN `metadata` AS `r0` ON `r0`.`type` = `metadata`.`key`"#,
                r#"WHERE `r0`.`uuid` = '00000000-0000-0000-0000-000000000000'"#,
            ]
            .join(" ")
        );
    }

    #[test]
    fn linked_2() {
        assert_eq!(
            Entity::find()
                .find_also_linked(SelfRefLinked)
                .build(DbBackend::MySql)
                .to_string(),
            [
                r#"SELECT"#,
                r#"`metadata`.`uuid` AS `A_uuid`, `metadata`.`type` AS `A_type`, `metadata`.`key` AS `A_key`, `metadata`.`value` AS `A_value`, `metadata`.`bytes` AS `A_bytes`, `metadata`.`date` AS `A_date`, `metadata`.`time` AS `A_time`,"#,
                r#"`metadata`.`uuid` AS `B_uuid`, `metadata`.`type` AS `B_type`, `metadata`.`key` AS `B_key`, `metadata`.`value` AS `B_value`, `metadata`.`bytes` AS `B_bytes`, `metadata`.`date` AS `B_date`, `metadata`.`time` AS `B_time`"#,
                r#"FROM `metadata`"#,
                r#"LEFT JOIN `metadata` ON `metadata`.`type` = `metadata`.`key`"#,
            ]
            .join(" ")
        );
    }
}

[tyt2y3]

Well, that's bad.

[billy1624]

Should be fixed :(

[tyt2y3]

Can we fix that in 0.3?

[bestgopher]

Can we fix that in 0.3?

of course

[billy1624]

We must

[bestgopher]

error: cannot find attribute `sea_orm` in this scope
  --> src/models/sys_dept.rs:52:7
   |
52 |     #[sea_orm(belongs_to = "Entity", from = "Column::Type", to = "Column::Key")]
   |       ^^^^^^^

[billy1624]

Did you enable macros feature?

[itsparser]

yes still not working

[McEazy2700]

Hi, How can I create a one-to-one relationship using sea-query? Here's my current code

manager.create_table(
            Table::create()
                .table(Profile::Table)
                .if_not_exists()
                .col(ColumnDef::new(Profile::Id).integer().not_null().auto_increment().primary_key())
                .col(ColumnDef::new(Profile::FirstName).string())
                .col(ColumnDef::new(Profile::LastName).string())
                .col(ColumnDef::new(Profile::Bio).string())
                .col(ColumnDef::new(Profile::UserId).integer().not_null())
                .foreign_key(
                    ForeignKey::create()
                        .name("FK_user_profile_foreign_key")
                        .from(Profile::Table, Profile::UserId)
                        .to(User::Table, User::Id)
                        .on_delete(ForeignKeyAction::Cascade)
                        .on_update(ForeignKeyAction::Cascade)
                )
                .to_owned()
        ).await

I need to make the relationship between Profile and User one-to-one

[billy1624]

I guess you need to add an unique index on the foreign key column

[zhongdongy]

Adding unique index on foreign key column doesn't seem to solve this. Hopefully the documentation can provide more info on how to write relations correctly using migration (SeaQuery).

[zhongdongy]

Sorry for not understanding your suggestion thoroughly, after adding .unique() when creating index, it works. Like this:

manager
    .create_index(
        Index::create()
            .name("idx_auth_user")
            .table(Authentication::Table)
            .col(Authentication::User)
            .unique()
            .to_owned(),
    )
    .await