How to Join Tables using Left JOIN IN diesel

[dxphilo]

I have two table politicians and corruption_cases that are related. I want to query all the corruption cases of a given politician based on the politicians id.

Here’s how my table looks like for politician and corruption_cases:

// @generated automatically by Diesel CLI.

diesel::table! {
    corruption_cases (id) {
        id -> Int4,
        #[max_length = 255]
        name -> Varchar,
        politician_id -> Int4,
        #[max_length = 255]
        case_description -> Varchar,
        #[max_length = 255]
        case_date -> Varchar,
        #[max_length = 255]
        legal_outcome -> Nullable<Varchar>,
        created_at -> Nullable<Timestamptz>,
        updated_at -> Nullable<Timestamptz>,
    }
}

diesel::table! {
    politicians (politician_id) {
        politician_id -> Int4,
        name -> Varchar,
        photo_url -> Nullable<Varchar>,
        office -> Nullable<Varchar>,
        county -> Varchar,
        political_party -> Nullable<Varchar>,
        source_website -> Nullable<Varchar>,
        created_at -> Nullable<Timestamptz>,
        updated_at -> Nullable<Timestamptz>,
    }
}

I have followed the API Guide on joins but it seems something is off that I can recognize with my joins.

pub fn get_politician_with_cases(&self, politician_id: i32) -> Option<PoliticianCases> {
    let politician_with_cases = politicians::table
        .left_join(corruption_cases::table.on(politicians::politician_id.eq(corruption_cases::politician_id)))
        .filter(politicians::politician_id.eq(user_id)) 
        .select((Politician::as_select(), Option::<CorruptionCase>::as_select())))
        .first(&mut self.pool.get().unwrap())?;

    // ... code to handle results
}

The query doesn’t compile, I have tried using the query from the Guide but cant make it compile. Can you pinpoint what I might be missing here?

Thanks,Phil.

[weiznich]

It's hard to answer this question based on the provided details. I've tried to reproduce your problem locally but the following minimal extended code compiles fine with diesel = { version = "2.2.2", default-features = false, features = ["postgres", "chrono"] }:

se diesel::prelude::*;
use diesel::PgConnection;

mod schema {
    diesel::table! {
        corruption_cases (id) {
            id -> Int4,
            #[max_length = 255]
            name -> Varchar,
            politician_id -> Int4,
            #[max_length = 255]
            case_description -> Varchar,
            #[max_length = 255]
            case_date -> Varchar,
            #[max_length = 255]
            legal_outcome -> Nullable<Varchar>,
            created_at -> Nullable<Timestamptz>,
            updated_at -> Nullable<Timestamptz>,
        }
    }

    diesel::table! {
        politicians (politician_id) {
            politician_id -> Int4,
            name -> Varchar,
            photo_url -> Nullable<Varchar>,
            office -> Nullable<Varchar>,
            county -> Varchar,
            political_party -> Nullable<Varchar>,
            source_website -> Nullable<Varchar>,
            created_at -> Nullable<Timestamptz>,
            updated_at -> Nullable<Timestamptz>,
        }
    }

    diesel::allow_tables_to_appear_in_same_query!(corruption_cases, politicians);
}

#[derive(Queryable, Selectable)]
#[diesel(check_for_backend(diesel::pg::Pg))]
#[diesel(table_name = self::schema::politicians)]
struct Politician {
    name: String,
}

#[derive(Queryable, Selectable)]
#[diesel(check_for_backend(diesel::pg::Pg))]
#[diesel(table_name = self::schema::corruption_cases)]
struct CorruptionCase {
    id: i32,
}

pub fn get_politician_with_cases(politician_id: i32, conn: &mut PgConnection) {
    use crate::schema::*;
    let user_id = 42;
    let politician_with_cases: (Politician, Option<CorruptionCase>) = politicians::table
        .left_join(
            corruption_cases::table
                .on(politicians::politician_id.eq(corruption_cases::politician_id)),
        )
        .filter(politicians::politician_id.eq(user_id))
        .select((
            Politician::as_select(),
            Option::<CorruptionCase>::as_select(),
        ))
        .first(conn)
        .unwrap();
}

If your issue still exists try to provide at least:

• The complete error message that you get as emitted by rustc
• The relevant struct definitions
• Information about your diesel version + feature flags, rust version and your database system.