PG Filters

一个简单的Rust辅助工具，用于生成PostgreSQL的SQL语句以实现分页、排序和过滤

用法

    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");

如果您需要应用分页的过滤规则，您可以从过滤选项中获取相应的SQL语句

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,
  )
}

注意

• 过滤规则按提供的顺序应用
• 排序在按列名字母顺序排序之后应用（重复项将被删除）
• 为了在第一条过滤规则上提高可读性，您可以使用 where - 除了 AND/OR 之外的所有内容都默认为 AND

有效的过滤选项

过滤接受过滤运算符和条件运算符，以下为有效选项

过滤运算符

可以是大写或小写

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

有效的条件过滤值

可以是大写或小写

• "AND"
• "OR"

返回的对象

除了SQL之外，它还返回包含已应用的分页、排序和过滤的对象，例如

  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,
  }

请参阅测试以获取更多示例

待办事项

• 日期/时间戳
• 此处列出的其他类型

变更日志

查看变更日志

许可证

许可以下任何一个

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