Struct for results with fields from sql functions

[Hovestar]

I'd like to write a query(postgres) that goes like
SELECT DATE_BIN('10 minutes',time,'2001-01-01'), SUM(field) FROM table GROUP BY DATE_BIN('10 minutes',time,'2001-01-01') ORDER BY DATE_BIN('10 minutes',time,'2001-01-01')
But I'm having a hard time finding examples of how I can define a struct to do that. I understand I can make a raw query, but it would be nice to have it all in a struct.

[weiznich]

Generally speaking its possible to express this query with diesel. It's hard to help you with details without knowing what you already tried or what exactly you understand as "having it all in a struct". You might want to add details to your question to elaborate on the following points:

• Code you already have
• Errors you hit (as emitted by the compiler)
• Information about your environment (diesel, rust, postgres, operating system)

[Hovestar]

Totally, I'm hoping to get something like this from this type of table:
CREATE TABLE table(time_of_data timestamptz NOT NULL, data INT4 NOT NULL);

I'd like to get a query that does something like this:
SELECT DATE_BIN('10 minutes',time_of_data ,'2001-01-01'), SUM(data ) FROM table GROUP BY DATE_BIN('10 minutes',time,'2001-01-01') ORDER BY DATE_BIN('10 minutes',time_of_data ,'2001-01-01')

In rust I'd want a struct like this:

#[derive(Queryable, Selectable)]
#[diesel(table_name = crate::schema::analytics_history)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct FilteredData{
   #[diesel(select_expression=sql("date_bin('10 minutes',time_of_data,'2001-01-01') as time"))]
   time_of_data : DateTime<Utc>, // this is the result of DATE_BIN
   #[diesel(select_expression=sql("sum(data)")]
   data: i32, // this is the sum
}

So the code would be:

table::dsl::table
   .select(FilteredData::as_select())
   .get_results(&mut conn)

لِ

[weiznich]

Conceptually that should work this way, with the restriction that this only constructs the select clause of the query. That's by design and also implied by the name. You need to provide the other parts on your own. If you always need the same base query you can easily factor that out as shown in the Composing applications guide on the diesel web page.

Now I have not tested the code you've provided and you did not provide an error message so it might be that you need to provide some additional type annotation as shown in the docs for #[derive(Selectable)] and the sql function.