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

So I have been researching and looking into Diesel, and as already noted in the answer above this problem arises due to way in which the BelongsTo<Parent> trait is defined.

A way to circumvent this is to do something like following:

// This trait contains the behavior common to all types
// representing the product category
trait ProductCategory{
    fn new(id: i32, name: String) -> Self;
}

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

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

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

impl ProductCategory for RawProductCategory {
    fn new(id: i32, name: String) -> Self {
        RawProductCategory {
            id,
            name
        }
    }
}

impl ProductCategory for UpperProductCategory {
    fn new(id: i32, name: String) -> Self {
        UpperProductCategory {
            id,
            name
        }
    }
}

impl ProductCategory for LowerProductCategory {
    fn new(id: i32, name: String) -> Self {
        LowerProductCategory {
            id,
            name
        }
    }
}

impl Queryable<product_category::SqlType, diesel::pg::Pg> for RawProductCategory {
    type Row = (i32, String);
    fn build(row: Self::Row) -> Self {
        ProductCategory::new(row.0, row.1)
    }
}

impl Queryable<product_category::SqlType, diesel::pg::Pg> for UpperProductCategory {
    type Row = (i32, String);
    fn build(row: Self::Row) -> Self {
        ProductCategory::new(row.0, row.1)
    }
}

impl Queryable<product_category::SqlType, diesel::pg::Pg> for LowerProductCategory {
    type Row = (i32, String);
    fn build(row: Self::Row) -> Self {
        ProductCategory::new(row.0, row.1)
    }
}

Now I have noticed that that I have quite large code duplication with respect to implementations of Queryable, but I had no desire to reduce it by introducing yet another struct which contains a single field implementing the ProductCategory trait.

Now here comes the fun part. I have noticed why this arises, and have made opened an issue in diesel Github repository. Should this issue be resolved I will update this answer accordingly to show the nicer way of achieving same thing.