How to implement nested filters in diesel?

Question

I'm quite new to Rust and Diesel. I'm now trying to implement Diesel filtering for query like this:

-- @param1 is duration in years
-- @param2 is duration in months

SELECT columns FROM a
WHERE 
(...dynamic AND clauses)
AND (((a.min_unit = "Years") AND (a.min_duration <= @param1))
  OR ((a.min_unit = "Months") AND (a.min_duration <= @param2)))
(...dynamic AND clauses)

After some searches in docs and around the web, I still couldn't find how to do this. My closest guess is:

let param1 = ...;
let param2 = ...;

let mut query = a::table.select(a::all_columns).into_boxed();
// dynamic clauses: query = query.filter(...) ...
query = query.filter(a::min_unit.eq(Some("Years")).and(a::min_duration.le(Some(param1))))
  .or_filter(a::min_unit.eq(Some("Months")).and(a::min_duration.le(Some(param2))));
// dynamic clauses: query = query.filter(...) ...

let results = a::table.load<A>(&*conn);

Anyone has idea?

Thanks!

Answer 1

Well, here is my latest try:

let min_months_predicate = a::min_unit.eq(Some("Months"))
    .and(a::min_duration.le(Some(param1)));
let min_years_predicate = a::min_unit.eq(Some("Years"))
    .and(a::min_duration.le(Some(param1)));

query = query.filter(min_months_predicate.or(min_years_predicate))
    .filter(a::max_duration.ge(Some(param2)));

debug!("TEST QUERY: {:?}", debug_query(&query));

which yields this query:

"SELECT \"a\".\"id\", \"a\".\"code\", .... WHERE (\"a\".\"min_unit\" = $1 AND \"a\".\"min_duration\" <= $2 OR \"a\".\"min_unit\" = $3 AND \"a\".\"min_duration\" <= $4) AND \"a\".\"max_duration\" >= $5"

And when I use sql EXPLAIN with this query, I got this clause:

  Filter: ((max_duration >= 60) AND (((min_unit = 'Months'::text) AND (min_duration <= 3)) OR ((min_unit = 'Years'::text) AND (min_duration <= 5))))

which seems correct.