Implement Comprehensive SELECT Query Builder & Operators (PostgreSQL)

[anitnilay20]

Labels: enhancement, core, database

Description

We need to flesh out the Select query builder to support the full range of standard SQL SELECT capabilities, with specific parity for PostgreSQL features.

The goal is to allow users to construct complex queries using a fluent Rust API that mirrors the structure of the underlying SQL while maintaining type safety. The syntax should be approachable, similar to Python's sqlmodel.

Reference Syntax:

let users = User::select()
    .filter(User::active().eq(true))
    .order_by(User::created_at().desc())
    .limit(10)
    .fetch_all(&db)
    .await?;

---

1. Basic Selection & Clauses

• Projection (SELECT):

• Default to * (all fields) if not specified.

• Support selecting specific columns: .select_only(&[User::id(), User::name()]).

• Distinct (DISTINCT):

• .distinct(): Removes duplicate rows.

• .distinct_on(User::column()): (Postgres) Keep the first row of each set of rows where the given expressions evaluate to equal.

• Limit & Offset:

• .limit(n: i64)

• .offset(n: i64)

2. Filtering (WHERE)

The .filter() method should accept expressions generated by column helper methods. We need to implement the following traits/methods on Column types.

Comparison Operators

• Equality: .eq(val), .ne(val)
• Numeric: .gt(val), .gte(val), .lt(val), .lte(val)
• Between: .between(low, high), .not_between(low, high)

Pattern Matching (String)

• Standard: .like("%pattern%"), .not_like("%pattern%")
• Case Insensitive (Postgres): .ilike("%pattern%"), .not_ilike("%pattern%")
• Convenience Helpers:
• .starts_with(str) (Compiles to LIKE 'str%')
• .ends_with(str) (Compiles to LIKE '%str')
• .contains(str) (Compiles to LIKE '%str%')

Nullability

• Checks: .is_null(), .is_not_null()

Set Operations

• Inclusion: .is_in(vec![...]), .is_not_in(vec![...])

Logical Combinators

The .filter() method should implicitly AND multiple calls. We also need explicit combinators for complex logic within a single filter.

• AND: .and(expr)
• OR: .or(expr) (e.g., User::age().lt(10).or(User::age().gt(60)))
• NOT: not(expr)

3. Ordering (ORDER BY)

• Direction:

• .asc(): ORDER BY col ASC

• .desc(): ORDER BY col DESC

• Null Handling (Postgres):

• .nulls_first()

• .nulls_last()

• Chaining: Support multiple order clauses (e.g., .order_by(User::role().asc()).order_by(User::name().desc())).

4. Grouping & Aggregation (GROUP BY / HAVING)

• Group By: .group_by(User::column())
• Having: .having(expr) (Applies filters after grouping).
• Aggregate Functions (To be implemented on Columns):
• count(), sum(), avg(), min(), max()

5. Joins (JOIN)

Drafting initial support for joining other models.

• Inner Join: .join::<OtherModel>(on_expr)
• Left Join: .left_join::<OtherModel>(on_expr)
• Right Join: .right_join::<OtherModel>(on_expr)
• Full Outer Join: .full_join::<OtherModel>(on_expr)

6. Locking (FOR UPDATE)

Essential for transactional integrity.

• Locking:
• .for_update()
• .for_no_key_update()
• .skip_locked()
• .nowait()

7. Execution Methods

Finalisers that execute the built query.

• .fetch_one(&db): Returns Result<T, Error>. Errors if 0 or >1 rows found.
• .fetch_optional(&db): Returns Result<Option<T>, Error>. Returns None if 0 rows.
• .fetch_all(&db): Returns Result<Vec<T>, Error>.
• .stream(&db): Returns impl Stream<Item = Result<T, Error>> (Async iteration).

---

Implementation Details & Questions

• Parameter Binding: Ensure all values passed to .eq(), .gt(), etc., are strictly parameterised ($1, $2) to prevent SQL injection.
• Expression Tree: How do we represent the WHERE clause internally? (Likely an AST enum: And(Box<Expr>, Box<Expr>), Eq(Col, Val)).
• Type Safety: Can we ensure at compile time that .ilike is only available on String columns and .gt only on Numeric/Date columns?