Querying from and joining sub-select queries

[oeed]

One query I need requires querying through a lot of data with joins, all of which are aggregated before joining. The only way to get good performance with this I've found (in Postgres) is aggregating in a sub-select, the query and join against that.

SELECT
  "grouped_users"."company_id",
  "grouped_users"."user_count",
  "grouped_campaign"."campaign_count"
FROM
  (
    SELECT
      "user"."company_id" AS "company_id",
      sum("user"."id") AS "user_count"
    FROM "user"
    GROUP BY "user"."company_id"
  ) "grouped_users"
INNER JOIN
  (
    SELECT
      "campaign"."advertiser_id" AS "company_id",
      sum("campaign"."id") AS "campaign_count"
    FROM "campaign"
    GROUP BY "campaign"."id"
  ) "grouped_campaign"
  ON ("grouped_campaign"."company_id" = "grouped_users"."company_id")

In many cases, you could do this with a view. But in my case, the values selected for each column, which columns are used for grouping is dynamic, so a view isn't feasible. So, instead, I created this approach. Declare with a macro the columns/selection of the sub-select, create the sub-select, then query it as the 'from' or joined source.

This approach is heavily tailored to what I need, but I thought it worthwhile sharing in case it inspires similar functionality in core diesel. It's probably also not the best approach.

First, declare your sub-select columns:

select_query_source! {
  grouped_users (user_schema) {
    company_id -> user_schema::company_id,
    user_count -> diesel::helper_types::sum<user_schema::id>,
  }
}

select_query_source! {
  grouped_campaign (campaign_schema) {
    company_id -> campaign_schema::advertiser_id,
    campaign_count -> diesel::helper_types::sum<campaign_schema::id>,
  }
}

Then, run your query. You need to turn the underlying queries in to a sub-select query source with as_query_source(). This results in the SQL above.

user_schema::table
  .group_by(user_schema::company_id)
  .select(grouped_users::selection {
    company_id: user_schema::company_id,
    user_count: diesel::dsl::sum(user_schema::id),
  })
  .as_query_source()
  .inner_join(
    campaign_schema::table
      .group_by(campaign_schema::id)
      .select(grouped_campaign::selection {
        company_id: campaign_schema::advertiser_id,
        campaign_count: diesel::dsl::sum(campaign_schema::id),
      })
      .as_query_source(),
  )
  .select((
    grouped_users::company_id,
    grouped_users::user_count,
    grouped_campaign::campaign_count,
  ))
  .load(connection)

The reason I used a struct where you must provide the selection for each column, rather than having that automatic from the macro, is for many columns I use a column selection type which lets me dynamically switch between selecting the column or null (i.e. skip the column), which requires providing a value to the selection at query time.

---

Below is the code for it, although it's far from well tested and leaves a bunch I didn't need unimplemented. It also may use some local changes to the diesel source (mod visibility), so mightn't compile. This is intended as inspiration/showing a potential approach, than a proper implementation.

Source code (long and messy)

pub trait SelectQuerySourceSelection: Expression {
const ALIAS_NAME: &'static str;

type SourceTable: Table;
type AllColumns: ValidGrouping<()>;

fn all_columns() -> Self::AllColumns;
}

pub trait AsSelectQuerySource<S: SelectQuerySourceSelection>
where
Self: Sized,
{
fn as_query_source(self) -> SelectQuerySource<Self, S>;
}

impl<F, S, D, W, O, LOf, G, H, LC> AsSelectQuerySource<S::Selection> for SelectStatement<F, S, D, W, O, LOf, G, H, LC>
where
S: SelectClauseExpression<F>,
S::Selection: SelectQuerySourceSelection,
{
fn as_query_source(self) -> SelectQuerySource<Self, S::Selection> {
  SelectQuerySource {
    query: self,
    _selection: PhantomData,
  }
}
}

#[derive(Clone)]
pub struct SelectQuerySource<Q, S: SelectQuerySourceSelection> {
query: Q,
_selection: PhantomData<S>,
}

impl<Q, S: SelectQuerySourceSelection> QueryId for SelectQuerySource<Q, S> {
type QueryId = ();
const HAS_STATIC_QUERY_ID: bool = false; // false as S generates different SQL despite having the same type
}

impl<Q, S: SelectQuerySourceSelection> QueryFragment<DB> for SelectQuerySource<Q, S>
where
Q: QueryFragment<DB>,
DB: Backend,
{
fn walk_ast<'b>(&'b self, mut pass: AstPass<'_, 'b, DB>) -> QueryResult<()> {
  pass.push_sql("(");
  self.query.walk_ast(pass.reborrow())?;
  pass.push_sql(") ");
  pass.push_identifier(S::ALIAS_NAME)
}
}

impl<Q, S> QuerySource for SelectQuerySource<Q, S>
where
Q: Query + Clone + Sized,
S: Clone + Sized,
S: SelectQuerySourceSelection,
S::AllColumns: SelectableExpression<Self>,
{
type FromClause = Self;
type DefaultSelection = S::AllColumns;

fn from_clause(&self) -> Self::FromClause {
  self.clone()
}

fn default_selection(&self) -> Self::DefaultSelection {
  S::all_columns()
}
}

impl<Q, S: SelectQuerySourceSelection> diesel::query_builder::AsQuery for SelectQuerySource<Q, S>
where
Self: QuerySource,
<Self as QuerySource>::DefaultSelection: ValidGrouping<()>,
{
type SqlType = <<Self as QuerySource>::DefaultSelection as Expression>::SqlType;
type Query = diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<Self>>;

fn as_query(self) -> Self::Query {
  diesel::internal::table_macro::SelectStatement::simple(self)
}
}

impl<Q, S: SelectQuerySourceSelection> Table for SelectQuerySource<Q, S>
where
Self: QuerySource,
<Self as QuerySource>::DefaultSelection: ValidGrouping<()>,
<S as SelectQuerySourceSelection>::AllColumns: diesel::SelectableExpression<SelectQuerySource<Q, S>>,
{
type PrimaryKey = Self::DefaultSelection;

type AllColumns = S::AllColumns;

fn primary_key(&self) -> Self::PrimaryKey {
  self.default_selection()
}

fn all_columns() -> Self::AllColumns {
  S::all_columns()
}
}

use proc_macro::TokenStream;
use syn::parse_macro_input;

mod select_query_source;

#[proc_macro]
pub fn select_query_source(input: TokenStream) -> TokenStream {
select_query_source::expand(parse_macro_input!(input)).into()
}

use self::declaration::{ColumnDef, Declaration};
use proc_macro2::TokenStream;
use syn::parse_quote;
use syn::spanned::Spanned;
use syn::Ident;

mod declaration;

pub(crate) fn expand(input: Declaration) -> TokenStream {
  let select_query_name = &input.select_query_name;
  let source_table = &input.source_table;
  let imports = if input.use_statements.is_empty() {
    vec![parse_quote!(
      use super::*;
    )]
  } else {
    input.use_statements.clone()
  };
  let column_names = input.column_defs.iter().map(|c| &c.column_name).collect::<Vec<_>>();
  let column_names = &column_names;

  let column_defs = input.column_defs.iter().map(|c| expand_column_def(source_table, c));
  let column_ty = input.column_defs.iter().map(|c| &c.tpe).collect::<Vec<_>>();
  let column_ty = &column_ty;
  let valid_grouping_for_table_columns = generate_valid_grouping_for_table_columns(&input);

  let sql_name = &input.select_query_name.to_string();
  let static_query_fragment_impl_for_table = quote::quote! {
      impl diesel::internal::table_macro::StaticQueryFragment for selection {
          type Component = diesel::internal::table_macro::Identifier<'static>;
          const STATIC_COMPONENT: &'static Self::Component = &diesel::internal::table_macro::Identifier(#sql_name);
      }
  };

  let reexport_column_from_dsl = input.column_defs.iter().map(|c| {
    let column_name = &c.column_name;
    if c.column_name == *select_query_name {
      let span = c.column_name.span();
      let message = format!(
        "Column `{column_name}` cannot be named the same as it's table.\n\
               You may use `#[sql_name = \"{column_name}\"]` to reference the table's \
               `{column_name}` column \n\
               Docs available at: `https://docs.diesel.rs/master/diesel/macro.table.html`\n"
      );
      quote::quote_spanned! { span =>
          compile_error!(#message);
      }
    } else {
      quote::quote! {
          pub use super::columns::#column_name;
      }
    }
  });

  let backend_specific_table_impls = quote::quote! {
      // impl<S> diesel::JoinTo<diesel::query_builder::Only<S>> for selection
      // where
      //     diesel::query_builder::Only<S>: diesel::JoinTo<selection>,
      // {
      //     type FromClause = diesel::query_builder::Only<S>;
      //     type OnClause = <diesel::query_builder::Only<S> as diesel::JoinTo<selection>>::OnClause;

      //     fn join_target(__diesel_internal_rhs: diesel::query_builder::Only<S>) -> (Self::FromClause, Self::OnClause) {
      //         let (_, __diesel_internal_on_clause) = diesel::query_builder::Only::<S>::join_target(selection);
      //         (__diesel_internal_rhs, __diesel_internal_on_clause)
      //     }
      // }

      // impl diesel::query_source::AppearsInFromClause<diesel::query_builder::Only<selection>>
      //     for selection
      // {
      //     type Count = diesel::query_source::Once;
      // }

      // impl diesel::query_source::AppearsInFromClause<selection>
      //     for diesel::query_builder::Only<selection>
      // {
      //     type Count = diesel::query_source::Once;
      // }

      // impl<S, TSM> diesel::JoinTo<diesel::query_builder::Tablesample<S, TSM>> for selection
      // where
      //     diesel::query_builder::Tablesample<S, TSM>: diesel::JoinTo<selection>,
      //     TSM: diesel::internal::table_macro::TablesampleMethod
      // {
      //     type FromClause = diesel::query_builder::Tablesample<S, TSM>;
      //     type OnClause = <diesel::query_builder::Tablesample<S, TSM> as diesel::JoinTo<selection>>::OnClause;

      //     fn join_target(__diesel_internal_rhs: diesel::query_builder::Tablesample<S, TSM>) -> (Self::FromClause, Self::OnClause) {
      //         let (_, __diesel_internal_on_clause) = diesel::query_builder::Tablesample::<S, TSM>::join_target(selection);
      //         (__diesel_internal_rhs, __diesel_internal_on_clause)
      //     }
      // }

      // impl<TSM> diesel::query_source::AppearsInFromClause<diesel::query_builder::Tablesample<selection, TSM>>
      //     for selection
      //         where
      //     TSM: diesel::internal::table_macro::TablesampleMethod
      // {
      //     type Count = diesel::query_source::Once;
      // }

      // impl<TSM> diesel::query_source::AppearsInFromClause<selection>
      //     for diesel::query_builder::Tablesample<selection, TSM>
      //         where
      //     TSM: diesel::internal::table_macro::TablesampleMethod
      // {
      //     type Count = diesel::query_source::Once;
      // }
  };

  let imports_for_column_module = imports.iter().map(fix_import_for_submodule);

  let struct_fields = input.column_defs.iter().map(|c| {
    let column_type = &c.tpe;
    let span = column_type.span();
    let column_type = quote::quote_spanned! { span =>
        #column_type
    };

    let column_name = &c.column_name;
    let span = column_name.span();

    quote::quote_spanned! { span =>
        pub #column_name: #column_type
    }
  });

  let struct_accessors = input.column_defs.iter().map(|c| {
    let column_name = &c.column_name;
    let span = column_name.span();

    quote::quote_spanned! { span =>
        self.#column_name
    }
  });

  quote::quote! {
      // #(#meta)* TODO:
      #[allow(unused_imports, dead_code, unreachable_pub, unused_qualifications)]
      pub mod #select_query_name {
          use ::diesel;
          pub use self::columns::*;
          #(#imports)*

          /// Re-exports all of the columns of this table, as well as the
          /// table struct renamed to the module name. This is meant to be
          /// glob imported for functions which only deal with one table.
          pub mod dsl {
              #(#reexport_column_from_dsl)*
              pub use super::selection as #select_query_name;
          }

          #[allow(non_upper_case_globals, dead_code)]
          /// A tuple of all of the columns on this table
          pub const all_columns: (#(#column_names,)*) = (#(#column_names,)*);

          #[allow(non_camel_case_types)]
          #[derive(Debug, Clone, diesel::query_builder::QueryId)]
          /// The actual table struct
          ///
          /// This is the type which provides the base methods of the query
          /// builder, such as `.select` and `.filter`.
          pub struct selection {
              #(#struct_fields,)*
          }

          impl<DB: diesel::backend::Backend> QueryFragment<DB> for selection
          where
            #(#column_ty: QueryFragment<DB>,)*
          {
              fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
                  let mut needs_comma = false;
                  #(
                      if !self.#column_names.is_noop(out.backend())? {
                          if needs_comma {
                              out.push_sql(", ");
                          }
                          self.#column_names.walk_ast(out.reborrow())?;
                          out.push_sql(" AS ");
                          out.push_identifier(#column_names::NAME)?;
                          needs_comma = true;
                      }
                  )*
                  Ok(())
              }
          }

          pub type TupleSelection = (#(#column_ty,)*);

          /// The SQL type of all of the columns on this table
          pub type SqlType = <TupleSelection as diesel::expression::Expression>::SqlType;

          impl Expression for selection {
              type SqlType = SqlType;
          }

          impl SelectableExpression<#source_table::table> for selection {}
          impl diesel::AppearsOnTable<#source_table::table> for selection {}

          impl<__GB> diesel::expression::ValidGrouping<__GB> for selection
          where
            TupleSelection: ValidGrouping<__GB>,
          {
              type IsAggregate = <TupleSelection as ValidGrouping<__GB>>::IsAggregate;
          }

          impl crate::diesel_extensions::select_query_source::SelectQuerySourceSelection for selection {
            const ALIAS_NAME: &'static str = #sql_name;

            type SourceTable = #source_table::table;
            type AllColumns = (#(#column_names,)*);

            fn all_columns() -> Self::AllColumns {
                (#(#column_names,)*)
            }
          }

          /// Helper type for representing a boxed query from this table
          // pub type BoxedQuery<'a, DB, ST = SqlType> = diesel::internal::table_macro::BoxedSelectStatement<'a, ST, diesel::internal::table_macro::FromClause<#source_table::table>, DB>;

          // impl diesel::QuerySource for selection {
          //     type FromClause = Self; // TODO: we might need to write a wrapper to add the `( ... ) alias` around the query
          //     type DefaultSelection = (#(#column_names,)*);

          //     fn from_clause(&self) -> Self::FromClause {
          //         self.clone()
          //     }

          //     fn default_selection(&self) -> Self::DefaultSelection {
          //         use diesel::Table;
          //         (#(#column_names,)*)
          //     }
          // }

          // impl<DB> diesel::query_builder::QueryFragment<DB> for selection where
          //     DB: diesel::backend::Backend,
          //     <selection as diesel::internal::table_macro::StaticQueryFragment>::Component: diesel::query_builder::QueryFragment<DB>
          // {
          //     fn walk_ast<'b>(&'b self, __diesel_internal_pass: diesel::query_builder::AstPass<'_, 'b, DB>) -> diesel::result::QueryResult<()> {
          //         <selection as diesel::internal::table_macro::StaticQueryFragment>::STATIC_COMPONENT.walk_ast(__diesel_internal_pass)
          //     }
          // }

          #static_query_fragment_impl_for_table

          // impl diesel::query_builder::AsQuery for selection {
          //     type SqlType = SqlType;
          //     type Query = diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<#source_table::table>>;

          //     fn as_query(self) -> Self::Query {
          //         diesel::internal::table_macro::SelectStatement::simple(self)
          //     }
          // }

          // impl SelectQuerySource for selection {}

          // impl diesel::Table for table {
          //     type PrimaryKey = #primary_key;
          //     type AllColumns = (#(#column_names,)*);

          //     fn primary_key(&self) -> Self::PrimaryKey {
          //         #primary_key
          //     }

          //     fn all_columns() -> Self::AllColumns {
          //         (#(#column_names,)*)
          //     }
          // }

          // impl diesel::associations::HasTable for selection {
          //     type Table = #source_table::table;

          //     fn table() -> Self::Table {
          //         #source_table::table
          //     }
          // }

          impl<Q> diesel::query_source::AppearsInFromClause<crate::diesel_extensions::select_query_source::SelectQuerySource<Q, selection>> for selection {
              type Count = diesel::query_source::Once;
          }

          impl<Q1, Q2> diesel::query_source::AppearsInFromClause<crate::diesel_extensions::select_query_source::SelectQuerySource<Q1, selection>>
            for crate::diesel_extensions::select_query_source::SelectQuerySource<Q2, selection>
          {
            type Count = diesel::query_source::Once;
          }


          // // impl<S: AliasSource<Table=selection>> AppearsInFromClause<selection> for Alias<S>
          // impl<S> diesel::internal::table_macro::AliasAppearsInFromClause<S, selection> for selection
          // where S: diesel::query_source::AliasSource<Target=selection>,
          // {
          //     type Count = diesel::query_source::Never;
          // }

          // // impl<S1: AliasSource<Table=selection>, S2: AliasSource<Table=selection>> AppearsInFromClause<Alias<S1>> for Alias<S2>
          // // Those are specified by the `alias!` macro, but this impl will allow it to implement this trait even in downstream
          // // crates from the schema
          // impl<S1, S2> diesel::internal::table_macro::AliasAliasAppearsInFromClause<selection, S2, S1> for selection
          // where S1: diesel::query_source::AliasSource<Target=selection>,
          //       S2: diesel::query_source::AliasSource<Target=selection>,
          //       S1: diesel::internal::table_macro::AliasAliasAppearsInFromClauseSameTable<S2, selection>,
          // {
          //     type Count = <S1 as diesel::internal::table_macro::AliasAliasAppearsInFromClauseSameTable<S2, selection>>::Count;
          // }

          // impl<S> diesel::query_source::AppearsInFromClause<diesel::query_source::Alias<S>> for selection
          // where S: diesel::query_source::AliasSource,
          // {
          //     type Count = diesel::query_source::Never;
          // }

          // impl<S, C> diesel::internal::table_macro::FieldAliasMapperAssociatedTypesDisjointnessTrick<selection, S, C> for selection
          // where
          //     S: diesel::query_source::AliasSource<Target = selection> + ::std::clone::Clone,
          //     C: diesel::query_source::Column<Table = selection>,
          // {
          //     type Out = diesel::query_source::AliasedField<S, C>;

          //     fn map(__diesel_internal_column: C, __diesel_internal_alias: &diesel::query_source::Alias<S>) -> Self::Out {
          //         __diesel_internal_alias.field(__diesel_internal_column)
          //     }
          // }

          // impl diesel::query_source::AppearsInFromClause<selection> for diesel::internal::table_macro::NoFromClause {
          //     type Count = diesel::query_source::Never;
          // }

          // impl<Left, Right, Kind> diesel::JoinTo<diesel::internal::table_macro::Join<Left, Right, Kind>> for selection where
          //     diesel::internal::table_macro::Join<Left, Right, Kind>: diesel::JoinTo<selection>,
          //     Left: diesel::query_source::QuerySource,
          //     Right: diesel::query_source::QuerySource,
          // {
          //     type FromClause = diesel::internal::table_macro::Join<Left, Right, Kind>;
          //     type OnClause = <diesel::internal::table_macro::Join<Left, Right, Kind> as diesel::JoinTo<selection>>::OnClause;

          //     fn join_target(__diesel_internal_rhs: diesel::internal::table_macro::Join<Left, Right, Kind>) -> (Self::FromClause, Self::OnClause) {
          //         let (_, __diesel_internal_on_clause) = diesel::internal::table_macro::Join::join_target(selection);
          //         (__diesel_internal_rhs, __diesel_internal_on_clause)
          //     }
          // }

          // impl<Join, On> diesel::JoinTo<diesel::internal::table_macro::JoinOn<Join, On>> for selection where
          //     diesel::internal::table_macro::JoinOn<Join, On>: diesel::JoinTo<selection>,
          // {
          //     type FromClause = diesel::internal::table_macro::JoinOn<Join, On>;
          //     type OnClause = <diesel::internal::table_macro::JoinOn<Join, On> as diesel::JoinTo<selection>>::OnClause;

          //     fn join_target(__diesel_internal_rhs: diesel::internal::table_macro::JoinOn<Join, On>) -> (Self::FromClause, Self::OnClause) {
          //         let (_, __diesel_internal_on_clause) = diesel::internal::table_macro::JoinOn::join_target(selection);
          //         (__diesel_internal_rhs, __diesel_internal_on_clause)
          //     }
          // }

          // impl<F, S, D, W, O, L, Of, G> diesel::JoinTo<diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<F>, S, D, W, O, L, Of, G>> for selection where
          //     diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<F>, S, D, W, O, L, Of, G>: diesel::JoinTo<selection>,
          //     F: diesel::query_source::QuerySource
          // {
          //     type FromClause = diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<F>, S, D, W, O, L, Of, G>;
          //     type OnClause = <diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<F>, S, D, W, O, L, Of, G> as diesel::JoinTo<selection>>::OnClause;

          //     fn join_target(__diesel_internal_rhs: diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<F>, S, D, W, O, L, Of, G>) -> (Self::FromClause, Self::OnClause) {
          //         let (_, __diesel_internal_on_clause) = diesel::internal::table_macro::SelectStatement::join_target(selection);
          //         (__diesel_internal_rhs, __diesel_internal_on_clause)
          //     }
          // }

          // impl<'a, QS, ST, DB> diesel::JoinTo<diesel::internal::table_macro::BoxedSelectStatement<'a, diesel::internal::table_macro::FromClause<QS>, ST, DB>> for selection where
          //     diesel::internal::table_macro::BoxedSelectStatement<'a, diesel::internal::table_macro::FromClause<QS>, ST, DB>: diesel::JoinTo<selection>,
          //     QS: diesel::query_source::QuerySource,
          // {
          //     type FromClause = diesel::internal::table_macro::BoxedSelectStatement<'a, diesel::internal::table_macro::FromClause<QS>, ST, DB>;
          //     type OnClause = <diesel::internal::table_macro::BoxedSelectStatement<'a, diesel::internal::table_macro::FromClause<QS>, ST, DB> as diesel::JoinTo<selection>>::OnClause;
          //     fn join_target(__diesel_internal_rhs: diesel::internal::table_macro::BoxedSelectStatement<'a, diesel::internal::table_macro::FromClause<QS>, ST, DB>) -> (Self::FromClause, Self::OnClause) {
          //         let (_, __diesel_internal_on_clause) = diesel::internal::table_macro::BoxedSelectStatement::join_target(selection);
          //         (__diesel_internal_rhs, __diesel_internal_on_clause)
          //     }
          // }

          // impl<S> diesel::JoinTo<diesel::query_source::Alias<S>> for selection
          // where
          //     diesel::query_source::Alias<S>: diesel::JoinTo<selection>,
          // {
          //     type FromClause = diesel::query_source::Alias<S>;
          //     type OnClause = <diesel::query_source::Alias<S> as diesel::JoinTo<selection>>::OnClause;

          //     fn join_target(__diesel_internal_rhs: diesel::query_source::Alias<S>) -> (Self::FromClause, Self::OnClause) {
          //         let (_, __diesel_internal_on_clause) = diesel::query_source::Alias::<S>::join_target(selection);
          //         (__diesel_internal_rhs, __diesel_internal_on_clause)
          //     }
          // }

          #backend_specific_table_impls

          /// Contains all of the columns of this table
          pub mod columns {
              use ::diesel;
              use super::selection;
              #(#imports_for_column_module)*

              // #[allow(non_camel_case_types, dead_code)]
              // #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId)]
              // /// Represents `select_query_name.*`, which is sometimes needed for
              // /// efficient count queries. It cannot be used in place of
              // /// `all_columns`, and has a `SqlType` of `()` to prevent it
              // /// being used that way
              // pub struct star;

              // impl<__GB> diesel::expression::ValidGrouping<__GB> for star
              // where
              //     (#(#column_names,)*): diesel::expression::ValidGrouping<__GB>,
              // {
              //     type IsAggregate = <(#(#column_names,)*) as diesel::expression::ValidGrouping<__GB>>::IsAggregate;
              // }

              // impl diesel::Expression for star {
              //     type SqlType = diesel::expression::expression_types::Notselection;
              // }

              // // Unclear why we need DieselReserveSpecialization here now
              // impl<DB: diesel::backend::Backend + diesel::backend::DieselReserveSpecialization> diesel::query_builder::QueryFragment<DB> for star where
              //     <selection as diesel::QuerySource>::FromClause: diesel::query_builder::QueryFragment<DB>,
              // {
              //     #[allow(non_snake_case)]
              //     fn walk_ast<'b>(&'b self, mut __diesel_internal_out: diesel::query_builder::AstPass<'_, 'b, DB>) -> diesel::result::QueryResult<()>
              //     {
              //         use diesel::QuerySource;

              //         if !__diesel_internal_out.should_skip_from() {
              //             const FROM_CLAUSE: diesel::internal::table_macro::StaticQueryFragmentInstance<#source_table::table> = diesel::internal::table_macro::StaticQueryFragmentInstance::new();

              //             FROM_CLAUSE.walk_ast(__diesel_internal_out.reborrow())?;
              //             __diesel_internal_out.push_sql(".");
              //         }
              //         __diesel_internal_out.push_sql("*");
              //         Ok(())
              //     }
              // }

              // impl diesel::SelectableExpression<selection> for star {
              // }

              // impl diesel::AppearsOnTable<selection> for star {
              // }

              #(#column_defs)*

              #(#valid_grouping_for_table_columns)*
          }
      }
  }
}

fn generate_valid_grouping_for_table_columns(decl: &Declaration) -> Vec<TokenStream> {
  let mut ret = Vec::with_capacity(decl.column_defs.len() * decl.column_defs.len());

  for (id, right_col) in decl.column_defs.iter().enumerate() {
    for left_col in decl.column_defs.iter().skip(id) {
      let left_col = &left_col.column_name;
      let right_col = &right_col.column_name;

      if left_col != right_col {
        ret.push(quote::quote! {
            impl diesel::expression::IsContainedInGroupBy<#right_col> for #left_col {
                type Output = diesel::expression::is_contained_in_group_by::No;
            }

            impl diesel::expression::IsContainedInGroupBy<#left_col> for #right_col {
                type Output = diesel::expression::is_contained_in_group_by::No;
            }
        });
      }
    }
  }
  ret
}

fn fix_import_for_submodule(import: &syn::ItemUse) -> syn::ItemUse {
  let mut ret = import.clone();

  if let syn::UseTree::Path(ref mut path) = ret.tree {
    // prepend another `super` to the any import
    // that starts with `super` so that it now refers to the correct
    // module
    if path.ident == "super" {
      let inner = path.clone();
      path.tree = Box::new(syn::UseTree::Path(inner));
    }
  }

  ret
}

fn expand_column_def(source_table: &Ident, column_def: &ColumnDef) -> TokenStream {
  let span = column_def.column_name.span();
  let column_name = &column_def.column_name;
  let sql_name = &column_def.sql_name;
  let struct_type = &column_def.tpe;

  let backend_specific_column_impl = quote::quote! {
      // impl diesel::query_source::AppearsInFromClause<diesel::query_builder::Only<super::selection>>
      //     for #column_name
      // {
      //     type Count = diesel::query_source::Once;
      // }
      // impl diesel::SelectableExpression<diesel::query_builder::Only<super::selection>> for #column_name {}

      // impl<TSM> diesel::query_source::AppearsInFromClause<diesel::query_builder::Tablesample<super::selection, TSM>>
      //     for #column_name
      //         where
      //     TSM: diesel::internal::table_macro::TablesampleMethod
      // {
      //     type Count = diesel::query_source::Once;
      // }
      // impl<TSM> diesel::SelectableExpression<diesel::query_builder::Tablesample<super::selection, TSM>>
      //     for #column_name where TSM: diesel::internal::table_macro::TablesampleMethod {}
  };

  // TODO: we'll need some ops
  // let ops_impls = if is_numeric(&column_def.tpe) {
  //   let add = generate_op_impl("Add", column_name);
  //   let sub = generate_op_impl("Sub", column_name);
  //   let div = generate_op_impl("Div", column_name);
  //   let mul = generate_op_impl("Mul", column_name);
  //   Some(quote::quote! {
  //       #add
  //       #sub
  //       #div
  //       #mul
  //   })
  // } else if is_date_time(&column_def.tpe) || is_network(&column_def.tpe) {
  //   let add = generate_op_impl("Add", column_name);
  //   let sub = generate_op_impl("Sub", column_name);
  //   Some(quote::quote! {
  //       #add
  //       #sub
  //   })
  // } else {
  //   None
  // };

  quote::quote_spanned! {span=>
      #[allow(non_camel_case_types, dead_code)]
      #[derive(Debug, Clone, Copy, diesel::query_builder::QueryId, Default)]
      pub struct #column_name;

      impl diesel::expression::Expression for #column_name {
          type SqlType = <#struct_type as diesel::expression::Expression>::SqlType;
      }

      impl<DB> diesel::query_builder::QueryFragment<DB> for #column_name where
          DB: diesel::backend::Backend,
          diesel::internal::table_macro::StaticQueryFragmentInstance<super::selection>: diesel::query_builder::QueryFragment<DB>,
      {
          #[allow(non_snake_case)]
          fn walk_ast<'b>(&'b self, mut __diesel_internal_out: diesel::query_builder::AstPass<'_, 'b, DB>) -> diesel::result::QueryResult<()>
          {
              if !__diesel_internal_out.should_skip_from() {
                  const FROM_CLAUSE: diesel::internal::table_macro::StaticQueryFragmentInstance<super::selection> = diesel::internal::table_macro::StaticQueryFragmentInstance::new();

                  FROM_CLAUSE.walk_ast(__diesel_internal_out.reborrow())?;
                  __diesel_internal_out.push_sql(".");
              }
              __diesel_internal_out.push_identifier(#sql_name)
          }
      }

      // impl diesel::SelectableExpression<super::selection> for #column_name {
      // }

      impl<Q> diesel::SelectableExpression<crate::diesel_extensions::select_query_source::SelectQuerySource<Q, super::selection>> for #column_name {}

      // TODO: think this is incorrect
      // impl<QS> diesel::AppearsOnTable<QS> for #column_name where
      //     QS: diesel::query_source::AppearsInFromClause<super::selection, Count=diesel::query_source::Once>,
      // {
      // }

      // crate::diesel_extensions::select_query_source::SelectQuerySource<diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<#source_table::table>>, super::selection>

      // diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<From>>

      impl<QS> diesel::AppearsOnTable<QS> for #column_name where
          QS: diesel::query_source::AppearsInFromClause<crate::diesel_extensions::select_query_source::SelectQuerySource<diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<#source_table::table>>, super::selection>, Count=diesel::query_source::Once>,
      {
      }

      impl<Left, Right> diesel::SelectableExpression<
              diesel::internal::table_macro::Join<Left, Right, diesel::internal::table_macro::LeftOuter>,
          > for #column_name where
          #column_name: diesel::AppearsOnTable<diesel::internal::table_macro::Join<Left, Right, diesel::internal::table_macro::LeftOuter>>,
          Self: diesel::SelectableExpression<Left>,
          // If our table is on the right side of this join, only
          // `Nullable<Self>` can be selected
          Right: diesel::query_source::AppearsInFromClause<crate::diesel_extensions::select_query_source::SelectQuerySource<diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<#source_table::table>>, super::selection>, Count=diesel::query_source::Never> + diesel::query_source::QuerySource,
          Left: diesel::query_source::QuerySource
      {
      }

      impl<Left, Right> diesel::SelectableExpression<
              diesel::internal::table_macro::Join<Left, Right, diesel::internal::table_macro::Inner>,
          > for #column_name where
          #column_name: diesel::AppearsOnTable<diesel::internal::table_macro::Join<Left, Right, diesel::internal::table_macro::Inner>>,
          Left: diesel::query_source::AppearsInFromClause<crate::diesel_extensions::select_query_source::SelectQuerySource<diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<#source_table::table>>, super::selection>> + diesel::query_source::QuerySource,
          Right: diesel::query_source::AppearsInFromClause<crate::diesel_extensions::select_query_source::SelectQuerySource<diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<#source_table::table>>, super::selection>> + diesel::query_source::QuerySource,
      (Left::Count, Right::Count): diesel::internal::table_macro::Pick<Left, Right>,
          Self: diesel::SelectableExpression<
              <(Left::Count, Right::Count) as diesel::internal::table_macro::Pick<Left, Right>>::Selection,
          >,
      {
      }

      // FIXME: Remove this when overlapping marker traits are stable
      impl<Join, On> diesel::SelectableExpression<diesel::internal::table_macro::JoinOn<Join, On>> for #column_name where
          #column_name: diesel::SelectableExpression<Join> + diesel::AppearsOnTable<diesel::internal::table_macro::JoinOn<Join, On>>,
      {
      }

      // FIXME: Remove this when overlapping marker traits are stable
      impl<From> diesel::SelectableExpression<diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<From>>> for #column_name where
          From: diesel::query_source::QuerySource,
          #column_name: diesel::SelectableExpression<From> + diesel::AppearsOnTable<diesel::internal::table_macro::SelectStatement<diesel::internal::table_macro::FromClause<From>>>,
      {
      }

      impl<__GB> diesel::expression::ValidGrouping<__GB> for #column_name
      where __GB: diesel::expression::IsContainedInGroupBy<#column_name, Output = diesel::expression::is_contained_in_group_by::Yes>,
      {
          type IsAggregate = diesel::expression::is_aggregate::Yes;
      }

      impl diesel::expression::ValidGrouping<()> for #column_name {
          type IsAggregate = diesel::expression::is_aggregate::No;
      }

      impl diesel::expression::IsContainedInGroupBy<#column_name> for #column_name {
          type Output = diesel::expression::is_contained_in_group_by::Yes;
      }

      impl diesel::query_source::Column for #column_name {
          type Table = <#source_table::table as diesel::associations::HasTable>::Table;

          const NAME: &'static str = #sql_name;
      }

      // I can't seem to get this to work, extracting the SqlType out doesn't work, and we don't know the SqlType here
      // impl<T> diesel::EqAll<T> for #column_name where
      //     T: diesel::expression::AsExpression<<#struct_type as diesel::expression::Expression>::SqlType>,
      //     diesel::dsl::Eq<#column_name, T::Expression>: diesel::Expression<SqlType=diesel::sql_types::Bool>,
      // {
      //     type Output = diesel::dsl::Eq<Self, T::Expression>;

      //     fn eq_all(self, __diesel_internal_rhs: T) -> Self::Output {
      //         use diesel::expression_methods::ExpressionMethods;
      //         self.eq(__diesel_internal_rhs)
      //     }
      // }

      // #ops_impls
      #backend_specific_column_impl
  }
}

use syn::Ident;

pub struct Declaration {
  pub use_statements: Vec<syn::ItemUse>,
  pub select_query_name: Ident,
  pub source_table: Ident,
  pub column_defs: syn::punctuated::Punctuated<ColumnDef, syn::Token![,]>,
}

pub struct ColumnDef {
  pub column_name: Ident,
  pub sql_name: String,
  _arrow: syn::Token![->],
  pub tpe: syn::TypePath,
}

impl syn::parse::Parse for Declaration {
  fn parse(buf: &syn::parse::ParseBuffer<'_>) -> Result<Self, syn::Error> {
    let mut use_statements = Vec::new();
    loop {
      let fork = buf.fork();
      if fork.parse::<syn::ItemUse>().is_ok() {
        use_statements.push(buf.parse()?);
      } else {
        break;
      };
    }

    let select_query_name = buf.parse()?;
    let content;
    let _: syn::token::Paren = syn::parenthesized!(content in buf);
    let source_table = content.parse()?;

    let content;
    let _: syn::token::Brace = syn::braced!(content in buf);
    let column_defs = syn::punctuated::Punctuated::parse_terminated(&content)?;

    Ok(Declaration {
      use_statements,
      select_query_name,
      source_table,
      column_defs,
    })
  }
}

impl syn::parse::Parse for ColumnDef {
  fn parse(input: syn::parse::ParseStream) -> syn::Result<Self> {
    let column_name: syn::Ident = input.parse()?;
    let _arrow: syn::Token![->] = input.parse()?;
    let tpe: syn::TypePath = input.parse()?;

    let sql_name = column_name.to_string();

    Ok(Self {
      column_name,
      _arrow,
      tpe,
      sql_name,
    })
  }
}

[weiznich]

Thanks for sharing this and good job to make this work 👍

This nicely identifies the main problem we have for supporting such constructs out of the box: Naming the result columns from the subqueries.

It's something I want to address someday, but my idea is a bit different that this approach: Instead of mirroring the table! definitions I would like to extend the alias! macro to allow defining aliases for select expressions. If you define such aliases before passing them to select you can share them between your subquery and your outer query, which would enable using them on the outer query + checking via SelectExpression<E> that they are a valid select expression for the subquery. That should make this much easier to express for the user. Unfortunately as with many things I do not have the capacity to implement that right now, I likely can provide pointers where to start looking into this and how to approach the problem.