Home › API › SIMPLE_SQL_SELECT_BUILDER

SIMPLE_SQL_SELECT_BUILDER

Fluent builder for SELECT queries. Chain methods to construct complex queries without string concatenation.

Fluent API

All builder methods return like Current, allowing method chaining: db.select_from("users").columns("name").where("active = 1").limit(10).execute

Column Selection

columns (a_columns: READABLE_STRING_8): like Current

Set columns to select. Comma-separated list. Default is *.

distinct: like Current

Add DISTINCT keyword to eliminate duplicate rows.

Filtering (WHERE)

where (a_condition: READABLE_STRING_8): like Current

Add WHERE clause. Can include ? placeholders.

where (a_condition: READABLE_STRING_8; a_value: detachable ANY): like Current

Add WHERE with single parameter binding.

and_where (a_condition: READABLE_STRING_8): like Current

Add additional AND condition to WHERE clause.

or_where (a_condition: READABLE_STRING_8): like Current

Add additional OR condition to WHERE clause.

Soft Delete Scopes

active_only: like Current

Filter to non-deleted records (WHERE deleted_at IS NULL). Most common scope.

deleted_only: like Current

Filter to soft-deleted records only (WHERE deleted_at IS NOT NULL).

with_deleted: like Current

Include all records regardless of deletion status. No filter added.

set_soft_delete_column (a_column: READABLE_STRING_8)

Configure column name for soft delete detection. Default: deleted_at.

Soft Delete Convention

By default, soft delete scopes look for a deleted_at column (nullable timestamp). Use set_soft_delete_column if your schema uses a different column name.

Joins

join (a_table: READABLE_STRING_8; a_condition: READABLE_STRING_8): like Current

Add INNER JOIN.

left_join (a_table: READABLE_STRING_8; a_condition: READABLE_STRING_8): like Current

Add LEFT OUTER JOIN.

Ordering

order_by (a_columns: READABLE_STRING_8): like Current

Add ORDER BY clause. Supports multiple columns and ASC/DESC.

Grouping

group_by (a_columns: READABLE_STRING_8): like Current

Add GROUP BY clause for aggregations.

having (a_condition: READABLE_STRING_8): like Current

Add HAVING clause for filtering grouped results.

Pagination

limit (a_count: INTEGER): like Current

Limit number of rows returned.

offset (a_count: INTEGER): like Current

Skip first N rows. Use with limit for pagination.

Offset Pagination Performance

Using offset with large values is slow because SQLite must scan all skipped rows. For large datasets, use cursor-based pagination instead.

Execution

execute: SIMPLE_SQL_RESULT

Execute the query and return all results in memory.

to_sql: STRING_8

Generate SQL string without executing. Useful for debugging.

Examples

Basic Select

result := db.select_from ("users")
    .columns ("id, name, email")
    .where ("active = 1")
    .execute

With Soft Deletes

-- Only active (non-deleted) records
result := db.select_from ("documents")
    .active_only
    .order_by ("created_at DESC")
    .execute

-- Only deleted records (trash view)
result := db.select_from ("documents")
    .deleted_only
    .execute

-- All records including deleted (admin view)
result := db.select_from ("documents")
    .with_deleted
    .execute

Complex Query with Join

result := db.select_from ("orders")
    .columns ("orders.id, orders.total, users.name")
    .left_join ("users", "users.id = orders.user_id")
    .where ("orders.status = ?", "pending")
    .order_by ("orders.created_at DESC")
    .limit (20)
    .execute

Aggregation

result := db.select_from ("orders")
    .columns ("user_id, COUNT(*) as order_count, SUM(total) as total_spent")
    .group_by ("user_id")
    .having ("order_count > 5")
    .order_by ("total_spent DESC")
    .execute

Debug SQL

local
    builder: SIMPLE_SQL_SELECT_BUILDER
do
    builder := db.select_from ("users").where ("active = 1").limit (10)
    print ("SQL: " + builder.to_sql)
    -- Output: SELECT * FROM users WHERE active = 1 LIMIT 10
end