Help translating SQL query to diesel

[mujx]

I have some trouble translating the following SQL query into diesel (mostly with the sub query). Basically I have a table named Elements(id, type, ordering) and I want to return the rows with the maximum ordering for some of the types (in the example the types 2 and 8).

SELECT * FROM 
(
   SELECT type, max(ordering) AS max_order 
   FROM Elements WHERE type = 2 or type = 8 GROUP BY type
) AS B 
INNER JOIN Elements AS A 
ON A.type = B.type AND A.ordering = B.max_order;

On a side note it would really helpful if there were more complex examples on the docs or some recipes for common queries.

[mujx]

Ok, I think I found a solution for the inner query, but I am not so sure how to approach the second step.

Currenlty it fails with
no method name 'get_results' found for type 'diesel::query_builder::SelectStatement<..

let max_ordering = max(elems::ordering).aliased("max_ordering");

let grouped = elems::table
    .with(max_ordering)
    .select((elems::type, max_ordering))
    .filter(elems::type.eq_any(vec![ElemType::Two, ElemType::Eight]))  
    .groub_by(elems::type)
    .aliased("grouped");

let results: Vec<Elements> = elems::table
    .with(grouped)
    .filter(grouped.eq(elems::table.select((elems::type, elems::ordering))))
    .get_results(connection)?;

[sgrif]

aliased and with are only for CTEs, not your usage. The short version is that you will need to use the sql function until #3 is fixed.

I do want to expand on the aliases thing though. We don't have any mechanism for arbitrary aliases in SQL, as it's not necessary. I am on my phone on roaming internet so apologies if the code is poorly formatted or has typos. For example, you could do:

let max_ordering = max(ordering);
elems.select(max_ordering)
    .group_by(tpe)
    .order(max_ordering)

Which will generate the SQL

SELECT MAX(ordering) FROM elems GROUP BY type ORDER BY MAX(ordering)

You may say "but that's less efficient!". It's not, though. If you look at the query plan it's identical to the aliased form. Query planners are smart.