Dynamic filtering and dynamic queries, example-based tutorials

[andreievg]

I've previous made tutorial for dynamic filtering using diesel-rs and now I've added another one for dynamic queries (more like extending diesel to allow for more sophisticated queries), wanted to share for others, I hope you'll find them useful.

https://github.com/andreievg/diesel-rs-dynamic-queries

https://github.com/andreievg/diesel-rs-dynamic-filters

[weiznich]

Thanks for writing these ❤️

One thing that I would like to share in this context is that using a custom extension usually does not mean that you have to write all the inner SQL on your own, even with CTE.

For simplicity lets assume a CTE like:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

that would allow you to write something like that using diesel + #[auto_type] to reuse some query parts.

// from schema.rs
table! {
    orders {
         id -> Integer,
         amount -> Integer,
         region -> Text,
         product -> Text,
    }
}

struct As<T>(t: T, &str);
// impl QueryFragment to have a as query node

#[diesel::dsl::auto_type]
fn regional_sales() -> _ {
    orders::table.group_by(orders::region).select((orders::region, As(diesel::dsl::avg(orders::amount), "total_sales"))
}

// fake table for regional sales defined "somewhere"
table! {
    regional_sales(region) {
        region -> Text,
        total_sales -> Integer,
    }
}

#[diesel::dsl::auto_type]
fn top_regions() -> _{
     let sq = regional_sales::select(dsl::sum(regional_sales::total_sales)/10);
    regional_sales::table.select(regional_sales::region).filter(regional_sales::total_sales.gt(sq))
}

// another fake table for top_regions defined somewhere
table! {
    top_regions () {
         region -> Text,
    }
}
// you might need some calls to `diesel::allow_tables_to_appear_in_same_query!` with the fake table and the real table

#[diesel::dsl::auto_type]
fn final_query() -> _ {
    orders::table
        .group_by((orders::region, orders::product))
        .select((orders::product, dsl::sum(orders::quantity), dsl::sum(orders::amount))
        .filter(orders::region.eq_any(top_regions::table.select(top_regions::region)))
       
}

struct MySalesCte {
    // these types are generated by auto_type
    // also use the functions above to construct values 
    // for these fields
    regional_sales_sq: regional_sales,
    top_regions_sq: top_regions,
    final_query_sq: final_query,
}

impl QueryFragment<Pg> for MySalesCte {
    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, Pg>) -> QueryResult<()> {
        out.push("WITH regional_sales AS (");
        // just walk the subquery here
        self.regional_sales_sq.walk_ast(out.reborrow())?;
        out.push_sql("), top_regions AS (");
       // again walk the subquery
       self.top_regions_sq.walk_ast(out.reborrow())?;
       out.push_sql(")");
       // again walk the subquery
       self.final_query_sq.walk_ast(out.reborrow())?;
       Ok(())
    }
}

// also for implementing `Query` you likely can forward that as well to the `final_query` type