PG Filters

A simple rust helper to generate postgres sql for pagination, sorting and filtering

Usage

    let filters = PgFilters::new(
        Some(PaginationOptions {
            current_page: 1,
            per_page: 10,
            per_page_limit: 10,
            total_records: 1000,
        }),
        vec![
            SortedColumn::new("age", "desc"),
            SortedColumn::new("name", "asc"),
        ],
        vec![
            FilteringRule::new("where", ColumnName::String("name"), "=", "John"),
            FilteringRule::new("or",    ColumnName::Int("age"),     ">", "18"),
        ],
    );

    let sql = filters.sql();
    assert_eq!(sql, " WHERE LOWER(name) = LOWER('John') OR age > 18 ORDER BY age DESC, name ASC LIMIT 10 OFFSET 0");

If you need to apply the filtering rules for pagination you can get the sql for that from the filter options

let filtering_rules: Vec<eyre::Result<FilteringRule>> = vec![FilteringRule::new("where", ColumnName::String("name"), "=", "John")];

let pagination_options = if filtering_rules.is_empty() {
  let total_rows = db.query_one(total_rows_select_statement.as_str(), &[]).await.map_err(|e| eyre::eyre!("Error getting total rows: {}", e))?;
  let total_records = total_rows.get::<usize, i64>(0);

  PaginationOptions::new(
    current_page as i64,
    per_page as i64,
    50,
    total_records as i64,
  )
} else {
  let filtering_options = FilteringOptions::new(filtering_rules);
  let filtering_sql = filtering_options.filtering.sql;
  let filtering_sql = format!(
    "select count(*) from {}", filtering_sql);

  let total_rows = db.query_one(filtering_sql.as_str(), &[]).await.map_err(|e| eyre::eyre!("Error getting total rows: {}", e))?;
  let total_records = total_rows.get::<usize, i64>(0);
  PaginationOptions::new(
    current_page as i64,
    per_page as i64,
    50,
    total_records as i64,
  )
}

Note

• filter rules are applied in the order which they are supplied
• sorting is applied after sorting on column name alphabetically (duplicates are removed)
• for readability on the first filtering rule you can use where - anything other than AND/OR defaults to AND

Valid Filtering Options

The filtering accepts a filter operator and a conditional operator the valid options are below:

Filtering Operator

can be upper or lower case

• "="
• "!="
• ">"
• ">="
• "<"
• "<="
• "LIKE"
• "NOT LIKE"
• "IN"
• "NOT IN"
• "IS NULL"
• "IS NOT NULL"
• "STARTS WITH"
• "ENDS WITH"

Valid Conditional Filter Values

can be upper or lower case

• "AND"
• "OR"

Returned Objects

Along with the sql it also returns objects containing the pagination, sorting and filtering that has been applied e.g :

  let pagination_sql = filters.pagination.sql
  let pagination = filters.pagination.pagination

  pub struct Paginate {
      pub pagination: Pagination,
      pub sql: String,
  }

  pub struct Pagination {
        current_page,
        previous_page,
        next_page,
        total_pages,
        per_page,
        total_records,
  }

see the tests for more examples

TODO

• Dates / Timestamps
• other Types as listed here

Changelog

See the Changelog

License

Licensed under either of these:

• MIT (https://opensource.org/licenses/MIT)
• Apache-2.0 (https://www.apache.org/licenses/LICENSE-2.0)