diesel.rs use select max with other arguments

Question

i have implemented the mysql query in rust, but i select causes an error. This is the error the trait `diesel::expression::MixedAggregates<diesel::expression::is_aggregate::Yes>` is not implemented for `diesel::expression::is_aggregate::No Whats the problem and how can i fix this?

let sub = progress::table
  .filter(progress::user.eq(1))
  .group_by(progress:: otherid)
  .select((diesel::dsl::max(progress::updated), progress::otherid, progress::onemoreid));

SELECT otherid, onemoreid, MAX(updated) AS newest_updated
    FROM progress
    WHERE user = 1
    GROUP BY otherid

EDIT: the error doesnt occur when i dont have progress::onemoreid in select.

Can't verify at the moment, but try moving `.select()` *after* `.group_by()`. That is how it is shown to use `GROUP BY` in the [documentation](https://docs.rs/diesel/latest/diesel/prelude/trait.QueryDsl.html#method.group_by) and in general when using Diesel the `SELECT` should usually be the *last* step before executing the query (contrary to normal SQL). — kmdreko, Apr 13 '23 at 15:41
it fixed it, but when i try to show another varible the error occurs again — filif96770, Apr 13 '23 at 16:22
If that query works with MySQL, it may be a difference between how it and Diesel handle SELECTs on GROUP BYs. From the documentation above: *"[A]ny column appearing in a group by clause is considered to be aggregated... Select clauses cannot mix aggregated and non aggregated expressions."*. So you may need `.group_by((progress::otherid, progress::onemoreid))` or an aggregation for `onemoreid` in the SELECT for it to work with Diesel. — kmdreko, Apr 13 '23 at 16:34

score 0 · Answer 1 · answered Apr 14 '23 at 06:12

The relevant part from diesel documentation is the following paragraph:

Diesel follows postgresql’s group by semantic, this means any column appearing in a group by clause is considered to be aggregated. If a primary key is part of the group by clause every column from the corresponding table is considerd to be aggregated. Select clauses cannot mix aggregated and non aggregated expressions.

This restriction is in place because otherwise your query could return non-deterministic results. After all, what value of onemoreid should be selected when the query produces multiple possible values.

MySQL allows to write such queries, but they can return non-deterministic results there. That means that just some value is returned. Possibly even different values for the execution of the same query using the same data.

diesel.rs use select max with other arguments

1 Answers1