rustrust-diesel

How to inner join to self using Diesel in Rust?

I've got a case where I need to inner join from a table block_headers to itself due to a parent-child relationship which exists in the same table. I have tried a number of different combinations of schema/model/attributes etc. using Diesel's ORM functionality but haven't found a way to make it work/compile yet.

I've reduced things down to the bare minimum for this question.

The table definition in question is:

table! {
    // Note: This is not the real PK of the table, but Diesel seems to require one.
    // I'm only querying this table so I simply chose a column.
    block_headers (block_height) { 
        block_height -> Integer,  // INTEGER NOT NULL
        index_block_hash -> Text, // TEXT NOT NULL
        parent_block_id -> Text   // TEXT NOT NULL
    }
}

In this case the parent_block_id of the child points to the index_block_hash of the parent. The reason for the inner join to itself is due to the fact that there may be multiple blocks at a given block_height, but only one is valid (the one which has a child block pointing back to itself). Here's a short example of the results from the query below:

0|55c9861be5cff984a20ce6d99d4aa65941412889bdc665094136429b84f8c2ee|ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
1|918697ef63f9d8bdf844c3312b299e72a231cde542f3173f7755bb8c1cdaf3a7|55c9861be5cff984a20ce6d99d4aa65941412889bdc665094136429b84f8c2ee
2|ca4b40509fa64c3676038b2c0f868559d1583c76617dee7b958028b17cc141b1|918697ef63f9d8bdf844c3312b299e72a231cde542f3173f7755bb8c1cdaf3a7
3|48edeb155557b9f301eea81343df18110ce642d093c4d689223492a57b9f3f0d|ca4b40509fa64c3676038b2c0f868559d1583c76617dee7b958028b17cc141b1
4|d2ffe2e8d03755ebe8a8285ac075de76cac4888ee9f8fce4b03c5a651b0eac54|48edeb155557b9f301eea81343df18110ce642d093c4d689223492a57b9f3f0d
5|537b9aaa545ed03635a58d092c5b9d585d78f8cb1e1214a8a7cfd20aa96d4718|d2ffe2e8d03755ebe8a8285ac075de76cac4888ee9f8fce4b03c5a651b0eac54
6|02006dc91b18f1d6eadf909e81e8fce5894591fd56030adc5868f62a6d005083|537b9aaa545ed03635a58d092c5b9d585d78f8cb1e1214a8a7cfd20aa96d4718

The SQL I'm trying to achieve for this example is:

SELECT 
    bh1.block_height, 
    bh1.index_block_hash 
  FROM 
    block_headers bh1 
  INNER JOIN 
    block_headers bh2 ON bh2.parent_block_id = bh1.index_block_hash 
  ORDER BY bh1.block_height ASC

The model in my latest attempt looks like:

#[derive(Queryable, Selectable, Identifiable, PartialEq, Eq, Debug, Clone, Associations)]
#[diesel(primary_key(block_height))]
#[diesel(belongs_to(BlockHeader, foreign_key = parent_block_id))]
#[diesel(table_name = block_headers)]
pub struct BlockHeader {
    pub block_height: i32,
    pub index_block_hash: String,
    pub parent_block_id: String
}

NOTE: I've tried a number of variations with belongs_to and the foreign_key etc. but nothing I've tried yet compiles.

And finally, what I'm trying to do is something like the following, using Diesel:

let result = block_headers::table
    .inner_join(block_headers::table.on(block_headers::parent_block_id.eq(block_headers::index_block_hash)))
    .filter(block_headers::index_block_hash.eq(&self.block_hash))
    .first::<BlockHeader>()
    .optional();

Which results in the following wall of text:

type mismatch resolving `<Once as Plus<Once>>::Output == Once`
2 redundant requirements hidden
required for `((block_height, index_block_hash, parent_block_id), (block_height, index_block_hash, parent_block_id))` to implement `diesel::AppearsOnTable<diesel::query_source::joins::Join<schema::block_headers::table, schema::block_headers::table, diesel::query_source::joins::Inner>>`
the full type name has been written to '/home/cylwit/Code/github.com/stacks-network/clarity-wasm/target/debug/deps/clarity_ab_tester-313c89c783028c52.long-type-10333939363849624741.txt'
required for `diesel::query_source::joins::Join<schema::block_headers::table, schema::block_headers::table, diesel::query_source::joins::Inner>` to implement `diesel::QuerySource`
1 redundant requirement hidden
required for `JoinOn<Join<table, table, Inner>, Grouped<Eq<parent_block_id, index_block_hash>>>` to implement `diesel::QuerySource`
the full type name has been written to '/home/cylwit/Code/github.com/stacks-network/clarity-wasm/target/debug/deps/clarity_ab_tester-313c89c783028c52.long-type-9677256780905495163.txt'
required for `diesel::query_builder::select_statement::SelectStatement<diesel::query_builder::from_clause::FromClause<schema::block_headers::table>>` to implement `diesel::query_dsl::InternalJoinDsl<schema::block_headers::table, diesel::query_source::joins::Inner, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<schema::block_headers::columns::parent_block_id, schema::block_headers::columns::index_block_hash>>>`
1 redundant requirement hidden
required for `schema::block_headers::table` to implement `diesel::query_dsl::InternalJoinDsl<schema::block_headers::table, diesel::query_source::joins::Inner, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<schema::block_headers::columns::parent_block_id, schema::block_headers::columns::index_block_hash>>>`
required for `schema::block_headers::table` to implement `diesel::query_dsl::JoinWithImplicitOnClause<diesel::query_source::joins::OnClauseWrapper<schema::block_headers::table, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<schema::block_headers::columns::parent_block_id, schema::block_headers::columns::index_block_hash>>>, diesel::query_source::joins::Inner>`rustcClick for full compiler diagnostic
context.rs(233, 14): required by a bound introduced by this call
schema.rs(5, 9): required for `schema::block_headers::columns::block_height` to implement `diesel::AppearsOnTable<diesel::query_source::joins::Join<schema::block_headers::table, schema::block_headers::table, diesel::query_source::joins::Inner>>`
mod.rs(521, 15): required by a bound in `diesel::QueryDsl::inner_join`
type mismatch resolving `<Once as Plus<Once>>::Output == Once`
2 redundant requirements hidden
required for `((block_height, index_block_hash, parent_block_id), (block_height, index_block_hash, parent_block_id))` to implement `diesel::AppearsOnTable<diesel::query_source::joins::Join<schema::block_headers::table, schema::block_headers::table, diesel::query_source::joins::Inner>>`
the full type name has been written to '/home/cylwit/Code/github.com/stacks-network/clarity-wasm/target/debug/deps/clarity_ab_tester-b6173cfc79c11f16.long-type-15833938815560068956.txt'
required for `diesel::query_source::joins::Join<schema::block_headers::table, schema::block_headers::table, diesel::query_source::joins::Inner>` to implement `diesel::QuerySource`
1 redundant requirement hidden
required for `JoinOn<Join<table, table, Inner>, Grouped<Eq<parent_block_id, index_block_hash>>>` to implement `diesel::QuerySource`
the full type name has been written to '/home/cylwit/Code/github.com/stacks-network/clarity-wasm/target/debug/deps/clarity_ab_tester-b6173cfc79c11f16.long-type-11396126599150083555.txt'
required for `diesel::query_builder::select_statement::SelectStatement<diesel::query_builder::from_clause::FromClause<schema::block_headers::table>>` to implement `diesel::query_dsl::InternalJoinDsl<schema::block_headers::table, diesel::query_source::joins::Inner, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<schema::block_headers::columns::parent_block_id, schema::block_headers::columns::index_block_hash>>>`
1 redundant requirement hidden
required for `schema::block_headers::table` to implement `diesel::query_dsl::InternalJoinDsl<schema::block_headers::table, diesel::query_source::joins::Inner, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<schema::block_headers::columns::parent_block_id, schema::block_headers::columns::index_block_hash>>>`
required for `schema::block_headers::table` to implement `diesel::query_dsl::JoinWithImplicitOnClause<diesel::query_source::joins::OnClauseWrapper<schema::block_headers::table, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<schema::block_headers::columns::parent_block_id, schema::block_headers::columns::index_block_hash>>>, diesel::query_source::joins::Inner>`rustcClick for full compiler diagnostic
context.rs(233, 14): required by a bound introduced by this call
schema.rs(5, 9): required for `schema::block_headers::columns::block_height` to implement `diesel::AppearsOnTable<diesel::query_source::joins::Join<schema::block_headers::table, schema::block_headers::table, diesel::query_source::joins::Inner>>`
mod.rs(521, 15): required by a bound in `diesel::QueryDsl::inner_join`

I'm not really sure how the Diesel query dsl I've tried would handle the bh1 vs. bh2 aliases in the SQL at the top of this question... I've also tried separating things out using separate variables for bh1 and bh2, but end up with pretty much the same result.

Maybe it's not possible to do using Diesel's ORM functionality and I'll need to use raw SQL?

Solution

  • This error message essentially says that one of your tables appears more than once in your from clause. That's an hard error for obvious reasons as it wouldn't be clear anymore to which of the tables a certain column refers. You need to use an alias for at least one of your tables in this case. That can be done via the alias! macro provided by diesel.

    You query would look like:

    let (bh1, bh2) = diesel::alias!(block_headers as bh1, block_headers as bh2);

let result = bh1.inner_join(bh2.on(bh1.field(block_headers::parent_block_id).eq(bh2.field(block_headers::index_block_hash))))
    .filter(bh1.field(block_headers::index_block_hash).eq(&self.block_hash))
    .select(bh1.fields((block_headers::block_height, block_headers::index_block_hash, block_headers::parent_block_id)))
    .first::<BlockHeader>()
    .optional();

    I've also added an explicit select so that what your query returns matches the structure of your BlockHeader type.