Ralph::Query::Builder

Check if query caching is enabled

Get cache statistics

Returns statistics about cache hits, misses, size, evictions, etc.

Example

stats = Ralph::Query::Builder.cache_stats
puts "Hits: #{stats.hits}, Misses: #{stats.misses}, Hit rate: #{stats.hit_rate}"

Clear all cached query results (class method)

Disable query caching (clears all cached entries)

Enable query caching

Invalidate cache entries for a specific table

This should be called after INSERT, UPDATE, or DELETE operations

Get all arguments including from subqueries (for parameterized execution)

Combine this query's WHERE clauses with another query's using AND (returns new Builder)

This is useful when you want explicit grouping of conditions. Normal chained .where() calls already use AND, but this method allows you to group conditions for clarity or when building dynamic queries.

Example:

query1 = Ralph::Query::Builder.new("users")
  .where("age > ?", 18)

query2 = Ralph::Query::Builder.new("users")
  .where("role = ?", "admin")
  .where("department = ?", "engineering")

combined = query1.and(query2)
# => WHERE (age > $1) AND (role = $2 AND department = $3)

Build an AVG query

Build a COUNT query

Build the DELETE query

Build the INSERT query

Build a MAX query

Build a MIN query

Build the SELECT query

Build the SELECT query with parameter offset (for subqueries) Returns the SQL string and the next parameter index to use

Build a SUM query

Build the UPDATE query

Mark this query for caching (returns new Builder)

When a query is marked for caching, subsequent executions with the same SQL and parameters will return cached results instead of hitting the database.

Example

# Cache with default TTL
query = Ralph::Query::Builder.new("users")
  .where("active = ?", true)
  .cache

# Cache with custom TTL
query = Ralph::Query::Builder.new("users")
  .where("active = ?", true)
  .cache(ttl: 10.minutes)

Generate a cache key based on SQL and parameters

Store results in cache

Get the cache TTL for this query

Check if results are cached for this query

Clear cached result for this specific query

Cross join (no ON clause)

Add DENSE_RANK() window function

Example:

query.dense_rank(partition_by: "department", order_by: "salary DESC", as: "dense_rank")

Add DISTINCT to SELECT (returns new Builder)

Add DISTINCT ON specific columns (returns new Builder)

Create a copy of this builder with all state duplicated

Add an EXCEPT operation with another query (returns new Builder)

EXCEPT returns rows from the first query that don't appear in the second.

Example:

all_users = Ralph::Query::Builder.new("users")
  .select("id")
  .where("active = ?", true)

banned_users = Ralph::Query::Builder.new("users")
  .select("id")
  .where("banned = ?", true)

non_banned = all_users.except(banned_users)
# => SELECT id FROM users WHERE active = $1 EXCEPT SELECT id FROM users WHERE banned = $2

Add a WHERE EXISTS clause (returns new Builder)

Example:

subquery = Ralph::Query::Builder.new("orders")
  .select("1")
  .where("orders.user_id = users.id")
  .where("status = ?", "pending")

query = Ralph::Query::Builder.new("users")
  .exists(subquery)

Add a FOR SHARE lock to the query (returns new Builder)

This acquires a shared row-level lock on selected rows, allowing other transactions to read but not modify or lock them.

Options

• No argument: Basic FOR SHARE
• :nowait - Fail immediately if lock cannot be acquired
• :skip_locked - Skip rows that are already locked

Example

# Basic FOR SHARE
User.query { |q| q.where("id = ?", 1).for_share }
# => SELECT * FROM "users" WHERE id = $1 FOR SHARE

# With SKIP LOCKED
User.query { |q| q.where("active = ?", true).for_share(:skip_locked) }
# => SELECT * FROM "users" WHERE active = $1 FOR SHARE SKIP LOCKED

Add a FOR UPDATE lock to the query (returns new Builder)

This acquires an exclusive row-level lock on selected rows, preventing other transactions from modifying or locking them.

Options

• No argument: Basic FOR UPDATE
• :nowait - Fail immediately if lock cannot be acquired
• :skip_locked - Skip rows that are already locked

Example

# Basic FOR UPDATE
User.query { |q| q.where("id = ?", 1).for_update }
# => SELECT * FROM "users" WHERE id = $1 FOR UPDATE

# With NOWAIT - don't wait for locks
User.query { |q| q.where("id = ?", 1).for_update(:nowait) }
# => SELECT * FROM "users" WHERE id = $1 FOR UPDATE NOWAIT

# With SKIP LOCKED - skip locked rows
User.query { |q| q.where("active = ?", true).for_update(:skip_locked) }
# => SELECT * FROM "users" WHERE active = $1 FOR UPDATE SKIP LOCKED

Add a FROM subquery (returns new Builder)

Example:

subquery = Ralph::Query::Builder.new("orders")
  .select("user_id", "SUM(total) as total_spent")
  .group("user_id")

query = Ralph::Query::Builder.new("users")
  .from_subquery(subquery, "order_totals")
  .where("total_spent > ?", 1000)

Full join (alias for full_outer_join)

Full outer join

Add a GROUP BY clause (returns new Builder)

Check if the query has conditions

Add a HAVING clause (returns new Builder)

Inner join (alias for join)

Add an INTERSECT operation with another query (returns new Builder)

INTERSECT returns only rows that appear in both result sets.

Example:

active_users = Ralph::Query::Builder.new("users")
  .select("id")
  .where("active = ?", true)

premium_users = Ralph::Query::Builder.new("users")
  .select("id")
  .where("subscription = ?", "premium")

both = active_users.intersect(premium_users)
# => SELECT id FROM users WHERE active = $1 INTERSECT SELECT id FROM users WHERE subscription = $2

Join another table (returns new Builder)

Join an association dynamically by name

This method is similar to Model.join_assoc but can be chained on an existing query builder.

Example:

User.query.join_assoc(:posts, :left).where("posts.title = ?", "Hello")

Left join

Add a LIMIT clause (returns new Builder)

Add a custom lock clause to the query (returns new Builder)

This is the most flexible lock method, allowing any lock mode and option.

Lock Modes

• :update - FOR UPDATE (exclusive lock)
• :share - FOR SHARE (shared lock)
• :no_key_update - FOR NO KEY UPDATE (PostgreSQL)
• :key_share - FOR KEY SHARE (PostgreSQL)

Options

• :nowait - Fail immediately if lock cannot be acquired
• :skip_locked - Skip rows that are already locked

Example

# FOR UPDATE with specific tables
query.lock(:update, tables: ["users", "orders"])
# => SELECT ... FOR UPDATE OF "users", "orders"

# FOR NO KEY UPDATE (PostgreSQL - allows concurrent inserts)
query.lock(:no_key_update)
# => SELECT ... FOR NO KEY UPDATE

# FOR KEY SHARE (PostgreSQL - weakest lock)
query.lock(:key_share, option: :skip_locked)
# => SELECT ... FOR KEY SHARE SKIP LOCKED

Add a raw lock clause string (returns new Builder)

For database-specific locking syntax not covered by the standard methods.

Example

query.lock_raw("FOR UPDATE OF users NOWAIT")
# => SELECT ... FOR UPDATE OF users NOWAIT

Merge another query's clauses into this one (returns new Builder)

This copies WHERE, ORDER, LIMIT, OFFSET, and other clauses from the other builder into this one. Useful for combining scope conditions.

Example:

base_query = Ralph::Query::Builder.new("users")
  .where("active = ?", true)

additional = Ralph::Query::Builder.new("users")
  .where("age > ?", 18)
  .order("name", :asc)

merged = base_query.merge(additional)
# Adds the WHERE and ORDER clauses from additional

Add a WHERE NOT EXISTS clause (returns new Builder)

Example:

subquery = Ralph::Query::Builder.new("orders")
  .select("1")
  .where("orders.user_id = users.id")

query = Ralph::Query::Builder.new("users")
  .not_exists(subquery)

Add an OFFSET clause (returns new Builder)

Combine this query's WHERE clauses with another query's using OR (returns new Builder)

This creates a combined condition where either set of conditions can match. The current query's WHERE clauses become the left side, and the other query's WHERE clauses become the right side.

Example:

query1 = Ralph::Query::Builder.new("users")
  .where("age > ?", 18)
  .where("active = ?", true)

query2 = Ralph::Query::Builder.new("users")
  .where("role = ?", "admin")

combined = query1.or(query2)
# => WHERE (age > $1 AND active = $2) OR (role = $3)

Add an ORDER BY clause (returns new Builder)

Order by full-text search rank (relevance score)

Higher rank = more relevant match. Must be used with a search query.

Example

query.where_search("title", "crystal")
  .order_by_search_rank("title", "crystal")
# Orders results by relevance to "crystal"

Normalization Options (via normalization parameter)

• 0: Default (ignores document length)
• 1: Divides rank by 1 + document length logarithm
• 2: Divides rank by document length
• 4: Divides rank by mean harmonic distance between extents
• 8: Divides rank by number of unique words
• 16: Divides rank by 1 + document length logarithm (different formula)
• 32: Divides rank by document length + 1

Combine with bitwise OR: normalization: 1 | 4

Order by full-text search rank with cover density

Similar to order_by_search_rank but also considers proximity of search terms. Uses ts_rank_cd which gives higher scores when matching terms are closer together.

Example

query.where_search("content", "crystal programming")
  .order_by_search_rank_cd("content", "crystal programming")

Add RANK() window function

Example:

query.rank(partition_by: "department", order_by: "salary DESC", as: "salary_rank")

Reset the query builder (returns new empty Builder with same table)

Right join

Add ROW_NUMBER() window function

Example:

query.row_number(partition_by: "department", order_by: "salary DESC", as: "rank")

Select specific columns from an array (returns new Builder)

Select specific columns (returns new Builder)

Aggregate values into an array

Example

query.group("user_id").select_array_agg("tag", as: "tags")
# SQL: SELECT array_agg("tag") AS "tags" FROM ... GROUP BY user_id

Append element to array (for use in UPDATE)

Returns an expression that can be used with raw SQL.

Get array element at index (1-based in PostgreSQL)

Remove element from array (for use in UPDATE)

Select CURRENT_TIMESTAMP

Select date-truncated column

Select extracted date/time component

Aggregate into JSON array

Example

query.group("user_id").select_json_agg("order_id", as: "order_ids")
# SQL: SELECT json_agg("order_id") AS "order_ids"

Build JSON object from key-value pairs

Example

query.select_json_build_object({"name" => "name", "email" => "email"}, as: "user_info")
# SQL: SELECT json_build_object('name', "name", 'email', "email") AS "user_info"

Aggregate into JSONB array

Select string length

Select lowercase column

Calculate median (50th percentile)

Calculate mode (most common value)

Example

query.select_mode("rating", as: "most_common_rating")
# SQL: SELECT mode() WITHIN GROUP (ORDER BY "rating") AS "most_common_rating"

Select NOW() as a column

Example

query.select_now("server_time")
# SQL: SELECT NOW() AS "server_time"

Calculate percentile (continuous)

Example

query.select_percentile("response_time", 0.95, as: "p95")
# SQL: SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY "response_time") AS "p95"

Calculate percentile (discrete - returns actual value from dataset)

Select gen_random_uuid() as a column

Generates a random UUID v4.

Example

query.select_random_uuid("new_id")
# SQL: SELECT gen_random_uuid() AS "new_id"

String replacement

Example

query.select_replace("email", "@example.com", "@test.com", as: "test_email")
# SQL: SELECT replace("email", '@example.com', '@test.com') AS "test_email"

Select search headline (highlighted excerpt with matching terms)

Generates a short excerpt with search terms highlighted using HTML tags.

Example

query.where_search("content", "crystal")
  .select_search_headline("content", "crystal")
# Returns content like: "Learn about <b>Crystal</b> programming language"

Options

• max_words: Maximum words in headline (default: 35)
• min_words: Minimum words in headline (default: 15)
• short_word: Ignore words shorter than this (default: 3)
• highlight_all: Highlight all occurrences, not just best (default: false)
• max_fragments: Maximum number of excerpts (default: 0 = unlimited)
• start_tag: Opening tag for highlights (default: "")
• stop_tag: Closing tag for highlights (default: "")
• fragment_delimiter: Text between fragments (default: " ... ")

Aggregate strings with delimiter

Example

query.group("category_id").select_string_agg("name", ", ", order_by: "name", as: "names")
# SQL: SELECT string_agg("name", ', ' ORDER BY "name") AS "names"

Select substring

Unnest array (expand to rows)

Example

# Expand tags array into individual rows
query.select_unnest("tags", as: "tag")

Select uppercase column

Expose table for subquery introspection

Disable caching for this query (returns new Builder)

Add a UNION operation with another query (returns new Builder)

UNION removes duplicate rows from the combined result set.

Example:

active_users = Ralph::Query::Builder.new("users")
  .select("id", "name")
  .where("active = ?", true)

premium_users = Ralph::Query::Builder.new("users")
  .select("id", "name")
  .where("subscription = ?", "premium")

combined = active_users.union(premium_users)
# => SELECT id, name FROM users WHERE active = $1 UNION SELECT id, name FROM users WHERE subscription = $2

Add a UNION ALL operation with another query (returns new Builder)

UNION ALL keeps all rows including duplicates (faster than UNION).

Example:

recent_orders = Ralph::Query::Builder.new("orders")
  .select("id", "total")
  .where("created_at > ?", last_week)

large_orders = Ralph::Query::Builder.new("orders")
  .select("id", "total")
  .where("total > ?", 1000)

combined = recent_orders.union_all(large_orders)
# => SELECT id, total FROM orders WHERE created_at > $1 UNION ALL SELECT id, total FROM orders WHERE total > $2

Add a WHERE clause (returns new Builder) Supports arrays for IN clauses - when an array is passed, the corresponding ? placeholder is expanded to (?, ?, ...) with one placeholder per array element.

Add a WHERE clause with a block (returns new Builder)

Compare column age (interval since timestamp)

The age() function calculates the interval between now and the given timestamp.

Example

# Find records created more than 7 days ago
query.where_age("created_at", ">", "7 days")

# Find records updated within the last hour
query.where_age("updated_at", "<", "1 hour")

Interval Format

PostgreSQL interval format: '1 year 2 months 3 days 4 hours 5 minutes 6 seconds' Also accepts: '1 week', '30 days', '2 hours', etc.

Convenience methods for common age comparisons

Get the WHERE clause arguments

Check if an array column is contained by the given values

Example:

query.where_array_contained_by("tags", ["crystal", "ruby", "elixir"])
# PostgreSQL: WHERE "tags" <@ ARRAY['crystal', 'ruby', 'elixir']

Check if an array column contains a specific value

Example:

query.where_array_contains("tags", "crystal")
# PostgreSQL: WHERE 'crystal' = ANY("tags")
# SQLite: WHERE EXISTS (SELECT 1 FROM json_each("tags") WHERE value = 'crystal')

Check if array contains all specified elements

Example

query.where_array_contains_all("tags", ["crystal", "orm"])
# SQL: WHERE "tags" @> ARRAY['crystal', 'orm']

Check if array is contained by another array

All elements in the column must be present in the given values.

Check the length of an array column

Example:

query.where_array_length("tags", ">", 3)
# PostgreSQL: WHERE array_length("tags", 1) > 3
# SQLite: WHERE json_array_length("tags") > 3

Check if an array column overlaps with the given values (has any common elements)

Example:

query.where_array_overlaps("tags", ["crystal", "ruby"])
# PostgreSQL: WHERE "tags" && ARRAY['crystal', 'ruby']
# SQLite: Emulated with json_each

Compare column to NOW()

Example

query.where_before_now("expires_at")
# SQL: WHERE "expires_at" < NOW()

query.where_after_now("start_date")
# SQL: WHERE "start_date" > NOW()

Array cardinality (length) using cardinality() function

Works correctly with multi-dimensional arrays (returns total elements).

String concatenation comparison

Example

query.where_concat("first_name", "last_name", "John Doe")
# SQL: WHERE "first_name" || ' ' || "last_name" = 'John Doe'

Compare column to CURRENT_TIMESTAMP

CURRENT_TIMESTAMP is SQL standard and returns the same value throughout a transaction. NOW() is PostgreSQL-specific and also returns a constant within a transaction.

Date truncation (round down to precision)

Truncates a timestamp to the specified precision level.

Supported Precisions

microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium

Example

# Find records created on a specific day
query.where_date_trunc("day", "created_at", "2024-01-15")

# Group by month
query.select_date_trunc("month", "created_at", as: "month").group("month")

Ends with comparison

Extract date/time component

Extracts a specific part from a timestamp.

Supported Parts

century, day, decade, dow (day of week), doy (day of year), epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year

Example

# Find records from 2024
query.where_extract("year", "created_at", 2024)

# Find records from January
query.where_extract("month", "created_at", 1)

Case-insensitive LIKE (PostgreSQL ILIKE)

Example

query.where_ilike("name", "%john%")
# Matches "John", "JOHN DOE", "johnny", etc.

Add a WHERE IN clause with a subquery (returns new Builder)

Example:

subquery = Ralph::Query::Builder.new("orders")
  .select("user_id")
  .where("total > ?", 100)

query = Ralph::Query::Builder.new("users")
  .where_in("id", subquery)

Add a WHERE IN clause with an array of values (returns new Builder)

Example:

query = Ralph::Query::Builder.new("users")
  .where_in("id", [1, 2, 3])

Extract a JSON value at the given path and compare it

Uses backend-specific syntax:

• PostgreSQL: column->>'path' = ?
• SQLite: json_extract(column, '$.path') = ?

Example:

query.where_json("settings", "theme", "dark")
# PostgreSQL: WHERE "settings"->>'theme' = 'dark'
# SQLite: WHERE json_extract("settings", '$.theme') = 'dark'

Check if a JSON column contains the given value (for JSONB in PostgreSQL)

Example:

query.where_json_contains("tags", "[\"crystal\", \"orm\"]")
# PostgreSQL: WHERE "tags" @> '["crystal", "orm"]'
# SQLite: Uses json_each for emulation

Check if a JSON column contains the given key

Example:

query.where_json_has_key("metadata", "theme")
# PostgreSQL: WHERE "metadata" ? 'theme'
# SQLite: WHERE json_extract("metadata", '$.theme') IS NOT NULL

String length comparison

Example

query.where_length("name", ">", 5)
# SQL: WHERE length("name") > 5

Convert to lowercase comparison

Example

query.where_lower("email", "test@example.com")
# SQL: WHERE lower("email") = 'test@example.com'

Add a WHERE NOT clause (returns new Builder)

Case-insensitive NOT LIKE

Add a WHERE NOT IN clause with a subquery (returns new Builder)

Example:

subquery = Ralph::Query::Builder.new("blacklisted_users")
  .select("user_id")

query = Ralph::Query::Builder.new("users")
  .where_not_in("id", subquery)

Add a WHERE NOT IN clause with an array of values (returns new Builder)

Example:

query = Ralph::Query::Builder.new("users")
  .where_not_in("id", [1, 2, 3])

Regular expression not match (case-sensitive)

Case-insensitive regular expression not match

Compare column to NOW() with custom operator

Example

query.where_now("updated_at", ">=")
# SQL: WHERE "updated_at" >= NOW()

Full-text search using phrase matching (PostgreSQL 9.6+)

Matches exact phrases where words must appear consecutively.

Example

query.where_phrase_search("content", "web framework")
# Only matches "web framework", not "web application framework"

Regular expression match (case-sensitive)

Uses PostgreSQL's ~ operator for POSIX regex matching.

Example

# Match usernames that start with a letter and contain only alphanumerics
query.where_regex("username", "^[a-zA-Z][a-zA-Z0-9_]*$")

# Match email pattern
query.where_regex("email", "^[^@]+@[^@]+\\.[^@]+$")

Case-insensitive regular expression match

Uses PostgreSQL's ~* operator.

Example

query.where_regex_i("name", "john")
# Matches "John", "JOHN", "john", etc.

Basic full-text search using @@ operator

Uses plainto_tsquery for simple search (automatically tokenizes). Supports any PostgreSQL text search configuration.

Example

query.where_search("title", "crystal orm")
# SQL: WHERE to_tsvector('english', "title") @@ plainto_tsquery('english', 'crystal orm')

query.where_search("content", "programming", config: "simple")
# SQL: WHERE to_tsvector('simple', "content") @@ plainto_tsquery('simple', 'programming')

Language Configurations

Common configs: 'english', 'simple', 'french', 'german', 'spanish', etc. Use PostgresBackend#available_text_search_configs to list all available configs.

Backend Requirement

Raises Ralph::BackendError if not using PostgreSQL backend.

Multi-column full-text search

Combines multiple columns into a single tsvector for searching. NULL values are safely handled with coalesce.

Example

query.where_search_multi(["title", "content"], "ruby framework")
# SQL: WHERE to_tsvector('english', coalesce("title", '') || ' ' || coalesce("content", ''))
#        @@ plainto_tsquery('english', 'ruby framework')

Starts with comparison (uses efficient index if available)

Example

query.where_starts_with("name", "John")
# SQL: WHERE "name" LIKE 'John%'

Substring comparison

Example

query.where_substring("code", 1, 3, "ABC")
# SQL: WHERE substring("code" from 1 for 3) = 'ABC'

Trim whitespace comparison

Convert to uppercase comparison

Full-text search using websearch_to_tsquery (PostgreSQL 11+)

Parses search queries using web search syntax:

• Unquoted words are combined with AND
• "quoted phrases" are treated as phrases
• OR connects alternatives
• -word excludes words

Example

query.where_websearch("content", "crystal -ruby \"web framework\"")
# Finds documents with "crystal" AND "web framework" but NOT "ruby"

Filter by date range relative to now

Example

# Records from the last 7 days
query.where_within_last("created_at", "7 days")

# Records from the last 2 hours
query.where_within_last("updated_at", "2 hours")

Expose fields for query composition

Add a window function to the SELECT clause (returns new Builder)

Supports common window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), COUNT(), MIN(), MAX(), LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(), etc.

Example:

query = Ralph::Query::Builder.new("employees")
  .select("name", "department", "salary")
  .window("ROW_NUMBER()", partition_by: "department", order_by: "salary DESC", as: "rank")
# => SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" FROM employees

Add AVG() window function

Example:

query.window_avg("salary", partition_by: "department", as: "dept_avg")

Add COUNT() window function

Example:

query.window_count(partition_by: "department", as: "dept_count")

Add SUM() window function

Example:

query.window_sum("salary", partition_by: "department", as: "dept_total")

Add a CTE (Common Table Expression) (returns new Builder)

Example:

subquery = Ralph::Query::Builder.new("orders")
  .select("user_id", "total")
  .where("status = ?", "completed")

query.with_cte("recent_orders", subquery)
  .where("user_id IN (SELECT user_id FROM recent_orders)")

Add a recursive CTE (returns new Builder)

Example:

# Base case: root categories
base = Ralph::Query::Builder.new("categories")
  .select("id", "name", "parent_id")
  .where("parent_id IS NULL")

# Recursive case: children
recursive = Ralph::Query::Builder.new("categories")
  .select("c.id", "c.name", "c.parent_id")
  .join("category_tree", "categories.parent_id = category_tree.id")

query.with_recursive_cte("category_tree", base, recursive)