Larger Inner Join with Junction Tables

Diesel 2.0
1

Hello everyone,

I’m quite new and currently I’m facing a problem. It’s the last step for my MVP.

I don’t know how to properly join my tables and it’s driving me crazy. I’m tackling this problem since 8 hours and hopefully someone can help me.

Basically I have the following tables: Event, Organizer, Discipline, Category, Event_Category, Event_Discipline.

This is how my models and schema looks:

[https://play.rust-lang.org/?version=stable&mode=debug&edition=2018&gist=49ea6498252d02f7b1268d5bf8b767ba](http://Playground Link)

And now I’m wondering how to join these tables to one single table where select everything except the ids.

My last state was this:

 use crate::database::schema::*;

        let join = event::dsl::event
            .inner_join(
                organizer::dsl::organizer.on(organizer::id.eq(event::fk_organizer_id)),
            )
            .inner_join(
                event_category::dsl::event_category
                    .on(event_category::fk_event_id.eq(event::id)),
            )
            .inner_join(
                event_discipline::table.on(event_discipline::fk_event_id.eq(event::id)),
            );

I really appreciate any help! :slight_smile:

2

Can you add some information about what exactly is not working as expected and what you expect as outcome of such an query?

3

Hey ty for the reply and sure :slight_smile:

The optimum would be to express this raw SQL query into the diesel logic:
SELECT *
FROM (((event
INNER JOIN event_category ON event.id = event_category.fk_event_id)
INNER JOIN event_discipline ON revent.id = event_discipline.fk_event_id)
INNER JOIN organizer ON event.fk_organizer_id = organizer.id);

There are still the ids included but I think the idea is clear.

I’m not sure how to translate this query into the ORM logic.

The result could be a struct that contains the information.

4

The diesel dsl snipped corresponds to that sql, so I’m not sure what your are exactly asking for. Do you get an error message while joining tables in that way? Is there anything else wrong?

5

Oh sorry I missed a line!

I want to store the results in a variable so my expression looks like this:

let result = join.load(connection).unwrap();

The problem here is that I have to specify a type after load:
type annotations needed for std::vec::Vec<U>

cannot infer type for type parameter U declared on the associated function loadrustc(E0282)

I have absolutely no clue which type this should be because I don’t know the return type.
My initial thought was to define a new struct to store the information:

#[derive(Queryable)]
pub struct Result {
    event_title: String,
    event_description: String,
    event_target_date: NaiveDate,
    event_deadline: NaiveDate,
    event_address: String,
    event_postcode: i32,
    event_city: String,
    organizer_title: String,
    organizer_email: String,
    organizer_address: String,
    organizer_postcode: String,
    organizer_city: String,
    organizer_phone: String,
    organizer_website: String,
    category_title: String,
    discipline_distance: String,
    discipline_fee: String,
    discipline_starttime: String,
    discipline_official: bool,
}

And then I select the values i.e. with:

.select((
                event::title,
                event::description,
                event::target_date,
                event::deadline,
                event::address,
                event::postcode,
                event::city,
                organizer::title,
                organizer::email,
                organizer::address,
                organizer::postcode,
                organizer::city,
                organizer::phone,
                organizer::website,
                category::title;
            )

But I can’t select the category because I join on the junction table and I don’t know how to access the category table. In a raw SQL statement I would also get the category table results.

6

Technically speaking is the type U some user provided type that implements Queryable<ST, DB> where ST corresponds to the type returned by your query. Practically speaking is U any type that implements/derives Queryable with fields in the corresponding order as listed in your select clause (position, types and field count need to match). At least the field count does not match between your select clause and your Queryable struct.
For the default select clause generated by .inner_join, see the corresponding documentation here.

As for accessing category::title: I do not see that the category table is part of your from clause, neither is it part of any join. To access that field you first need to join the corresponding table.

1 Like
7

You are right! I changed the struct and get logic:

#[derive(Queryable)]
pub struct Result {
    event_title: String,
    event_description: String,
    event_target_date: NaiveDate,
    event_deadline: NaiveDate,
    event_address: String,
    event_postcode: i32,
    event_city: String,
    organizer_title: String,
    organizer_email: String,
    organizer_address: String,
    organizer_postcode: String,
    organizer_city: String,
    organizer_phone: String,
    organizer_website: String,
    category_title: String,
    discipline_distance: String,
    discipline_fee: String,
    discipline_starttime: String,
    discipline_official: bool,
}

pub fn get_events(connection: &MysqlConnection) {
        use crate::database::schema::*;

        let join = event::dsl::event
            .inner_join(
                organizer::dsl::organizer.on(organizer::id.eq(event::fk_organizer_id)),
            )
            .inner_join(
                event_category::dsl::event_category
                    .on(event_category::fk_event_id.eq(event::id)),
            )
            .inner_join(
                event_discipline::table.on(event_discipline::fk_event_id.eq(event::id)),
            )
            .inner_join(
                category::table.on(category::id.eq(event_category::fk_category_id))
            )
            .inner_join(
                discipline::table.on(discipline::id.eq(event_discipline::fk_discipline_id))
            )
            .select((
                event::title,
                event::description,
                event::target_date,
                event::deadline,
                event::address,
                event::postcode,
                event::city,
                organizer::title,
                organizer::email,
                organizer::address,
                organizer::postcode,
                organizer::city,
                organizer::phone,
                organizer::website,
                category::title,
                discipline::distance,
                discipline::fee,
                discipline::starttime,
                event_discipline::official,
            ));

        let result:Vec<Result> = join.load(connection).unwrap();
    }

Unfortunately there are still 5 erros inside the load logic:

the trait bound chrono::NaiveDate: diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Date>, diesel::mysql::Mysql> is not satisfied
the following implementations were found:
<chrono::NaiveDate as diesel::deserialize::FromSql<diesel::sql_types::Date, diesel::mysql::Mysql>>

the trait bound bool: diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Bool>, diesel::mysql::Mysql> is not satisfied

the following implementations were found:

<bool as diesel::deserialize::FromSql<diesel::sql_types::Bool, diesel::mysql::Mysql>>

These are two of them.

I really appreciate your patience and help!

8

This error says that you cannot load a nullable date field into a chrono::NaiveDate. You need to use Option<NaiveDate> instead.

This error says basically the same, but for bool + nullable boolean fields. You need to use Option<bool> instead.

1 Like
9

Ok so I wrapped the NaiveDate and Time that I’m using into the options field.

There are 2 more problems:

the trait bound std::option::Option<chrono::NaiveDate>: diesel::Queryable<diesel::sql_types::Date, diesel::mysql::Mysql> is not satisfied
the following implementations were found:
<std::option::Option as diesel::Queryable<diesel::sql_types::Nullable, DB>>

And several times

the trait bound *const str: diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, diesel::mysql::Mysql> is not satisfied
the following implementations were found:
<*const [u8] as diesel::deserialize::FromSql<diesel::sql_types::Binary, DB>>
<*const str as diesel::deserialize::FromSql<diesel::sql_types::Text, DB>>

Edit: The struct looks atm like this:

#[derive(Queryable)]
pub struct Result {
    event_title: String,
    event_description: Option<String>,
    event_target_date: Option<NaiveDate>,
    event_deadline: Option<NaiveDate>,
    event_address: Option<String>,
    event_postcode: i32,
    event_city: Option<String>,
    organizer_title: String,
    organizer_email: Option<String>,
    organizer_address: Option<String>,
    organizer_postcode: i32,
    organizer_city: String,
    organizer_phone: Option<String>,
    organizer_website: Option<String>,
    category_title: String,
    discipline_distance: String,
    discipline_fee: Option<String>,
    discipline_starttime: Option<String>,
    discipline_official: Option<bool>,
}
10

The first error says that you have somewhere an Option<NaiveDate> where you should have a plain NaiveDate instead. The second error message is basically the same as the last two but for String fields. So you need to use Option<String> instead of String there.

1 Like
11

Damn it’s so confusing. I compared the structs in the models and they should be the same now. All Queryable Structs have to be the same right?

12

All Queryable structs for the same query needs to be the same. For different queries they can be different.

1 Like
13

Oh wow! You helped me so much. It made finally click!
I solved it :slight_smile:

There was a small difference between my schema.rs schemas and my struct!