Creating Diesel.rs queries with a dynamic number of .and()'s

Question

While playing with Diesel, I got stuck writing a function which takes an vector of Strings as input and does the following:

1. Combine all Strings to a large query
2. run the query on the Connection
3. process the result
4. return a Vec

If I construct the query in one step, it works just fine:

fn get_books(authors: Vec<String>, connection: SqliteConnection) {
    use schema::ebook::dsl::*;

    let inner = author
        .like(format!("%{}%", authors[0]))
        .and(author.like(format!("%{}%", authors[1])))
        .and(author.like(format!("%{}%", authors[2])));

    ebook
        .filter(inner)
        .load::<Ebook>(&connection)
        .expect("Error loading ebook");
}

If I try to generate the query in more steps (needed in order to work with the variable length of the input vector) I can't get it to compile:

fn get_books(authors: Vec<String>, connection: SqliteConnection) {
    use schema::ebook::dsl::*;

    let mut inner = author
        .like(format!("%{}%", authors[0]))
        .and(author.like(format!("%{}%", authors[1]))); // <1>

    inner = inner.and(author.like(format!("%{}%", authors[2]))); // <2>

    ebook
        .filter(inner)
        .load::<Ebook>(&connection)
        .expect("Error loading ebook");
}

This generates the following error:

inner = inner.and(author.like(format!("%{}%",authors[2])));
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected struct `diesel::expression::operators::Like`, found struct `diesel::expression::operators::And`

I don't understand why Rust expects a Like and not an And. The function an the line line marked <1> returns an And and therefore an And is stored in inner.

What am I missing? Why does the first bit of code compile and the second won't? What is the right way to generate this kind of query?

Answer 1

The first thing you need to do is look at the complete error message:

error[E0308]: mismatched types
  --> src/main.rs:23:13
   |
23 |     inner = inner.and(author.like(format!("%{}%", authors[2])));//<2>
   |             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected struct `diesel::expression::operators::Like`, found struct `diesel::expression::operators::And`
   |
   = note: expected type `diesel::expression::operators::And<diesel::expression::operators::Like<_, _>, _>`
              found type `diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Like<_, _>, diesel::expression::operators::Like<schema::ebook::columns::author, diesel::expression::bound::Bound<diesel::sql_types::Text, std::string::String>>>, _>`

It's long, but that's because it's fully qualified. Lets shorten the last part a bit:

expected type `And<Like<_, _>, _>`
   found type `And<And<Like<_, _>, Like<author, Bound<Text, String>>>, _>`

If you review the documentation for and, you'll see that every call to and consumes the receiver and returns a brand new type — And:

fn and<T: AsExpression<Bool>>(self, other: T) -> And<Self, T::Expression>

This is the core of Diesel's ability to generate strongly-typed SQL expressions with no run-time overhead. All of the work is delegated to the type system. In fact, the creator of Diesel has an entire talk where he shows how far Diesel pushes the type system and what benefits it has.

Turning back to your question, it's impossible to store an And<_, _> in the same location as an And<And<_, _>, _> because they will have different sizes and are in fact different types. At the root, this is the same as trying to store an integer in a boolean.

In fact, there's no way to know what concrete type you need because you don't even know how many conditions you will have — it depends on the size of the vector.

In this case, we have to give up static dispatch and move to dynamic dispatch via a trait object. Diesel has a specific trait for this case (which also has good examples): BoxableExpression.

The remaining piece is to convert your authors to like expressions and combine them. We need a base case, however, for when authors is empty. We construct a trivially true statement (author = author) for that.

#[macro_use]
extern crate diesel;

use diesel::SqliteConnection;
use diesel::prelude::*;
use diesel::sql_types::Bool;

mod schema {
    table! {
        ebook (id) {
            id -> Int4,
            author -> Text,
        }
    }
}

fn get_books(authors: Vec<String>, connection: SqliteConnection) {
    use schema::ebook::dsl::*;

    let always_true = Box::new(author.eq(author));
    let query: Box<BoxableExpression<schema::ebook::table, _, SqlType = Bool>> = authors
        .into_iter()
        .map(|name| author.like(format!("%{}%", name)))
        .fold(always_true, |query, item| {
            Box::new(query.and(item))
        });

    ebook
        .filter(query)
        .load::<(i32, String)>(&connection)
        .expect("Error loading ebook");
}

fn main() {}

---

I also wouldn't be surprised if there wasn't a better SQL way of doing this. It appears that PostgreSQL has the WHERE col LIKE ANY( subselect ) and WHERE col LIKE ALL( subselect ) forms, for example.