How to use self referential foreign key in Rust Diesel

Question

I created a self referential table and I want to use it with Rust Diesel v2.x.

#[derive(Debug, PartialEq, Identifiable, Queryable, Selectable)]
#[diesel(table_name = modality)]
pub struct Modality {
    pub id: i32,
    pub ... wathever ...
    pub reply_to: i32  // Foreign key to Modality
}

But I haven't found much information on how to do so.

Answer 1

One can find an example in this issue (although with an old version of the syntax) and some hints in the official documentation in the association page.

It works the same way as when you write a foreign key (use belongs_to and i32) but most people don't name their self-referential column {table_name}_id so you must tell diesel which column is the foreign key. Here's an excerpt from the documentation:

By default, Diesel assumes that your foreign keys will follow the convention table_name_id. If your foreign key has a different name, you can provide the foreign_key argument to #[diesel(belongs_to)].

Here's an example:

#[derive(Debug, PartialEq, Identifiable, Queryable, Selectable)]
#[diesel(table_name = modality)]
#[diesel(belongs_to(Modality, foreign_key = reply_to))]
pub struct Modality {
    pub id: i32,
    pub ... wathever ...
    pub reply_to: i32  // Or Option<i32> if nullable
}