I currently have this working diesel code:
let recent_downloads = sql::<Nullable<BigInt>>("SUM(crate_downloads.downloads)");
let most_recently_downloaded = crates
.left_join(
crate_downloads::table.on(id.eq(crate_downloads::crate_id)
.and(crate_downloads::date.gt(date(now - 90.days())))),
)
.group_by(id)
.order(recent_downloads.desc().nulls_last())
.limit(10)
.select(ALL_COLUMNS)
.load::<Crate>(&*conn)?;I want to move the group_by, order, and limit within a subselect that I join on instead of joining on crate_downloads::table, to ultimately generate SQL that functions like this:
select crates.* from crates join (
select crate_downloads.crate_id, sum(crate_downloads.downloads)
from crate_downloads
where crate_downloads.date > date(current_timestamp - interval '90 days')
group by crate_downloads.crate_id
order by sum(crate_downloads.downloads) desc nulls last
limit 10
) cd on crates.id = cd.crate_id;The first step I took was removing a bunch of the diesel code, until I got to this, which compiles:
let most_recently_downloaded = crates
.left_join(
(
crate_downloads::table
).on(id.eq(crate_downloads::crate_id)),
)
.select(ALL_COLUMNS)
.load::<Crate>(&*conn)?;Then I started adding in what I want to change and I got to this, which does not compile:
let most_recently_downloaded = crates
.left_join(
(
crate_downloads::table
.filter(crate_downloads::date.gt(date(now - 90.days())))
.select(crate_downloads::crate_id)
).on(id.eq(crate_downloads::crate_id)),
)
.select(ALL_COLUMNS)
.load::<Crate>(&*conn)?;The error I get is:
error[E0599]: no method named `on` found for type `diesel::query_builder::SelectStatement<schema::crate_downloads::table, diesel::query_builder::select_clause::SelectClause<schema::crate_downloads::columns::crate_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Gt<schema::crate_downloads::columns::date, diesel::dsl::date_t<diesel::expression::ops::Sub<diesel::dsl::now, diesel::expression::bound::Bound<diesel::sql_types::Interval, diesel::data_types::PgInterval>>>>>>` in the current scope
--> src/controllers/krate/metadata.rs:67:15
|
67 | ).on(id.eq(crate_downloads::crate_id)),
| ^^
|
= note: the method `on` exists but the following trait bounds were not satisfied:
`diesel::query_builder::SelectStatement<schema::crate_downloads::table, diesel::query_builder::select_clause::SelectClause<schema::crate_downloads::columns::crate_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Gt<schema::crate_downloads::columns::date, diesel::dsl::date_t<diesel::expression::ops::Sub<diesel::dsl::now, diesel::expression::bound::Bound<diesel::sql_types::Interval, diesel::data_types::PgInterval>>>>>> : diesel::JoinOnDsl`
`&diesel::query_builder::SelectStatement<schema::crate_downloads::table, diesel::query_builder::select_clause::SelectClause<schema::crate_downloads::columns::crate_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Gt<schema::crate_downloads::columns::date, diesel::dsl::date_t<diesel::expression::ops::Sub<diesel::dsl::now, diesel::expression::bound::Bound<diesel::sql_types::Interval, diesel::data_types::PgInterval>>>>>> : diesel::JoinOnDsl`
`&mut diesel::query_builder::SelectStatement<schema::crate_downloads::table, diesel::query_builder::select_clause::SelectClause<schema::crate_downloads::columns::crate_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Gt<schema::crate_downloads::columns::date, diesel::dsl::date_t<diesel::expression::ops::Sub<diesel::dsl::now, diesel::expression::bound::Bound<diesel::sql_types::Interval, diesel::data_types::PgInterval>>>>>> : diesel::JoinOnDsl`