While ORMs provide convenient abstractions, sometimes you need the power and flexibility of raw SQL. Ruby provides excellent tools for executing SQL directly, giving you full control over database operations.
# Raw SQL is useful for:
# - Complex queries that are difficult to express in ORM
# - Performance-critical operations
# - Database-specific features
# - Data migration and ETL processes
# - Reporting and analytics queries
# - Working with views, stored procedures, and functionsThe execute method is the primary way to run SQL statements. It's available in most Ruby database libraries.
require 'sqlite3'
# SQLite3 example
db = SQLite3::Database.new('example.db')
db.results_as_hash = true
# Create tables
db.execute <<~SQL
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
department TEXT,
salary DECIMAL(10,2),
hire_date DATE,
active BOOLEAN DEFAULT 1
)
SQL
db.execute <<~SQL
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER REFERENCES users(id),
product TEXT NOT NULL,
amount DECIMAL(10,2),
order_date DATE,
status TEXT DEFAULT 'pending'
)
SQL
# Insert sample data
db.execute "INSERT INTO users (name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?)",
['Alice Johnson', 'alice@company.com', 'Engineering', 75000, '2022-01-15']
db.execute "INSERT INTO users (name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?)",
['Bob Smith', 'bob@company.com', 'Sales', 65000, '2021-03-10']
db.execute "INSERT INTO users (name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?)",
['Carol Davis', 'carol@company.com', 'Engineering', 80000, '2020-08-22']
# Insert orders
orders_data = [
[1, 'Laptop', 1200.00, '2023-01-10', 'completed'],
[2, 'Mouse', 25.00, '2023-01-12', 'completed'],
[1, 'Keyboard', 150.00, '2023-01-15', 'pending'],
[3, 'Monitor', 300.00, '2023-01-18', 'completed']
]
orders_data.each do |order|
db.execute "INSERT INTO orders (user_id, product, amount, order_date, status) VALUES (?, ?, ?, ?, ?)", order
end
puts "Sample data inserted successfully"
db.closerequire 'sqlite3'
class SQLExecutor
def initialize(database_path)
@db = SQLite3::Database.new(database_path)
@db.results_as_hash = true
end
def execute_query(sql, params = [])
begin
if params.empty?
@db.execute(sql)
else
@db.execute(sql, params)
end
rescue SQLite3::Exception => e
puts "SQL Error: #{e.message}"
puts "SQL: #{sql}"
puts "Params: #{params.inspect}"
[]
end
end
def execute_single(sql, params = [])
result = execute_query(sql, params)
result.first
end
def execute_scalar(sql, params = [])
result = execute_single(sql, params)
result ? result.values.first : nil
end
def close
@db.close
end
end
executor = SQLExecutor.new('example.db')
# Complex aggregation query
sales_report = executor.execute_query(<<~SQL)
SELECT
u.department,
COUNT(o.id) as order_count,
SUM(o.amount) as total_sales,
AVG(o.amount) as average_order,
MAX(o.amount) as largest_order,
MIN(o.amount) as smallest_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.department
ORDER BY total_sales DESC
SQL
puts "=== Sales Report by Department ==="
sales_report.each do |row|
puts "Department: #{row['department']}"
puts " Orders: #{row['order_count']}"
puts " Total Sales: $#{row['total_sales']}"
puts " Average Order: $#{'%.2f' % row['average_order']}"
puts " Range: $#{row['smallest_order']} - $#{row['largest_order']}"
puts
end
# Window functions (advanced SQL)
user_rankings = executor.execute_query(<<~SQL)
SELECT
name,
department,
salary,
RANK() OVER (ORDER BY salary DESC) as overall_rank,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
LAG(salary) OVER (ORDER BY salary DESC) as prev_salary,
salary - LAG(salary) OVER (ORDER BY salary DESC) as salary_diff
FROM users
ORDER BY salary DESC
SQL
puts "=== Employee Rankings ==="
user_rankings.each do |row|
puts "#{row['name']} (#{row['department']})"
puts " Salary: $#{row['salary']}"
puts " Overall Rank: #{row['overall_rank']}"
puts " Department Rank: #{row['dept_rank']}"
if row['prev_salary']
puts " Salary difference from next highest: $#{row['salary_diff']}"
end
puts
end
# Common Table Expressions (CTE)
recursive_data = executor.execute_query(<<~SQL)
WITH RECURSIVE monthly_sales AS (
SELECT
DATE('2023-01-01') as month_start,
DATE('2023-01-31') as month_end,
1 as month_num
UNION ALL
SELECT
DATE(month_start, '+1 month'),
DATE(month_end, '+1 month'),
month_num + 1
FROM monthly_sales
WHERE month_num < 3
),
sales_by_month AS (
SELECT
ms.month_num,
ms.month_start,
ms.month_end,
COALESCE(SUM(o.amount), 0) as total_sales,
COUNT(o.id) as order_count
FROM monthly_sales ms
LEFT JOIN orders o ON o.order_date BETWEEN ms.month_start AND ms.month_end
GROUP BY ms.month_num, ms.month_start, ms.month_end
)
SELECT * FROM sales_by_month
ORDER BY month_num
SQL
puts "=== Monthly Sales Analysis ==="
recursive_data.each do |row|
puts "Month #{row['month_num']} (#{row['month_start']} to #{row['month_end']})"
puts " Sales: $#{row['total_sales']}"
puts " Orders: #{row['order_count']}"
puts
end
executor.closePrepared statements improve performance and security by pre-compiling SQL and using parameterized queries.
require 'sqlite3'
class PreparedStatementManager
def initialize(database_path)
@db = SQLite3::Database.new(database_path)
@db.results_as_hash = true
@statements = {}
end
def prepare(name, sql)
@statements[name] = @db.prepare(sql)
end
def execute(name, *params)
statement = @statements[name]
raise "Statement '#{name}' not found" unless statement
begin
statement.execute(*params)
rescue SQLite3::Exception => e
puts "Error executing #{name}: #{e.message}"
[]
end
end
def execute_single(name, *params)
result = execute(name, *params)
result.first
end
def execute_batch(name, param_sets)
statement = @statements[name]
raise "Statement '#{name}' not found" unless statement
results = []
param_sets.each do |params|
results << statement.execute(*params).to_a
end
results
end
def close_all
@statements.each_value(&:close)
@statements.clear
@db.close
end
end
# Usage example
stmt_manager = PreparedStatementManager.new('example.db')
# Prepare commonly used statements
stmt_manager.prepare(:find_user_by_email, "SELECT * FROM users WHERE email = ?")
stmt_manager.prepare(:find_users_by_department, "SELECT * FROM users WHERE department = ? ORDER BY name")
stmt_manager.prepare(:update_user_salary, "UPDATE users SET salary = ? WHERE id = ?")
stmt_manager.prepare(:insert_order, "INSERT INTO orders (user_id, product, amount, order_date, status) VALUES (?, ?, ?, ?, ?)")
stmt_manager.prepare(:user_order_summary, <<~SQL)
SELECT
u.name,
u.email,
COUNT(o.id) as order_count,
COALESCE(SUM(o.amount), 0) as total_spent,
MAX(o.order_date) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = ?
GROUP BY u.id, u.name, u.email
SQL
# Use prepared statements
puts "=== Using Prepared Statements ==="
# Find user
alice = stmt_manager.execute_single(:find_user_by_email, 'alice@company.com')
puts "Found user: #{alice['name']}"
# Find users by department
engineers = stmt_manager.execute(:find_users_by_department, 'Engineering')
puts "Engineers: #{engineers.map { |u| u['name'] }.join(', ')}"
# Update salary
stmt_manager.execute(:update_user_salary, 78000, alice['id'])
puts "Updated Alice's salary"
# Batch operations
new_orders = [
[alice['id'], 'Tablet', 500.00, '2023-02-01', 'pending'],
[alice['id'], 'Case', 50.00, '2023-02-01', 'pending'],
[alice['id'], 'Charger', 30.00, '2023-02-01', 'completed']
]
stmt_manager.execute_batch(:insert_order, new_orders)
puts "Inserted #{new_orders.length} new orders"
# Get user summary
summary = stmt_manager.execute_single(:user_order_summary, alice['id'])
puts "\nUser Summary for #{summary['name']}:"
puts " Total Orders: #{summary['order_count']}"
puts " Total Spent: $#{summary['total_spent']}"
puts " Last Order: #{summary['last_order_date']}"
stmt_manager.close_allTransactions ensure data consistency and allow you to group multiple operations into atomic units.
require 'sqlite3'
class TransactionManager
def initialize(database_path)
@db = SQLite3::Database.new(database_path)
@db.results_as_hash = true
end
def simple_transaction
@db.transaction do
yield @db
end
end
def manual_transaction
@db.execute("BEGIN TRANSACTION")
begin
yield @db
@db.execute("COMMIT")
rescue => e
@db.execute("ROLLBACK")
raise e
end
end
def savepoint_transaction(savepoint_name)
@db.execute("SAVEPOINT #{savepoint_name}")
begin
yield @db
@db.execute("RELEASE SAVEPOINT #{savepoint_name}")
rescue => e
@db.execute("ROLLBACK TO SAVEPOINT #{savepoint_name}")
raise e
end
end
def nested_transactions
@db.transaction do
puts "Outer transaction started"
# First operation
@db.execute("INSERT INTO users (name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?)",
['David Wilson', 'david@company.com', 'Marketing', 60000, '2023-02-01'])
user_id = @db.last_insert_row_id
puts "Inserted user with ID: #{user_id}"
# Nested savepoint
begin
savepoint_transaction('order_creation') do
puts "Savepoint transaction started"
# Insert multiple orders
orders = [
[user_id, 'Business Cards', 100.00, '2023-02-02', 'pending'],
[user_id, 'Brochures', 250.00, '2023-02-02', 'pending']
]
orders.each do |order|
@db.execute("INSERT INTO orders (user_id, product, amount, order_date, status) VALUES (?, ?, ?, ?, ?)", order)
end
# Simulate error condition
raise "Order processing failed" if orders.length > 1
puts "Orders inserted successfully"
end
rescue => e
puts "Savepoint rolled back: #{e.message}"
# Outer transaction continues
end
# Add a successful order outside the savepoint
@db.execute("INSERT INTO orders (user_id, product, amount, order_date, status) VALUES (?, ?, ?, ?, ?)",
[user_id, 'Website Design', 2000.00, '2023-02-03', 'completed'])
puts "Final order inserted"
end
puts "Outer transaction completed"
end
def concurrent_transaction_example
# Simulate concurrent access with proper locking
threads = []
# Create account balances table
@db.execute <<~SQL
CREATE TABLE IF NOT EXISTS accounts (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
balance DECIMAL(10,2) DEFAULT 0
)
SQL
# Initialize accounts
@db.execute("INSERT OR REPLACE INTO accounts (id, name, balance) VALUES (1, 'Account A', 1000)")
@db.execute("INSERT OR REPLACE INTO accounts (id, name, balance) VALUES (2, 'Account B', 500)")
# Simulate money transfer with proper locking
5.times do |i|
threads << Thread.new do
transfer_money(1, 2, 100, "Transfer #{i + 1}")
end
end
threads.each(&:join)
# Check final balances
balances = @db.execute("SELECT * FROM accounts ORDER BY id")
puts "\nFinal Balances:"
balances.each do |account|
puts "#{account['name']}: $#{account['balance']}"
end
end
def performance_transaction_comparison
require 'benchmark'
# Create test table
@db.execute "DROP TABLE IF EXISTS test_items"
@db.execute <<~SQL
CREATE TABLE test_items (
id INTEGER PRIMARY KEY,
name TEXT,
value INTEGER
)
SQL
puts "=== Transaction Performance Comparison ==="
# Without transaction
time_without = Benchmark.measure do
1000.times do |i|
@db.execute("INSERT INTO test_items (name, value) VALUES (?, ?)", ["Item #{i}", i])
end
end
@db.execute("DELETE FROM test_items")
# With single transaction
time_with = Benchmark.measure do
@db.transaction do
1000.times do |i|
@db.execute("INSERT INTO test_items (name, value) VALUES (?, ?)", ["Item #{i}", i])
end
end
end
puts "Without transaction: #{time_without.real.round(3)}s"
puts "With transaction: #{time_with.real.round(3)}s"
puts "Performance improvement: #{(time_without.real / time_with.real).round(1)}x faster"
# Batch insert comparison
@db.execute("DELETE FROM test_items")
time_batch = Benchmark.measure do
values = 1000.times.map { |i| "(#{i + 1}, 'Batch Item #{i}', #{i})" }.join(', ')
@db.execute("INSERT INTO test_items (id, name, value) VALUES #{values}")
end
puts "Batch insert: #{time_batch.real.round(3)}s"
puts "Batch vs transaction: #{(time_with.real / time_batch.real).round(1)}x faster"
end
def close
@db.close
end
private
def transfer_money(from_account, to_account, amount, description)
@db.transaction do
# Lock accounts to prevent race conditions
from_balance = @db.execute("SELECT balance FROM accounts WHERE id = ?", [from_account]).first
to_balance = @db.execute("SELECT balance FROM accounts WHERE id = ?", [to_account]).first
raise "Insufficient funds" if from_balance['balance'] < amount
# Update balances
@db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from_account])
@db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to_account])
puts "#{description}: Transferred $#{amount} from account #{from_account} to #{to_account}"
# Small delay to increase chance of race conditions (for demonstration)
sleep(0.01)
end
rescue => e
puts "#{description}: Failed - #{e.message}"
end
end
# Usage examples
tx_manager = TransactionManager.new('example.db')
puts "=== Simple Transaction Example ==="
tx_manager.simple_transaction do |db|
db.execute("UPDATE users SET salary = salary * 1.05 WHERE department = 'Engineering'")
puts "Engineering salaries increased by 5%"
end
puts "\n=== Nested Transactions with Savepoints ==="
tx_manager.nested_transactions
puts "\n=== Concurrent Transaction Example ==="
tx_manager.concurrent_transaction_example
puts "\n=== Performance Comparison ==="
tx_manager.performance_transaction_comparison
tx_manager.closeTechniques for writing database-agnostic raw SQL and handling database-specific features.
require 'pg'
require 'mysql2'
require 'sqlite3'
class MultiDatabaseExecutor
def initialize(db_type, connection_params)
@db_type = db_type.to_sym
@connection_params = connection_params
@connection = nil
connect
end
def connect
case @db_type
when :postgresql
@connection = PG.connect(@connection_params)
when :mysql
@connection = Mysql2::Client.new(@connection_params)
when :sqlite
@connection = SQLite3::Database.new(@connection_params[:database])
@connection.results_as_hash = true
else
raise "Unsupported database type: #{@db_type}"
end
end
def execute_raw(sql, params = [])
case @db_type
when :postgresql
if params.empty?
@connection.exec(sql)
else
@connection.exec_params(sql, params)
end
when :mysql
if params.empty?
@connection.query(sql)
else
stmt = @connection.prepare(sql)
result = stmt.execute(*params)
stmt.close
result
end
when :sqlite
if params.empty?
@connection.execute(sql)
else
@connection.execute(sql, params)
end
end
end
def get_database_info
case @db_type
when :postgresql
version = @connection.exec("SELECT version()").first['version']
{ type: 'PostgreSQL', version: version }
when :mysql
version = @connection.query("SELECT VERSION() as version").first['version']
{ type: 'MySQL', version: version }
when :sqlite
version = @connection.execute("SELECT sqlite_version()").first[0]
{ type: 'SQLite', version: version }
end
end
def create_users_table
sql = case @db_type
when :postgresql
<<~SQL
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
SQL
when :mysql
<<~SQL
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB
SQL
when :sqlite
<<~SQL
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
SQL
end
execute_raw(sql)
end
def insert_user(name, email)
sql = case @db_type
when :postgresql
"INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id"
when :mysql, :sqlite
"INSERT INTO users (name, email) VALUES (?, ?)"
end
result = execute_raw(sql, [name, email])
case @db_type
when :postgresql
result.first['id']
when :mysql
@connection.last_id
when :sqlite
@connection.last_insert_row_id
end
end
def find_users_with_pagination(page = 1, per_page = 10)
offset = (page - 1) * per_page
sql = case @db_type
when :postgresql
"SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2"
when :mysql, :sqlite
"SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?"
end
execute_raw(sql, [per_page, offset])
end
def get_table_schema(table_name)
case @db_type
when :postgresql
execute_raw(<<~SQL, [table_name])
SELECT
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_name = $1
ORDER BY ordinal_position
SQL
when :mysql
execute_raw("DESCRIBE #{table_name}")
when :sqlite
execute_raw("PRAGMA table_info(#{table_name})")
end
end
def close
case @db_type
when :postgresql, :mysql, :sqlite
@connection.close
end
end
end
# Example usage with different databases
databases = [
{
type: :sqlite,
params: { database: ':memory:' },
name: 'SQLite In-Memory'
}
# You can add other databases:
# {
# type: :postgresql,
# params: { host: 'localhost', dbname: 'test', user: 'postgres', password: 'password' },
# name: 'PostgreSQL'
# },
# {
# type: :mysql,
# params: { host: 'localhost', database: 'test', username: 'root', password: 'password' },
# name: 'MySQL'
# }
]
databases.each do |db_config|
puts "=== Testing #{db_config[:name]} ==="
begin
executor = MultiDatabaseExecutor.new(db_config[:type], db_config[:params])
# Get database info
info = executor.get_database_info
puts "Connected to #{info[:type]} version #{info[:version]}"
# Create table
executor.create_users_table
puts "Users table created"
# Insert users
user_id1 = executor.insert_user('Alice Johnson', 'alice@example.com')
user_id2 = executor.insert_user('Bob Smith', 'bob@example.com')
puts "Inserted users with IDs: #{user_id1}, #{user_id2}"
# Query users
users = executor.find_users_with_pagination(1, 10)
puts "Found #{users.respond_to?(:count) ? users.count : users.length} users"
# Get schema
schema = executor.get_table_schema('users')
puts "Table schema has #{schema.respond_to?(:count) ? schema.count : schema.length} columns"
executor.close
puts "#{db_config[:name]} test completed successfully\n"
rescue => e
puts "Error testing #{db_config[:name]}: #{e.message}\n"
end
endSometimes you need dynamic SQL generation. Here's a simple query builder for complex scenarios.
class SQLQueryBuilder
def initialize(db_type = :sqlite)
@db_type = db_type
@select_fields = []
@from_table = nil
@joins = []
@where_conditions = []
@group_by_fields = []
@having_conditions = []
@order_by_fields = []
@limit_value = nil
@offset_value = nil
@params = []
end
def select(*fields)
@select_fields.concat(fields.map(&:to_s))
self
end
def from(table)
@from_table = table.to_s
self
end
def join(table, condition)
@joins << "JOIN #{table} ON #{condition}"
self
end
def left_join(table, condition)
@joins << "LEFT JOIN #{table} ON #{condition}"
self
end
def where(condition, *params)
@where_conditions << condition
@params.concat(params)
self
end
def group_by(*fields)
@group_by_fields.concat(fields.map(&:to_s))
self
end
def having(condition, *params)
@having_conditions << condition
@params.concat(params)
self
end
def order_by(field, direction = :asc)
@order_by_fields << "#{field} #{direction.to_s.upcase}"
self
end
def limit(count)
@limit_value = count
self
end
def offset(count)
@offset_value = count
self
end
def to_sql
sql_parts = []
# SELECT clause
if @select_fields.empty?
sql_parts << "SELECT *"
else
sql_parts << "SELECT #{@select_fields.join(', ')}"
end
# FROM clause
raise "FROM table is required" unless @from_table
sql_parts << "FROM #{@from_table}"
# JOIN clauses
sql_parts.concat(@joins) unless @joins.empty?
# WHERE clause
unless @where_conditions.empty?
sql_parts << "WHERE #{@where_conditions.join(' AND ')}"
end
# GROUP BY clause
unless @group_by_fields.empty?
sql_parts << "GROUP BY #{@group_by_fields.join(', ')}"
end
# HAVING clause
unless @having_conditions.empty?
sql_parts << "HAVING #{@having_conditions.join(' AND ')}"
end
# ORDER BY clause
unless @order_by_fields.empty?
sql_parts << "ORDER BY #{@order_by_fields.join(', ')}"
end
# LIMIT and OFFSET
if @limit_value
case @db_type
when :postgresql
sql_parts << "LIMIT #{@limit_value}"
sql_parts << "OFFSET #{@offset_value}" if @offset_value
when :mysql, :sqlite
if @offset_value
sql_parts << "LIMIT #{@offset_value}, #{@limit_value}"
else
sql_parts << "LIMIT #{@limit_value}"
end
end
end
sql_parts.join(' ')
end
def params
@params
end
def reset
@select_fields.clear
@from_table = nil
@joins.clear
@where_conditions.clear
@group_by_fields.clear
@having_conditions.clear
@order_by_fields.clear
@limit_value = nil
@offset_value = nil
@params.clear
self
end
end
# Usage examples
puts "=== SQL Query Builder Examples ==="
# Simple query
builder = SQLQueryBuilder.new(:sqlite)
query1 = builder
.select(:name, :email, :department)
.from(:users)
.where("active = ?", true)
.order_by(:name)
.to_sql
puts "Simple Query:"
puts query1
puts "Params: #{builder.params.inspect}"
puts
# Complex query with joins and aggregation
builder.reset
query2 = builder
.select("u.name", "u.department", "COUNT(o.id) as order_count", "SUM(o.amount) as total_amount")
.from("users u")
.left_join("orders o", "u.id = o.user_id")
.where("u.active = ?", true)
.where("u.hire_date > ?", '2020-01-01')
.group_by("u.id", "u.name", "u.department")
.having("COUNT(o.id) > ?", 0)
.order_by("total_amount", :desc)
.limit(10)
.to_sql
puts "Complex Query with Joins and Aggregation:"
puts query2
puts "Params: #{builder.params.inspect}"
puts
# Dynamic query building based on conditions
def build_user_search_query(filters = {})
builder = SQLQueryBuilder.new(:sqlite)
builder.select(:id, :name, :email, :department, :salary).from(:users)
if filters[:department]
builder.where("department = ?", filters[:department])
end
if filters[:min_salary]
builder.where("salary >= ?", filters[:min_salary])
end
if filters[:max_salary]
builder.where("salary <= ?", filters[:max_salary])
end
if filters[:name_contains]
builder.where("name LIKE ?", "%#{filters[:name_contains]}%")
end
if filters[:hired_after]
builder.where("hire_date > ?", filters[:hired_after])
end
case filters[:sort_by]
when 'name'
builder.order_by(:name)
when 'salary'
builder.order_by(:salary, :desc)
when 'hire_date'
builder.order_by(:hire_date, :desc)
else
builder.order_by(:id)
end
if filters[:page] && filters[:per_page]
offset = (filters[:page] - 1) * filters[:per_page]
builder.limit(filters[:per_page]).offset(offset)
end
builder
end
# Example dynamic queries
search_filters = [
{ department: 'Engineering', min_salary: 70000, sort_by: 'salary' },
{ name_contains: 'Alice', hired_after: '2021-01-01' },
{ min_salary: 60000, max_salary: 80000, page: 1, per_page: 5 }
]
search_filters.each_with_index do |filters, index|
builder = build_user_search_query(filters)
puts "Dynamic Query #{index + 1}:"
puts "Filters: #{filters.inspect}"
puts "SQL: #{builder.to_sql}"
puts "Params: #{builder.params.inspect}"
puts
endWatch and learn raw sql execution