Where Model

Where Model Use cases

Single Model generates AND bounded where the condition

Plain simple Fields

#[derive(WhereDbModel)]
pub struct GetInputParam {
    pub client_id: String,
    pub key: String,
}

Generates into

WHERE client_id = $1 AND key = $2

Operators

Supported operators

• "=" - Equal;
• "!=" or "<>" - Checks if the values of two operands are equal or not, if values are not equal then the condition becomes true.
• ">" - Checks if the value of the left operand is greater than the value of the right operand, if yes then the condition becomes true.
• "<" - Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true.
• ">=" - Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes then the condition becomes true.
• "<=" - Checks if the value of the left operand is less than or equal to the value of the right operand, if yes then the condition becomes true.

#[derive(WhereDbModel)]
pub struct GetInputParam {
    #[operator(">")]
    pub from_amount: i64,
    #[operator("<")]
    pub to_amount: i64,
}

Generates into

WHERE from_amount > $1 AND to_amount < $2

In Operator

#[derive(WhereDbModel)]
pub struct GetInputParam {
    #[operator(">")]
    pub from_amount: i64,
    #[operator("<")]
    pub to_amount: i64,
    pub status: Vec<i32>
}

Generates into

WHERE from_amount > $1 AND to_amount < $2 AND status IN ($3, $4, $5, ...)

If property status is created empty then result is rendered as:

WHERE from_amount > $1 AND to_amount < $2

Using the Option of Value

None value makes where statement renderer skipping the value at all;

#[derive(WhereDbModel)]
pub struct GetInputParam {
    #[operator(">")]
    pub from_amount: i64,
    #[operator("<")]
    pub to_amount: Option<i64>,

}

let where = GetInputParam { 
  from_amount: 5, 
  to_amount : None
}

Generates into

WHERE from_amount > $1

But

#[derive(WhereDbModel)]
pub struct GetInputParam {
    #[operator(">")]
    pub from_amount: i64,
    #[operator("<")]
    pub to_amount: Option<i64>,

}

let where = GetInputParam { 
  from_amount: 5, 
  to_amount : Some(6)
}

Generates into

WHERE from_amount > $1 AND to_amount < $2

Limit and Offset

Attributes #[limit] and #[offset] can be used.

Option type is applicable to skip rendering the skip or offset statement;

#[derive(WhereDbModel)]
pub struct GetInputParam {
    pub asset_id: String,

    #[limit]
    pub limit: usize,

    #[offset]
    pub offset: usize,

}

Generates into

SQL field FROM {TABLE_NAME} LIMIT {$1} OFFSET {2}

IS_NULL and IS_NOT_NULL

For where Model

#[derive(WhereDbModel)]
pub struct GetInputParam {
    pub asset_id: IsNull,
}

Generates into

SQL field FROM {TABLE_NAME} LIMIT {$1} OFFSET {2}

NoneWhereModel

If we have to proved Option::None as a Where Parameter to get_rows() case and overcome the lack of type - NoneWhereModel is introduced.

let result: Result<Vec<MyEntity>,_> = postgres.query_rows("table_name", NoneWhereModel::new()).await?;

where_raw_model

Gives Ability to write Where part of the sql request with values of model

#[where_raw_model("Content=${field_1} AND Content2=${field_2} AND Content3 in ${field_3}")]
pub struct WhereRawModel {
    pub field_1: String,
    pub field_2: bool,
    pub field_3: Vec<i32>,
}

generates where component of SQL such as

  Content=$1 AND Content2=true AND Content3 in (1,2,3)

Json Fields inside where model

Json field is going to generate where condition considering the matching all the fields inside the json model inside the column

#[derive(Serialize, Deserialize, MyPostgresJsonModel)]
pub struct JsonTestField {
    pub key: String,
    pub value: i32,
}

#[derive(WhereDbModel)]
pub struct WhereWithJsonField {
    pub field_before: String,
    #[db_column_name("my_json_field")]
    pub json_field: JsonTestField,
    pub field_after: bool,
}

Would Generate SQL like

field_before=$1 AND ("my_json_field"->>'key'=$2 AND "my_json_field"->>'value'=5) AND field_after=true

Dynamic json Fields inside where model

#[derive(WhereDbModel)]
pub struct WhereWithDynamicJsonValues {
    pub value_before: i32,
    #[db_column_name("my_dynamic_json")]
    pub dynamic_json: BTreeMap<String, String>,
    pub value_after: bool,
}

would generate code like

value_before=1 AND (\"my_dynamic_json\"->>'json_field'=$1 AND \"my_dynamic_json\"->>'json_field2'=$2) AND value_after=true

#[inside_json] attribute.

#[derive(Debug, WhereDbModel)]
pub struct WhereJsonField {
    #[inside_json("db_column_name.sub_field")]
    pub json_prop: String,
}

Generates SQL

"db_column_name"->>'sub_field'->>'json_prop'=$1

$[where_raw_model]

#[where_raw_model("field_1_column = ${field_1_val} OR field_2_column = ${field_2_val}")]
pub struct WhereRaw {
    pub field_1_val: String,
    pub field_2_val: String,
}

StaticLineWhereModel

If there is a case there need to have a static where line at where part of the SQL request

  let where_model = StaticLineWhereModel::new("NOT starts_with(table_name, '_')");

  self.postgres
      .query_rows("information_schema.tables", Some(&where_model))
      .await