Diesel chain inner joins

help
1

I have the following situation: I have a database with objects, and tags. They are related in a many-to-many way, that is, each object might be tagged with any number of tags, and tags may be applied to any number of objects. This is modeled with

  • an object table, which has an object_id primary key
  • a tag table, which has a tag_id primary key
  • an object_tag table, which has two columns: object_id and tag_id, both, being foreign keys, and (object_id, tag_id) being the primary key.

I would like the user to be able to filter for objects in my database that have all of the tags the user provides. To do this with a single tag, I would:

  • inner join object and object_tag on object_id (which is implicit, thanks to the joinable! macro)
  • select only the object part of the table

This operation starts with a table whose schema is the same as object, and ends with a table whose schema is the same as object, so I assumed that I could just iterate this operation for every tag that the user has provided.

However, this doesn't work: diesel complains, with very ugly type errors. I assume the issue is that somehow I haven't done things in the right way, and so Diesel doesn't see that applying this operation is supposed to leave the schema unchanged.

So, I have tried another approach (which seems to more less efficient, but idk how the database manager optimizes this kind of things so I wouldn't bet on it):

  • for every tag, I generate a query on object_tag that filters only rows whose tag_id matches the tag
  • for every such query, I then select only the object_id
  • I intersect all of them
    the code is roughly the following:
tags.iter().copied().map(|id_of_tag| {
    use schema::object_tag::dsl::*;
    
    object_tag
        .select(object_id)
        .filter(tag_id.eq(id_of_tag))
}).reduce(|l, r| l.intersect(r))

this doesn't work either, though. I've tried throwing a couple of .into_boxed() to see if that would help, but I still can't get it to work.

How am I supposed to make this work?

2

After further investigation, I have found out that boxed queries only remember certain information in their type. One of them is the where clauses. Hence, if I manage to express my query where the only dynamic part is adding where clauses, it should work.

Indeed, I have found a way to make it work this way: I start with a boxed query over objects, and then I

use schema::objects::dsl::*;

let query = objects.select(Object::as_select()).filter(...).into_boxed();

let query = user_provided_tags
    .iter()
    .copied()
    .map(|id_of_tag| {
        use schema::object_tag::dsl::*;
        
        object_tag
            .select(object_id)
            .filter(tag_id.eq(id_of_tag))
    })
    .fold(query, |l, r| l.filter(id.eq_any(r)));

This will generate a query that looks like

SELECT object_id, ... FROM objects
WHERE ...
AND object_id IN (SELECT object_id FROM object_tag WHERE tag_id = 2)
AND object_id IN (SELECT object_id FROM object_tag WHERE tag_id = 17)
AND object_id IN (SELECT object_id FROM object_tag WHERE tag_id = 5)
...

I'm not sure whether this is an efficient way to express the query, but since my database is rather small (there are less than 10000 rows, all tables considered, and every schema has at most, like, 5 columns), it is ok in practice.

3

I'm not sure whether this is helpful as I don't know Diesel (or ORMs in general) - esp. as you already have a solution that works for you.

Couldn't you create something that would generate

select obj.object_id, ...
from objects obj
where
    ...
    and
    obj.object_id in (
        select ot.object_id
        from object_tag ot
        where
            ot.object_tag in (2, 17, 5, ...)
        group by ot.object_id
        having count (*) = ... number of selected tags...
        // having count (distinct ot.object_tag) = ...
    )

or in the where-clause

...number of selected tags ... = (
        select count (*)
        from object_tag ot
        where
            ot.object_id = ot.object_id
            and
            ot.object_tag in (2, 17, 5, ...)
    )