Why is a table with two column references to another table not joinable?

Question

I have 2 issues.

1. There is no joinable! entry for "categories" in the schema.rs, why does this happen?

2. if 2 columns are added with foreign key reference to the same table, it doesn't allow me to add two joinable! statements manually , eg:

   joinable!(categories -> users (created_by));
   joinable!(categories -> users (modified_by));
   

I tried creating the "categories" table with one foreign key reference to the "users" table, dropped database, deleted schema.rs , ran migration , still no change to the schema.

table! {
    categories (id) {
        id -> Int4,
        category_code -> Varchar,
        description -> Varchar,
        gl_cr -> Varchar,
        gl_dr -> Varchar,
        asset_cost_less_than -> Nullable<Int4>,
        over_ride_depreciation_rate -> Bool,
        over_ride_useful_life -> Bool,
        created_by -> Int4,
        created_on -> Timestamp,
        modified_by -> Nullable<Int4>,
        modified_date -> Nullable<Timestamp>,
    }
}

table! {
    users (id) {
        id -> Int4,
        username -> Varchar,
        full_name -> Varchar,
        email -> Varchar,
        active -> Bool,
        created_on -> Timestamp,
    }
}

part of the schema.rs is

joinable!(branch_categories -> categories (category_id));
joinable!(branches -> cities (city_id));
joinable!(branches -> users (created_by));
joinable!(category_book_rate_codes -> books (book_id));
joinable!(category_book_rate_codes -> categories (category_id));
joinable!(category_book_rate_codes -> depreciation_rates (rate_id));
joinable!(cities -> states (state_id));
joinable!(cities -> users (created_by));

Answer 1

If your categories table has two columns that reference the users table:

CREATE TABLE categories (
    id           INTEGER,
    created_by   INTEGER NOT NULL REFERENCES users,
    modified_by  INTEGER          REFERENCES users,
    ...
);

Diesel will not generate a joinable! entry for categories to users since it would be ambiguous which column to use. You cannot create them both manually either because their implementations would conflict. The macro is designed to make this kind of query possible without an explicit ON clause:

categories::table.join(users::table)...

Not sure why removing one foreign reference and regenerating the schema didn't make a difference for you, but regardless... You can of course implement one of them manually like so:

joinable!(categories -> users (created_by));

You could then use the above query syntax when you wanted to join categories to users based on their creator. If you later want to join them based on their last modifier as well, you can do so in your query explicitly (untested):

categories::table.join(users::table.on(categories::modified_by.eq(users::id.nullable())))...