sqlrustormrust-diesel

How to use two different expressions in one filter in Diesel ORM?


I am trying to run a very simple filter on a joined table using Diesel ORM.

The database has a simple many-to-many relationship set up using an associative table:

CREATE TABLE items (
    id INT NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE item_lists (
    id INT NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY(id)
);

/* Associative table that links lists to their contents. */ 
CREATE TABLE list_items (
    id INT NOT NULL,
    list_id INT NOT NULL,
    item_id INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(list_id) REFERENCES item_lists(id),
    FOREIGN KEY(item_id) REFERENCES items(id)
);

I need to write a function that will return all items in a specific list, or in no list. Given an id or NULL represented as Option<i32>, the function needs to: if it's None, return items that aren't in any list; if it's Some(id), return items in that list. It looks somewhat like this so far:

pub type ItemInList = (Item, Option<(ListItem, ItemList)>);

pub fn get_items_in_list(
    db: &mut SqliteConnection,
    list_id: Option<i32>,
) -> Result<Vec<ItemInList>> {
    let result: Vec<ItemInList> = items::table
        .left_join(list_items::table.inner_join(item_lists::table))
        .filter(list_id
            .map(|id| item_lists::id.eq(id))
            .unwrap_or_else(|| item_lists::id.is_null()))
        .select(ItemInList::as_select())
        .load(db)?;

    Ok(result)
}

This code does not compile as the return types of eq and is_null, which are dsl::Eq and dsl::IsNull) respectively, aren't the same, so the filter call can't be compiled. I don't understand how the ORM expects me to type this otherwise, it seems that it should be trivial to create a filter call equivalent to WHERE item_lists.id = in_id OR (item_lists.id IS NULL AND in_id IS NULL).

Is there a type that represents "any filterable expression"? How do I make this work without separate duplicate functions (or two separate implementations in a match statement) for the None and Some variants of list_id?


Solution

  • Without an SSCCE I can't validate this, but you can use boxed queries to build up a query programmatically like:

    
    let mut qry = items::table
        .left_join(list_items::table.inner_join(item_lists::table))
        .select(ItemInList::as_select())
        .into_boxed();
    
    if let Some(list_id) = list_id {
        qry = qry.filter(item_lists::id.eq(list_id));
    } else {
        qry = qry.filter(item_lists::id.is_null());
    }
    
    let result: Vec<ItemInList> = qry.load(db)?;