Custom from clause

[belovaf]

Is it possible to reuse dto structure field selection and result set binding, but specify custom from clause?
I have a relatively complex query, which I want to use as from clause and add custom filter / limit logic on top of this query.

For example, If I create a custom structure

#[derive(Queryable, Selectable)]
#[diesel(table_name = crate::schema::tasks)]
pub struct Task {
    pub id: i64,
    pub status: String,
}

I want to produce sql like this: select id, status from <complex_subquery>
I tried to implement this with QueryFragment but it looks like diesel doesn't expose from clause customization.

[weiznich]

It's hard to answer complex questions like this without having a lot of context. Given that this question does not include this context (like what's exactly <complex_subquery> or what exactly you already tried) I can only give a generic answer:

Diesel allows you to customize the generated DSL down to everything. You are not restricted to the build in DSL, you can bring your own extensions and even build your own separate DSL that doesn't use anything from diesel. Additionally you can always fall back to sql_query for cases where you believe it's too hard to extend the SQL.

[belovaf]

Thank you for response, I will try to add some context.
I want to pass <complex_subquery> as a custom string. It is effectively a union all tasks query with complex user access predicates. I beleive that this is not important, I don't want to customize this part with query builder. This subquery selects all tasks columns, so the problem boils down to a simple question: can I replace task table with custom query if I guarantee that custom query contains exactly same columns?
I want to reuse structure selection + mapping and query builder filters.

I tried to implement this with QueryFragment, but I can't understand how can I extract select clause without from clause from Task::as_select() expression.

[weiznich]

It's conceptually not possible that you just provide a string as from clause as diesel's query builder is optimized to check things at compile time and that string wouldn't have compile time information at all.

That written: You can implement a marker type that represents your string and provides these information. That's a bit of work but it's possible. Documentation for this topic is currently quite sparse, but you need to implement most of the traits that are implemented for a table struct.

There are slides from a old workshop that I've given some years back that cover that topic a bit more in detail: https://weiznich.github.io/rustfest2019-diesel-workshop/#/final-exercise The relevant source code for that workshop is here: https://github.com/weiznich/wundergraph-workshop?tab=readme-ov-file
Keep in mind that this targeted diesel 1.4 so syntax changed slightly since then.

[belovaf]

Thank you very much! I tried to create QueryFragment, which patches original query with query builder, but from clause is private, so I couldn't modify it.
I did not mind, that I can make this String looks like a table for diesel.
I will try it!

[belovaf]

I finally implemented it with QueryFragment.
But this implementation requires feature i-implement-a-third-party-backend-and-opt-into-breaking-changes.
Does it make sense?

Usage:

// simplified from query
let from_query = tasks.select(Task::as_select())
        .union_all(tasks.select(Task::as_select()));

let results = tasks
        .select(Task::as_select())
        .filter(status.eq(TODO))
        .from_clause(from_query, tasks)
        .load(&mut con)
        .await?;

Implementation

#[derive(Debug, Clone, QueryId)]
pub struct FromSelect<Q, FS, FA> {
    pub select: Q,
    pub from_select: FS,
    pub from_alias: FA,
}

impl<
        DB: Backend,
        FS: QueryFragment<DB>,
        FA: QueryFragment<DB>,
        F,
        S: QueryFragment<DB>,
        D: QueryFragment<DB>,
        W: QueryFragment<DB>,
        O: QueryFragment<DB>,
        LOF: QueryFragment<DB>,
        G: QueryFragment<DB>,
        H: QueryFragment<DB>,
        LC: QueryFragment<DB>,
    > QueryFragment<DB> for FromSelect<SelectStatement<F, S, D, W, O, LOF, G, H, LC>, FS, FA>
{
    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
        out.push_sql("SELECT ");
        self.select.distinct.walk_ast(out.reborrow())?;
        self.select.select.walk_ast(out.reborrow())?;
        out.push_sql(" FROM (");
        self.from_select.walk_ast(out.reborrow())?;
        out.push_sql(") ");
        self.from_alias.walk_ast(out.reborrow())?;
        self.select.where_clause.walk_ast(out.reborrow())?;
        self.select.group_by.walk_ast(out.reborrow())?;
        self.select.having.walk_ast(out.reborrow())?;
        self.select.order.walk_ast(out.reborrow())?;
        self.select.limit_offset.walk_ast(out.reborrow())?;
        self.select.locking.walk_ast(out.reborrow())?;
        Ok(())
    }
}

impl<Q: Query, FS, FA> Query for FromSelect<Q, FS, FA> {
    type SqlType = Q::SqlType;
}

pub trait FromSelectDsl: Sized {
    #[allow(clippy::wrong_self_convention)]
    fn from_select<FS, FA>(self, from_select: FS, from_alias: FA) -> FromSelect<Self, FS, FA> {
        FromSelect {
            select: self,
            from_select,
            from_alias,
        }
    }
}

impl<T> FromSelectDsl for T {}