Extending the DSL to allow a JOIN on a subselection

[ginger51011]

Hello!

I'm working on a feature and I think I know what I want to happen, but not how to express it in diesel.

Basically, I have tables A and B, where B has many-to-one relationship for A. I want to be able to join only the latest value in B to A (if it exists), and then to be able to do filtering etc. on that result. The reason for not just using raw SQL is that I want to augment the real query depending on some external parameters.

From what I can read in the extending diesel guide, I might want to do something like this:

//! Attempting to extend DSL
use diesel::{
    pg::Pg,
    query_builder::{AstPass, Query, QueryFragment, QueryId},
};

use crate::schema::B;

pub(super) trait LatestBJoin: Sized {
    fn join_latest_b(self) -> BJoined<Self>;
}

impl<T> LatestBJoin for T {
    fn join_latest_b(self) -> LatestBJoined<Self> {
        LatestBJoined { query: self }
    }
}

#[derive(Debug, Clone, Copy, QueryId)]
pub(super) struct LatestBJoined<T> {
    query: T,
}

impl<T> QueryFragment<Pg> for LatestBJoined<T>
where
    T: QueryFragment<Pg>,
{
    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, Pg>) -> QueryResult<()> {
        out.push_sql(
            r#"
LEFT OUTER JOIN (
    SELECT DISTINCT ON (B.a_id) *
    FROM B
    ORDER BY B.a_id, B.inserted_at DESC
) ON (B.a_id = A.id)
"#,
        );
        Ok(())
    }
}

and then for example

let mut q = A::as_select().join_latest_b().order_by(A::inserted_at);

So I guess I want to express that LatestBJoined can be handled just like a LeftJoin<A, B> . Can this be done, and if so how?

Please point out if I'm going down the wrong path. I was thinking about just saving latest_b as a view and select from that, but it seems like diesel does not support views so that is also not that great.

This discussion about greatest-n-per-group might be related, but there is a mention about extending the DSL there as well but I can't figure out how to do it properly.

[weiznich]

This particular part of the query builder is currently hard to extend. We are open to suggestions how to improve that, but so far that hasn't been a priority to research for us.

That written: You can likely restructure your query to use a subquery instead of the join, so something like:

SELECT * FROM A INNER JOIN B ON A.id = B.a_id WHERE A.id = (SELECT other_B.a_id FROM B AS other_B WHERE B.id = other_b.id ORDER BY other_b.inserted_at DESC LIMIT 1)

[ginger51011]

Thanks for the reply. I will try something like you suggested out, seems like I can get it to work with some fidgeting :)