Skip to content

Latest commit

 

History

History
141 lines (109 loc) · 4.11 KB

README.md

File metadata and controls

141 lines (109 loc) · 4.11 KB

PG Filters

License Docs Test Coverage Status Crates

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

Changelog

See the Changelog

License

Licensed under either of these: