Is there a way of excluding specific fields from select queries?

[thomasmost]

i.e. without creating a new struct, do not select a specific (Optional) column such that is it is always None ?

[Ten0]

The best way to achieve that would likely be to not mention it in the select expression of your query.
https://docs.rs/diesel/latest/diesel/prelude/trait.QueryDsl.html#method.select

If you need to add an extra empty field on the fly without making an extra allocation, you may use load_iter(), and map to a different type that has that field set to an empty value.

[thomasmost]

Is there a way to select a raw value in the column's place? If I do this:

#[derive(AsChangeset, Default, Insertable, Queryable, Serialize)]
#[diesel(table_name = org_transaction)]
pub struct OrgTransaction {
  pub id: String,
  pub org_id: String,
  pub amount: i64,
  pub currency_code: String,
  pub merchant_name: String,
  pub initiated_at: Option<chrono::NaiveDate>,
  pub raw_transaction: Option<String>,
  pub created_at: chrono::NaiveDateTime,
}


    org_transaction::table
      .filter(org_transaction::org_id.eq(org_id))
      .select((
        org_transaction::id,
        org_transaction::org_id,
        org_transaction::amount,
        org_transaction::currency_code,
        org_transaction::merchant_name,
        org_transaction::initiated_at,
        None as Option<String>,
        org_transaction::created_at,
      ))
      .order(org_transaction::initiated_at.desc())
      .load::<OrgTransaction>(connection)
      .expect("Error reading org_transactions")

I get the error: the trait OrderDslis implemented forSelectStatement— and if I exclude theNone as Option` like so:

    org_transaction::table
      .filter(org_transaction::org_id.eq(org_id))
      .select((
        org_transaction::id,
        org_transaction::org_id,
        org_transaction::amount,
        org_transaction::currency_code,
        org_transaction::merchant_name,
        org_transaction::initiated_at,
        org_transaction::created_at,
      ))
      .order(org_transaction::initiated_at.desc())
      .load::<OrgTransaction>(connection)
      .expect("Error reading org_transactions")

I get an error on the LoadQuery implying I cannot satisfy the struct fields

[Ten0]

It seems to have inferred that you're looking for a non Nullable text sql type, instead of a Nullable text sql type. Try specifying explicitly the generic type parameter of the into_sql function as sql_types::Text and see where the compiler errors take you from there.
If that doesn't help please again provide the full code and error.

[thomasmost]

Okay, sorry, I took a break and came back to it but I still need help. Full function:

  pub fn read_assessed_since_date(
    connection: &mut MysqlConnection,
    org_id: &str,
    date: chrono::NaiveDate,
  ) -> Vec<OrgTransaction> {
    org_transaction::table
      .filter(org_transaction::org_id.eq(org_id))
      .filter(org_transaction::kgco2e_estimate.is_not_null())
      .filter(org_transaction::initiated_at.ge(date))
      .select((
        org_transaction::id,
        org_transaction::org_id,
        org_transaction::service_connection_id,
        org_transaction::amount,
        org_transaction::currency_code,
        org_transaction::merchant_name,
        org_transaction::merchant_raw,
        org_transaction::mcc,
        org_transaction::transaction_type,
        org_transaction::provider_tx_id,
        org_transaction::provider_account_id,
        org_transaction::initiated_at,
        org_transaction::posted_at,
        None::<Option<String>>.into_sql::<sql_types::Nullable<sql_types::Text>>(),
        org_transaction::created_at,
        org_transaction::factor_id,
        org_transaction::merchant_org_id,
        org_transaction::kgco2e_estimate,
        org_transaction::transaction_import_id,
        org_transaction::offsets_bill_id,
        org_transaction::category_id,
        org_transaction::included_in_offsets_bill_id,
        org_transaction::represented_offsets_bill_id,
        org_transaction::assessment_method,
        org_transaction::user_id,
        org_transaction::mask,
        org_transaction::provider_transaction_group_id,
        org_transaction::duplicate_tx_id,
      ))
      .order(org_transaction::initiated_at.desc())
      .load::<OrgTransaction>(connection)
      .expect("Error reading org_transactions")
  }

Full error:

error[E0277]: the trait bound `std::option::Option<std::string::String>: ToSql<diesel::sql_types::Text, Mysql>` is not satisfied
  --> src/dal/org_transaction.rs:223:8
   |
223  |       .load::<OrgTransaction>(connection)
   |        ^^^^ the trait `ToSql<diesel::sql_types::Text, Mysql>` is not implemented for `std::option::Option<std::string::String>`
   |
   = help: the trait `ToSql<diesel::sql_types::Nullable<ST>, DB>` is implemented for `std::option::Option<T>`
   = note: required because of the requirements on the impl of `ToSql<diesel::sql_types::Nullable<diesel::sql_types::Text>, Mysql>` for `std::option::Option<std::option::Option<std::string::String>>`
   = note: required because of the requirements on the impl of `QueryFragment<Mysql>` for `expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, std::option::Option<std::option::Option<std::string::String>>>`
   = note: 7 redundant requirements hidden
   = note: required because of the requirements on the impl of `QueryFragment<Mysql>` for `SelectStatement<FromClause<schema::org_transaction::table>, query_builder::select_clause::SelectClause<(schema::org_transaction::columns::id, schema::org_transaction::columns::org_id, schema::org_transaction::columns::service_connection_id, schema::org_transaction::columns::amount, schema::org_transaction::columns::currency_code, schema::org_transaction::columns::merchant_name, schema::org_transaction::columns::merchant_raw, schema::org_transaction::columns::mcc, schema::org_transaction::columns::transaction_type, schema::org_transaction::columns::provider_tx_id, schema::org_transaction::columns::provider_account_id, schema::org_transaction::columns::initiated_at, schema::org_transaction::columns::posted_at, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, std::option::Option<std::option::Option<std::string::String>>>, schema::org_transaction::columns::created_at, schema::org_transaction::columns::factor_id, schema::org_transaction::columns::merchant_org_id, schema::org_transaction::columns::kgco2e_estimate, schema::org_transaction::columns::transaction_import_id, schema::org_transaction::columns::offsets_bill_id, schema::org_transaction::columns::category_id, schema::org_transaction::columns::included_in_offsets_bill_id, schema::org_transaction::columns::represented_offsets_bill_id, schema::org_transaction::columns::assessment_method, schema::org_transaction::columns::user_id, schema::org_transaction::columns::mask, schema::org_transaction::columns::provider_transaction_group_id, schema::org_transaction::columns::duplicate_tx_id)>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::Eq<schema::org_transaction::columns::org_id, expression::bound::Bound<diesel::sql_types::Text, &str>>>, expression::grouped::Grouped<expression::operators::IsNotNull<schema::org_transaction::columns::kgco2e_estimate>>>>, expression::grouped::Grouped<expression::operators::GtEq<schema::org_transaction::columns::initiated_at, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Date>, chrono::NaiveDate>>>>>>, query_builder::order_clause::OrderClause<expression::operators::Desc<schema::org_transaction::columns::initiated_at>>>`
   = note: required because of the requirements on the impl of `LoadQuery<'_, _, dal::org_transaction::OrgTransaction>` for `SelectStatement<FromClause<schema::org_transaction::table>, query_builder::select_clause::SelectClause<(schema::org_transaction::columns::id, schema::org_transaction::columns::org_id, schema::org_transaction::columns::service_connection_id, schema::org_transaction::columns::amount, schema::org_transaction::columns::currency_code, schema::org_transaction::columns::merchant_name, schema::org_transaction::columns::merchant_raw, schema::org_transaction::columns::mcc, schema::org_transaction::columns::transaction_type, schema::org_transaction::columns::provider_tx_id, schema::org_transaction::columns::provider_account_id, schema::org_transaction::columns::initiated_at, schema::org_transaction::columns::posted_at, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, std::option::Option<std::option::Option<std::string::String>>>, schema::org_transaction::columns::created_at, schema::org_transaction::columns::factor_id, schema::org_transaction::columns::merchant_org_id, schema::org_transaction::columns::kgco2e_estimate, schema::org_transaction::columns::transaction_import_id, schema::org_transaction::columns::offsets_bill_id, schema::org_transaction::columns::category_id, schema::org_transaction::columns::included_in_offsets_bill_id, schema::org_transaction::columns::represented_offsets_bill_id, schema::org_transaction::columns::assessment_method, schema::org_transaction::columns::user_id, schema::org_transaction::columns::mask, schema::org_transaction::columns::provider_transaction_group_id, schema::org_transaction::columns::duplicate_tx_id)>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::Eq<schema::org_transaction::columns::org_id, expression::bound::Bound<diesel::sql_types::Text, &str>>>, expression::grouped::Grouped<expression::operators::IsNotNull<schema::org_transaction::columns::kgco2e_estimate>>>>, expression::grouped::Grouped<expression::operators::GtEq<schema::org_transaction::columns::initiated_at, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Date>, chrono::NaiveDate>>>>>>, query_builder::order_clause::OrderClause<expression::operators::Desc<schema::org_transaction::columns::initiated_at>>>`
note: required by a bound in `diesel::RunQueryDsl::load`
  --> /Users/thomas/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-2.0.2/src/query_dsl/mod.rs:1499:15
   |
1499 |         Self: LoadQuery<'query, Conn, U>,
   |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `diesel::RunQueryDsl::load`

error[E0271]: type mismatch resolving `<Mysql as SqlDialect>::SelectStatementSyntax == query_builder::private::NotSpecialized`
  --> src/dal/org_transaction.rs:223:8
   |
223  |       .load::<OrgTransaction>(connection)
   |        ^^^^ expected struct `query_builder::private::NotSpecialized`, found struct `backend::sql_dialect::select_statement_syntax::AnsiSqlSelectStatement`
   |
   = note: required because of the requirements on the impl of `QueryFragment<Mysql>` for `SelectStatement<FromClause<schema::org_transaction::table>, query_builder::select_clause::SelectClause<(schema::org_transaction::columns::id, schema::org_transaction::columns::org_id, schema::org_transaction::columns::service_connection_id, schema::org_transaction::columns::amount, schema::org_transaction::columns::currency_code, schema::org_transaction::columns::merchant_name, schema::org_transaction::columns::merchant_raw, schema::org_transaction::columns::mcc, schema::org_transaction::columns::transaction_type, schema::org_transaction::columns::provider_tx_id, schema::org_transaction::columns::provider_account_id, schema::org_transaction::columns::initiated_at, schema::org_transaction::columns::posted_at, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, std::option::Option<std::option::Option<std::string::String>>>, schema::org_transaction::columns::created_at, schema::org_transaction::columns::factor_id, schema::org_transaction::columns::merchant_org_id, schema::org_transaction::columns::kgco2e_estimate, schema::org_transaction::columns::transaction_import_id, schema::org_transaction::columns::offsets_bill_id, schema::org_transaction::columns::category_id, schema::org_transaction::columns::included_in_offsets_bill_id, schema::org_transaction::columns::represented_offsets_bill_id, schema::org_transaction::columns::assessment_method, schema::org_transaction::columns::user_id, schema::org_transaction::columns::mask, schema::org_transaction::columns::provider_transaction_group_id, schema::org_transaction::columns::duplicate_tx_id)>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::Eq<schema::org_transaction::columns::org_id, expression::bound::Bound<diesel::sql_types::Text, &str>>>, expression::grouped::Grouped<expression::operators::IsNotNull<schema::org_transaction::columns::kgco2e_estimate>>>>, expression::grouped::Grouped<expression::operators::GtEq<schema::org_transaction::columns::initiated_at, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Date>, chrono::NaiveDate>>>>>>, query_builder::order_clause::OrderClause<expression::operators::Desc<schema::org_transaction::columns::initiated_at>>>`
   = note: required because of the requirements on the impl of `LoadQuery<'_, _, dal::org_transaction::OrgTransaction>` for `SelectStatement<FromClause<schema::org_transaction::table>, query_builder::select_clause::SelectClause<(schema::org_transaction::columns::id, schema::org_transaction::columns::org_id, schema::org_transaction::columns::service_connection_id, schema::org_transaction::columns::amount, schema::org_transaction::columns::currency_code, schema::org_transaction::columns::merchant_name, schema::org_transaction::columns::merchant_raw, schema::org_transaction::columns::mcc, schema::org_transaction::columns::transaction_type, schema::org_transaction::columns::provider_tx_id, schema::org_transaction::columns::provider_account_id, schema::org_transaction::columns::initiated_at, schema::org_transaction::columns::posted_at, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, std::option::Option<std::option::Option<std::string::String>>>, schema::org_transaction::columns::created_at, schema::org_transaction::columns::factor_id, schema::org_transaction::columns::merchant_org_id, schema::org_transaction::columns::kgco2e_estimate, schema::org_transaction::columns::transaction_import_id, schema::org_transaction::columns::offsets_bill_id, schema::org_transaction::columns::category_id, schema::org_transaction::columns::included_in_offsets_bill_id, schema::org_transaction::columns::represented_offsets_bill_id, schema::org_transaction::columns::assessment_method, schema::org_transaction::columns::user_id, schema::org_transaction::columns::mask, schema::org_transaction::columns::provider_transaction_group_id, schema::org_transaction::columns::duplicate_tx_id)>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::And<expression::grouped::Grouped<expression::operators::Eq<schema::org_transaction::columns::org_id, expression::bound::Bound<diesel::sql_types::Text, &str>>>, expression::grouped::Grouped<expression::operators::IsNotNull<schema::org_transaction::columns::kgco2e_estimate>>>>, expression::grouped::Grouped<expression::operators::GtEq<schema::org_transaction::columns::initiated_at, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Date>, chrono::NaiveDate>>>>>>, query_builder::order_clause::OrderClause<expression::operators::Desc<schema::org_transaction::columns::initiated_at>>>`
note: required by a bound in `diesel::RunQueryDsl::load`
  --> /Users/thomas/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-2.0.2/src/query_dsl/mod.rs:1499:15
   |
1499 |         Self: LoadQuery<'query, Conn, U>,
   |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `diesel::RunQueryDsl::load`

Some errors have detailed explanations: E0271, E0277.
For more information about an error, try `rustc --explain E0271`.
error: could not compile `api-service` due to 2 previous errors

[thomasmost]

I may try @weiznich's suggestion below, but I'd like to understand diesel/this error better too

[weiznich]

None::<Option<String>> creates an Option<Option<String>>. You likely want to use only None::<String>, which creates an Option<String>.

[thomasmost]

🤦🏼

[weiznich]

In addition the the already provided answer: You can always implement Queryable a second time manually for your struct. These impls shouldn't conflict as they would use tuples of different sizes as one of their generic arguments.

[thomasmost]

Idk if you can close discussions, but both methods work for me now (implementing Queryable with a subset of the sql columns, and selecting a raw value and converting it into sql)

[weiznich]

Discussions are not "closed", but you can mark one of the answers as the correct answers, so others don't need to read all of the thread.

[thomasmost]

Two ways of going about this:

1. Select a declared value and convert it to SQL

use diesel::{self, sql_types};
use diesel::prelude::*;

use crate::schema::item;

#[derive(AsChangeset, Debug, Insertable, Queryable)]
#[diesel(table_name = item)]
pub struct Item {
  pub id: String,
  pub name: String,
  pub raw_json: Option<String>,  // this is the field we want to exclude
  pub created_at: chrono::NaiveDateTime,
}

  pub fn read_items_by_ids(
    connection: &mut MysqlConnection,
    ids: Vec<&String>,
  ) -> Vec<Item> {
    item::table
      .select(
        item::id,
        item::name,
        None::<String>.into_sql::<sql_types::Nullable<sql_types::Text>>(),
        item::created_at,)
      .filter(item::id.eq_any(ids))
      .load::<Item>(connection)
      .expect("Error reading items")
  }

2. Implement Queryable using a tuple of SQL values without the excluded value

use diesel;
use diesel::prelude::*;
use diesel::sql_types::*;

use crate::schema::item;

#[derive(AsChangeset, Debug, Insertable)]
#[diesel(table_name = item)]
pub struct Item {
  pub id: String,
  pub name: String,
  pub raw_json: Option<String>,  // this is the field we want to exclude
  pub created_at: chrono::NaiveDateTime,
}

type SqlWithoutRawJson = (
  Char,
  Varchar,
  Timestamp,
);

impl Queryable<SqlWithoutRawJson, diesel::mysql::Mysql> for Item {
  type Row = (
    String,
    String,
    chrono::NaiveDateTime,
  );

  fn build(row: Self::Row) -> diesel::deserialize::Result<Self> {
    let (
      id,
      name,
      created_at,
    ) = row;
    Ok(Item {
      id,
      name,
      created_at,
      raw_json: None,
    })
  }
}

  pub fn read_items_by_ids(
    connection: &mut MysqlConnection,
    ids: Vec<&String>,
  ) -> Vec<Item> {
    item::table
      .select(
        item::id,
        item::name,
        item::created_at,
      )
      .filter(item::id.eq_any(ids))
      .load::<Item>(connection)
      .expect("Error reading items")
  }

Queryable has the added advantage of being a trait you can implement multiple times, for different sets of required fields to be selected

Thanks @weiznich and @Ten0 for the help; I wanted to summarize your answers/what worked for me here