More than one foreign key from the same table

[aymanalqadhi]

Hey all,

So I have this ERD that I want to map using diesel:

I want to map the entity "Transaction" using rust. The SQL I ended up for the entity is as the following:

CREATE TABLE transactions (
        id          SERIAL    PRIMARY KEY,
        amount      NUMERIC   NOT NULL,
        state       BOOLEAN   NULL,
        signature   VARCHAR   NOT NULL,

        sender      SERIAL    NOT NULL REFERENCES users(id),
        receiver    SERIAL    NOT NULL REFERENCES users(id),
        made_at     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

        verified_by SERIAL    REFERENCES users(id)
)

The model code that I wrote to map the database table is as follows:

#[derive(Associations, Clone, Debug, Identifiable, Queryable, Validate)]
#[belongs_to(User, foreign_key = "sender")]
#[belongs_to(User, foreign_key = "receiver")]
pub struct Transaction {
    pub id: KeyType,

    pub amount: BigDecimal,
    pub state: Option<bool>,
    pub signature: String,

    pub sender: KeyType,
    pub receiver: KeyType,
    pub made_at: chrono::DateTime<chrono::Utc>,
}

When I try to compile, I get this error:

error[E0119]: conflicting implementations of trait `diesel::associations::BelongsTo<data::models::user::User>` for type `data::models::financial_record::Transaction`
  --> wasl/src/data/models/financial_record.rs:23:10
   |
23 | #[derive(Associations, Clone, Debug, Identifiable, Queryable, Validate)]
   |          ^^^^^^^^^^^^
   |          |
   |          first implementation here
   |          conflicting implementation for `data::models::financial_record::Transaction`
   |
   = note: this error originates in the derive macro `Associations` (in Nightly builds, run with -Z macro-backtrace for more info)

I have done some research, and found that diesel does not support multiple uses of the #[belongs_to] attribute because BelongsTo trait can only be implemented once per struct.
Is there any way to solve this problem? or if not, how can I map the entity without arriving to this situation?

[weiznich]

Let me start with some reasoning why multiple belongs_to attribute referencing the same table are not allowed. The issue there is that we cannot say which of the referenced dependencies should be used to resolve Transactions::belonging_to(&user). We could likely require that you always need to specify the used foreign key column, but that would add unneeded boiler plate code for the case where only on belongs_to attribute is given.

So how to "fix" this: As Transactions::belonging_to(&user) only constructs a simple query (users::table.filter(users::id.eq_any(foreign_keys))) you can just write this query explicitly.

What could be improved here is the derive itself, so that we already issue an error there indicating this solution. If someone is interested in contributing this improvement I can add details about the potential implementation.

[aymanalqadhi]

@weiznich
Why would the solution that @Mingun suggested be a bad one?

[weiznich]

Why would the solution that @Mingun suggested be a bad one?

Because that's not possible to implement, at least not by "just add another method or attribute."

Let me explain that a bit more in detail:

Transactions::belonging_to uses BelongingToDsl. Just adding a belogning_to_by method there is not possible as we would need to have a type for the foreign key column. @minigun just let of that information from their "suggestion". I would such call "simplifications" common for their comments on this repo. So why is this a problem at all: Each column has a unique type, therefore we need some way to specify that type as part of the trait definition. BeloningToDsl uses the definition by BelongsTo. This is the trait that #[derive(Associations]] implements for your type. As you can see there this trait defines the type of the foreign key column as associated type. Now you could say: Lets just use this type and I would answer that this is possible but would not solve your problem. In fact you see the problem as part of the error message above: You cannot implement the same trait twice for the same struct if all generic parameters are the same, even if any associated type is different. Thats a language restriction, so that's not up to discussion.
Now you could argue: Lets just change the trait to include the foreign key as well. Again thats something that is possible and would likely solve your problem. So whats my problem with that solution: There wouldn't be any way to differentiate between associations with one foreign key relation and with those more than one relation. The problem with that is that it would very likely require that the foreign key is always required as part of the Transactions::beloging_to(&user) call, even if there is only one matching relation. Thats something I find not a acceptable change, especially as the alternative contains basically the same fields and is not any longer to write: transactions.filter(foreign_key.eq(user.id)).
Now one could argue that there must be an other solution here and that might be true. I'm happy to see an concrete proposal here but at least for me its not something with priority (as my use cases do not have many of those case) and I want to spend my limited time to work something else more important for my own use cases.

@Mingun To repeat that again: Your repeated simplified "suggestions" how to solve problems are not welcome here. Either spend the time to do at least a minimal amount of checking on how to implement your suggestions or just stop posting them. Otherwise I must consider your posts as hostile.

[aymanalqadhi]

Thanks for your answer. I guess I will use your alternative solution for the moment.