Proper way to do many-to-many?

[duckfromdiscord]

I'm basically asking for something that would be in the relations guide, except getting book IDs for each book that was created by only a set of authors, given a list of author IDs. The books returned would only be books created by all of those authors at once, with no other authors not in that list. I understand it would probably involve a join, and I was able to get this to work in normal SQL.

[duckfromdiscord]

Basically my query looks like this:

SELECT b.book_id FROM books b
JOIN book_authors ba ON b.book_id = ba.book_id
WHERE (some kind of search)
GROUP BY b.book_id
HAVING COUNT(DISTINCT ba.book_author) = (SELECT COUNT(DISTINCT book_author)
    FROM book_authors
    WHERE book_id = b.book_id
        AND book_author IN ({each book author id})
     AND COUNT (DISTINCT ba.book_author) = {author count};

and I do not understand from the docs/examples how to translate this to diesel.

[weiznich]

With which part of their query do you have problems? Which code have you already written? Did you encounter any errors?

[duckfromdiscord]

I understand that the query would start off something like this:

Book::table()
    .inner_join(BookAuthor::table())
    .filter(some kind of search)
    .group_by(schema::books::book_id)
    .having(count_distinct(schema::book_authors::author_id).eq(select(count_distinct(schema::book_authors::author_id)).filter(...))

but that is as far as I got because that count_distinct function says something like

Cannot select `book_authors::columns::author_id` from `NoFromClause`
`book_authors::columns::author_id` is no valid selection for `NoFromClause`

plus a massive error message from the having function.

the trait bound `SelectStatement<NoFromClause, diesel::query_builder::select_clause::SelectClause<CountDistinct<diesel::sql_types::Integer, book_authors::columns::book_id>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<book_authors::columns::book_id, schema::books::columns::book_id>>>>: AppearsOnTable<JoinOn<query_source::joins::Join<schema::books::table, book_authors::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<book_authors::columns::book_id>, NullableExpression<schema::books::columns::book_id>>>>>` is not satisfied

I tried to modify the error messages and code to fit the example in the guide.

[weiznich]

I'm currently on mobile, so I cannot easily test this on my own.

What I can say from looking at the query you wrote is:

• That's likely already a goof starting point
• Your SQL query uses aliases, your DSL query don't. You likely need these aliases to correctly refer to the right table instance. Have a look at diesel::alias! for that.
• For the sub query you seem to use diesel::select which constructs a select query without from clause, therefore the first error message. You want to use a normal query construct there as well.
• You likely need to call .single_value() on the subquery to turn into an expression that can be used as right hand side of .eq()
• Finally subqueries are assumed to be nullable you possibly need to call .nullable() before calling .eq() with the subquery

[duckfromdiscord]

let authors: Vec<i32> = vec![ /* author ids */ ];
Book::table()
                    .select(schema::books::book_id)
                    .inner_join(BookAuthor::table())
                    .filter(
                        /* ... */
                    )
                    .group_by(schema::books::book_id)
                    .having(
                        count_distinct(schema::book_authors::book_id)
                        .eq(count_distinct(schema::book_authors::book_id))
                    )
                    .filter(
                        schema::book_authors::book_id.eq(schema::books::book_id)
                        .and(schema::book_authors::author_id.eq_any(authors))
                        .and(count_distinct(schema::book_authors::author_id).eq(authors.len()))
                    )
                    .first::<i32>(conn)

The last filter says:

the trait bound `usize: ValidGrouping<()>` is not satisfied

The last count_distinct says:

the trait bound `usize: diesel::Expression` is not satisfied

And conn says:

the trait bound `usize: AppearsOnTable<JoinOn<query_source::joins::Join<schema::books::table, book_authors::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<book_authors::columns::book_id>, NullableExpression<schema::books::columns::book_id>>>>>` is not satisfied

[duckfromdiscord]

I think the last filter belongs inside the eq two lines above it but if I put it there it says

the method `filter` exists for struct `CountDistinct<Integer, author_id>`, but its trait bounds were not satisfied
the following trait bounds were not satisfied:
`CountDistinct<diesel::sql_types::Integer, book_authors::columns::author_id>: Table`
which is required by `CountDistinct<diesel::sql_types::Integer, book_authors::columns::author_id>: diesel::QueryDsl`

[weiznich]

It's hard to answer that without knowing the complete error messages from rustc.

I would guess that the second error message indicates that authors.len() just has the wrong type, likely usize vs i64. There should be a second part of that message which mentions AsExpression and the sql side type.

The other error messages, especially the last one look strange. You don't call filter on count_distinct according to the provided code, so maybe something is different in your real code?

[duckfromdiscord]

Album::table()
    .select(schema::albums::album_id)
    .inner_join(AlbumArtist::table())
    .filter(
        /* ... */
    )
    .group_by(schema::albums::album_id)
    .having(
        count_distinct(schema::album_artists::artist_id)
        .eq( //here
            AlbumArtist::table()
                .filter(
                    schema::album_artists::album_id.eq(schema::albums::album_id)
                    .and(schema::album_artists::artist_id.eq_any(artists))
                )
                .distinct()
                .count()
        )
        /* here */ .and(count_distinct(schema::album_artists::artist_id).eq(artists.len() as i64))  

This is my real code now, which I am using in my app. It looks like i64 fixed the len problem hopefully. The two errors occur at the eq:

the trait bound `SelectStatement<FromClause<album_artists::table>, diesel::query_builder::select_clause::SelectClause<CountStar>, diesel::query_builder::distinct_clause::DistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::Eq<album_artists::columns::album_id, schema::albums::columns::album_id>>, expression::grouped::Grouped<diesel::expression::array_comparison::In<album_artists::columns::artist_id, Many<diesel::sql_types::Integer, i32>>>>>>>: diesel::Expression` is not satisfied

and and:

the method `and` exists for struct `Grouped<Eq<CountDistinct<Integer, artist_id>, SelectStatement<FromClause<table>, ..., ..., ...>>>`, but its trait bounds were not satisfied
the full type name has been written to '....txt'
consider using `--verbose` to print the full type name to the console
the following trait bounds were not satisfied:
`expression::grouped::Grouped<expression::operators::Eq<CountDistinct<diesel::sql_types::Integer, album_artists::columns::artist_id>, SelectStatement<FromClause<album_artists::table>, diesel::query_builder::select_clause::SelectClause<CountStar>, diesel::query_builder::distinct_clause::DistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::Eq<album_artists::columns::album_id, schema::albums::columns::album_id>>, expression::grouped::Grouped<diesel::expression::array_comparison::In<album_artists::columns::artist_id, Many<diesel::sql_types::Integer, i32>>>>>>>>>: diesel::Expression`
which is required by `expression::grouped::Grouped<expression::operators::Eq<CountDistinct<diesel::sql_types::Integer, album_artists::columns::artist_id>, SelectStatement<FromClause<album_artists::table>, diesel::query_builder::select_clause::SelectClause<CountStar>, diesel::query_builder::distinct_clause::DistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::Eq<album_artists::columns::album_id, schema::albums::columns::album_id>>, expression::grouped::Grouped<diesel::expression::array_comparison::In<album_artists::columns::artist_id, Many<diesel::sql_types::Integer, i32>>>>>>>>>: diesel::BoolExpressionMethods`

.