Reusable & Dynamic Query Pieces

[ahmed-said-jax]

I'm using a sort of "composition" in my database schema to extract fields that are common to many tables. Here's a contrived example schema in an unrelated domain (for funsies):

diesel::table! {
    player (id) {
        id -> Uuid,
        name -> Text,
        nationality -> Text
    }
}

diesel::table! {
    goalkeeper (id) {
        id -> Uuid,
        metadata_id -> Uuid,
        n_saves -> Integer,
    }
}

diesel::table! {
    striker (id) {
        id -> Uuid,
        metadata_id -> Uuid,
        n_goals -> Integer,
    }
}

diesel::joinable!(goalkeeper -> player (metadata_id));
diesel::joinable!(striker -> player (metadata_id));

It's not exactly conventional, but I think my actual use-case merits it (I'm open to changing my schema though). Now, I want to be able to filter either goalkeepers or strikers based on fields specific to those tables, but also common fields, like name or nationality. I've gotten this far:

#[derive(Deserialize, Default, Debug)]
struct MetadataQuery {
    name: Option<String>,
    nationality: Option<String>,
}

impl MetadataQuery {
    fn as_diesel_expression(
        &self,
    ) -> Box<dyn BoxableExpression<player::table, Pg, SqlType = Bool> + '_> {
        use player::{
            dsl::{name, nationality},
            table,
        };

        let mut query: Vec<Box<dyn BoxableExpression<table, Pg, SqlType = Bool>>> = Vec::new();

        if let Some(player_name) = &self.name {
            let ilike_string = format!("%{player_name}%");
            let q = name.ilike(ilike_string);

            query.push(Box::new(q));
        }

        if let Some(player_country) = &self.nationality {
            query.push(Box::new(nationality.eq(player_country)));
        }

        query
            .into_iter()
            .reduce(|q1, q2| Box::new(q1.and(q2)))
            .unwrap()
    }
}

#[derive(Deserialize)]
struct GoalKeeperQuery {
    #[serde(flatten)]
    #[serde(default)]
    metadata_query: MetadataQuery,
    more_saves_than: Option<i32>,
    less_saves_than: Option<i32>,
}
impl GoalKeeperQuery {
    // What's the correct, ergonomic return type here?
    fn as_diesel_expression(&self) {
        use goalkeeper::dsl::n_saves;

        let mut goalie_query: Vec<
            Box<dyn BoxableExpression<goalkeeper::table, Pg, SqlType = Bool>>,
        > = Vec::new();

        if let Some(less_saves_than) = &self.less_saves_than {
            goalie_query.push(Box::new(n_saves.lt(less_saves_than)));
        }

        if let Some(more_saves_than) = &self.more_saves_than {
            goalie_query.push(Box::new(n_saves.gt(more_saves_than)));
        }

        let goalie_query = goalie_query
            .into_iter()
            .reduce(|q1, q2| Box::new(q1.and(q2)))
            .unwrap();

        let metadata_query = self.metadata_query.as_diesel_expression();

        // I want to return one of the following, but it results in a massive return type.
        let query = goalie_query.and(metadata_query);
        let boxed_query = Box::new(query);
    }
}

Now, I know I might use into_boxed, and then call .filter(..) multiple times like so:

let query = player::table.into_boxed();
// Add my filter conditions
return query;

But then I cannot use that result in an inner_join with goalkeeper due to a type error (happy to add it if it's useful, but I'm pretty sure it would just be noise). Any suggestions on how to achieve this behavior?

[ahmed-said-jax]

So it turns out that using trait bounds, I can actually accomplish this:

use diesel::{RunQueryDsl, pg::Pg, prelude::*, sql_types::Bool};
use serde::Deserialize;
use uuid::Uuid;

type BoxedFilterExpression<'a, T> = Box<dyn BoxableExpression<T, Pg, SqlType = Bool> + 'a>;

#[derive(Deserialize, Debug, Default)]
struct MetadataFilter {
    name: Option<String>,
    nationality: Option<String>,
}

impl MetadataFilter {
    fn as_diesel_expression<'a, T: 'a>(&'a self) -> Option<BoxedFilterExpression<'a, T>>
    where
        player::id: SelectableExpression<T>,
        player::name: SelectableExpression<T>,
        player::nationality: SelectableExpression<T>,
    {
        use player::dsl::{name as name_col, nationality as nationality_col};

        let Self { name, nationality } = self;

        if matches!((name, nationality), (None, None)) {
            return None;
        }

        let mut query: Vec<BoxedFilterExpression<T>> = Vec::new();

        if let Some(player_name) = &self.name {
            let ilike_string = format!("%{player_name}%");
            let q = name_col.ilike(ilike_string);

            query.push(Box::new(q));
        }

        if let Some(player_country) = &self.nationality {
            let q = nationality_col.eq(player_country);

            query.push(Box::new(q));
        }

        query.into_iter().reduce(|q1, q2| Box::new(q1.and(q2)))
    }
}

#[derive(Deserialize, Default)]
struct GoalKeeperFilter {
    #[serde(flatten)]
    #[serde(default)]
    metadata_filter: MetadataFilter,
    more_saves_than: Option<i32>,
    less_saves_than: Option<i32>,
}

impl GoalKeeperFilter {
    fn as_diesel_expression<'a, T: 'a>(&'a self) -> Option<BoxedFilterExpression<'a, T>>
    where
        player::id: SelectableExpression<T>,
        player::name: SelectableExpression<T>,
        player::nationality: SelectableExpression<T>,
        goalkeeper::n_saves: SelectableExpression<T>,
    {
        use goalkeeper::dsl::n_saves;

        let Self {
            metadata_filter,
            more_saves_than,
            less_saves_than,
        } = self;

        let metadata_filter = metadata_filter.as_diesel_expression();

        if matches!(
            (&metadata_filter, more_saves_than, less_saves_than),
            (None, None, None)
        ) {
            return None;
        }

        let mut query: Vec<Box<dyn BoxableExpression<T, Pg, SqlType = Bool>>> = Vec::new();

        if let Some(metadata_filter) = metadata_filter {
            query.push(metadata_filter);
        }

        if let Some(less_saves_than) = &self.less_saves_than {
            query.push(Box::new(n_saves.lt(less_saves_than)));
        }

        if let Some(more_saves_than) = &self.more_saves_than {
            query.push(Box::new(n_saves.gt(more_saves_than)));
        }

        query.into_iter().reduce(|q1, q2| Box::new(q1.and(q2)))
    }
}

This is not bad, but there are a couple problems I'd like to improve:

1. This is a fair amount of boilerplate. It doesn't seem like there's a way that I can consolidate my trait bounds so as to reuse them in a composable manner. That's not the worst thing, but it gets a bit cumbersome with the number of fields I'm working with.
2. There's a lot of heap allocations. I imagine that in Python or something, we'd end up with the same situation, so I'm not that mad about this either. However, in the worst case scenario, we have 2f - 1 calls to Box::new, where f is the total number of fields we're filtering on. In my use case, I might end up with something around 20-30 heap allocations per query. That's not probably not noticeable, but I have not tested this intution.