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
?
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)?;