rust - Multiple foreign keys referencing same table in Diesel

Question

I am trying to create a struct which references the same table twice. The purpose of this is to create a kind of hierarchy of categories. Here is what I am trying to do for following tables:

create table product_category_rollup(
    id serial primary key,
    upper_category_id integer not null,
    lower_category_id integer not null,
    foreign key (upper_category_id) references product_category(id),
    foreign key (lower_category_id) references product_category(id)
);

create table product_category(
    id serial primary key,
    name varchar unique not null
);

I am trying to create the matching structs as in :

#[derive(Identifiable, Queryable)]
#[table_name = "product_category"]
pub struct ProductCategory {
    id: i32,
    name: String,
}

#[derive(Queryable, Identifiable, Associations)]
#[belongs_to(ProductCategory, foreign_key="upper_category_id")]
#[belongs_to(ProductCategory, foreign_key="lower_category_id")]
#[table_name = "product_category_rollup"]
pub struct ProductCategoryRollup {
    id: i32,
    upper_category_id: i32,
    lower_category_id: i32,
}

I am getting an error saying:

error[E0119]: conflicting implementations of trait `diesel::associations::BelongsTo<entities::ProductCategory>` for type `entities::ProductCategoryRollup`:
  --> src/entities.rs:29:35
   |
29 | #[derive(Queryable, Identifiable, Associations)]
   |                                   ^^^^^^^^^^^^
   |                                   |
   |                                   first implementation here
   |                                   conflicting implementation for `entities::ProductCategoryRollup`
   |
   = note: this error originates in a derive macro (in Nightly builds, run with -Z macro-backtrace for more info)

What is the proper way to have multiple foreign keys referencing the same table? Is this some inherent limitation in Diesel which was not worked out yet?

Answer 1

The BelongsTo trait definition is:

pub trait BelongsTo<Parent> {
    type ForeignKey: Hash + Eq;
    type ForeignKeyColumn: Column;
    fn foreign_key(&self) -> Option<&Self::ForeignKey>;
    fn foreign_key_column() -> Self::ForeignKeyColumn;
}

As a result of ForeignKey (and ForeignKeyColumn) being associated types, rather than generic parameters, a given Child can only have one implementation of BelongsTo<Parent>.

---

In general, it seems that BelongsTo is fairly limited; note that it's also limited to a single column.