Examples of dynamic filtering with diesel-rs

Dynamic Filtering ?

Quite often we want to build filters at runtime (i.e. from Graphql input), this requires a structure of defining dynamic filter shape and a mechanism to convert it to database queries.

Motivation

Anything dynamic in diesel-rs can be quite daunting at the beginning, thus I wanted to show examples of dynamic filtering with diesel-rs via evolutionary guide (from basic to complex).

Content

This tutorial is meant to be consumed in steps, each step has a branch and each step evolves from the previous step (git diff helps with understanding the progression). These topics are covered:

• Basic condition boxing
• Generic type filters
• And/Or and nesting/grouping condition
• Joins
• Macros to help with extendability
• Inner queries
• Complex joins

Generics and Types

I tried to find a balance between the use of generic and complexity, and in some cases opted in for macros or copy paste approach. When working in a team, readability and simplicity trumps most other subjective characteristics of code, and I think complex use of generics undermines that characteristic.

Basic condition boxing

We start with basic-generic-filters branch and this file.

Basically we get to below data structure:

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Lines 15 to 20 in 74283f9

	enum Condition {
	Equal(i32),
	NotEqual(i32),
	GreaterThen(i32),
	LowerThen(i32),
	}

That can be used in the following way:

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Lines 116 to 127 in 74283f9

	let condition =
	create_filter(vec![Condition::GreaterThen(1), Condition::LowerThen(3)]).unwrap();
	let result = vec!["2".to_string()];
	assert_eq!(
	result,
	test::dsl::test
	.filter(condition)
	.select(test::dsl::id)
	.load::<String>(&mut connection)
	.unwrap()
	);

Generic type filters

The next step, in generic-field-filters branch and as per this diff, generic type filters were added, to be able to filter more then one field.

This allowed this structure:

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Lines 17 to 23 in d715883

	enum NumberFilter {
	Equal(i32),
	NotEqual(i32),
	GreaterThen(i32),
	LowerThen(i32),
	}
	// Filters for "strings"

To be used in the following way:

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Lines 158 to 162 in d715883

	let condition = create_filter(vec![
	Condition::number_field(NumberFilter::GreaterThen(1)),
	Condition::text_field(StringFilter::Like("%4%".to_string())),
	Condition::bool_field(true),
	])

And/Or and nesting/grouping condition

This is where it starts to get interesting, in and/or branch as per this diff we are able to use dynamic and and or statement with unlimited number of nesting and fine grained control over the grouping.

The tests have a good example of this, basically we get:

bool_field = true or (bool_field = true and bool_field = false) === true:

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Lines 217 to 224 in b8591aa

	Condition::number_field(NumberFilter::Equal(5)),
	Condition::Or(vec![
	Condition::bool_field(true),
	Condition::And(vec![
	Condition::bool_field(true),
	Condition::bool_field(false),
	]),
	]),

(bool_field = true or bool_field = true) and bool_field = false === false:

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Lines 240 to 247 in b8591aa

	Condition::number_field(NumberFilter::Equal(5)),
	Condition::And(vec![
	Condition::Or(vec![
	Condition::bool_field(true),
	Condition::bool_field(true),
	]),
	Condition::bool_field(false),
	]),

Joins

The next step was to show how we can build dynamic conditions for joined tables, in joins branch as per this diff. You will note in the following statement, BoxedCondition now returns Nullable bool, and .nullable() needs to be added to every single field, this doesn't affect the condition, and macros will help us with the syntax.

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Line 64 in babe4f0

type BoxedCondition = Box<dyn BoxableExpression<Source, Sqlite, SqlType = Nullable<Bool>>>;

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Line 74 in babe4f0

Box::new(test::dsl::number_field.eq(value).nullable())

An example of more complex joins will be shown in later section

Macros

It should be easy to extend existing condition and add filtering functionality to new tables, thus helper were added in macros branch and this diff.

Now new filter can be added by one line addition of a field in conditions enum and a case in match:

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Line 40 in 9c938ae

bool_field(BooleanFilter),

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Line 55 in 9c938ae

Condition::bool_field(value) => boolean_filter!(value, test::dsl::bool_field),

In addition, is_null was added to boolean filter type to show how we can enforce that filter:

diesel-rs-dynamic-filters/src/dynamic_filters.rs

Line 327 in 9c938ae

Condition::double_field(NumberFilter::IsNull),

Even with macros there seems to be a bit of bloat in code and to add dynamic filtering functionality to a new table we would need to add Condition enum, Impl of that Condition enum and create_filter method, it would still be just copy paster operation, I thought that was a good compromise for readability, since a macro to auto generate this method or to make it a method with generics would add a bit of complexity.

Inner query

Sometimes you may want to re-use existing queries and filters as a condition elsewhere. The inner-query branch as per this diff, makes this possible by also creating a boxed query.

You would have seen the use of diesel type helpers already, here the use is extended to create boxed query with filter, that is later used as a condition in another table.

diesel-rs-dynamic-filters/src/inner_statement/bike.rs

Lines 77 to 84 in 776eca2

	pub(super) fn create_filtered_query(conditions: Vec<Condition>) -> BoxedQuery {
	let boxed_query = bike::dsl::bike.left_join(color::dsl::color).into_boxed();
	match create_filter(conditions, AndOr::And) {
	Some(boxed_conditions) => boxed_query.filter(boxed_conditions),
	None => boxed_query,
	}
	}

diesel-rs-dynamic-filters/src/inner_statement/person.rs

Lines 45 to 53 in 776eca2

	Condition::bike(conditions) => {
	// Inner statement, reusing conditions defined in bike
	let inner_statement = bike::create_filtered_query(conditions);
	Box::new(
	person::dsl::id
	.eq_any(inner_statement.select(bike::bike::dsl::owner_id))
	.nullable(),
	)
	}

That results in being able to:

diesel-rs-dynamic-filters/src/inner_statement/mod.rs

Lines 78 to 80 in 776eca2

	let condition = vec![Condition::bike(vec![bike::Condition::color(
	StringFilter::In(vec!["orange".to_string(), "purple".to_string()]),
	)])];

This example also shows that macros make extension of field type filters quite easy

diesel-rs-dynamic-filters/src/lib.rs

Line 30 in 776eca2

In(Vec<String>),

diesel-rs-dynamic-filters/src/lib.rs

Line 39 in 776eca2

StringFilter::In(value) => Box::new($dsl_field.eq_any(value).nullable()),

TODO is there a way to use one 'source' (ConditionSource and QuerySource)

Complex joins

One of the harder things I found with diesel-rs types was construction boxed join types, especially when join has ON statements as showcase in complex-joins branch and this diff.

You can extend this example by adding road_on_bike_ride join table, rather than road_id on bike_ride table (this will allow multiple roads to be attached to a bike_ride and a good exercise for diesel-rs query boxing)

Summary

I hope you found this tutorial useful, you can create an issue if you need some clarification or found an error, etc..

(For anyone that's interested, the original trigger for writing this tutorial came from a more demanding filtering requirement from omSupply project during a central server synchronization research done in one of our monthly RnD days)