Module: AppQuery::RenderHelpers

Defined in:
lib/app_query/render_helpers.rb

Overview

Note:

These methods require +@collected_binds+ (Hash) and +@placeholder_counter+ (Integer) instance variables to be initialized in the including context.

Provides helper methods for rendering SQL templates in ERB.

These helpers are available within ERB templates when using Q#render. They provide safe SQL construction with parameterized queries.

Examples:

Basic usage in an ERB template

SELECT * FROM users WHERE name = <%= bind(name) %>
<%= order_by(sorting) %>

See Also:

Instance Method Summary collapse

Instance Method Details

#bind(value) ⇒ String

Creates a named bind parameter placeholder and collects the value.

This is the preferred way to include dynamic values in SQL queries. The value is collected internally and a placeholder (e.g., +:b1+) is returned for insertion into the SQL template.

Examples:

Basic bind usage

bind("Some title") #=> ":b1" (with "Some title" added to collected binds)

In an ERB template

SELECT * FROM videos WHERE title = <%= bind(title) %>
# Results in: SELECT * FROM videos WHERE title = :b1
# With binds: {b1: <value of title>}

Multiple binds

SELECT * FROM t WHERE a = <%= bind(val1) %> AND b = <%= bind(val2) %>
# Results in: SELECT * FROM t WHERE a = :b1 AND b = :b2

Parameters:

  • value (Object) —

    the value to bind (any type supported by ActiveRecord)

Returns:

  • (String) —

    the placeholder string (e.g., ":b1", ":b2", etc.)

See Also:



65
66
67
 
# File 'lib/app_query/render_helpers.rb', line 65

def bind(value)
  collect_bind(value)
end

#order_by(hash) ⇒ String

Note:

The hash must not be blank. Use conditional ERB for optional ordering.

Generates an ORDER BY clause from a hash of column directions.

Converts a hash of column names and sort directions into a valid SQL ORDER BY clause.

Examples:

Basic ordering

order_by(year: :desc, month: :desc)
#=> "ORDER BY year DESC, month DESC"

Mixed directions

order_by(published_on: :desc, title: :asc)
#=> "ORDER BY published_on DESC, title ASC"

Column without direction (uses database default)

order_by(id: nil)
#=> "ORDER BY id"

In an ERB template with a variable

SELECT * FROM articles
<%= order_by(ordering) %>

Making it optional (when ordering may not be provided)

<%= @ordering.presence && order_by(ordering) %>

With default fallback

<%= order_by(@order || {id: :desc}) %>

Parameters:

  • hash (Hash{Symbol, String => Symbol, String, nil}) —

    column names mapped to sort directions (+:asc+, +:desc+, +"ASC"+, +"DESC"+) or nil for default

Returns:

  • (String) —

    the complete ORDER BY clause

Raises:

  • (ArgumentError) —

    if hash is blank (nil, empty, or not present)



179
180
181
182
183
184
 
# File 'lib/app_query/render_helpers.rb', line 179

def order_by(hash)
  raise ArgumentError, "Provide columns to sort by, e.g. order_by(id: :asc)  (got #{hash.inspect})." unless hash.present?
  "ORDER BY " + hash.map do |k, v|
    v.nil? ? k : [k, v.upcase].join(" ")
  end.join(", ")
end

#quote(value) ⇒ String

Note:

Prefer #bind for parameterized queries when possible, as it provides better security and query plan caching.

Quotes a value for safe inclusion in SQL using ActiveRecord's quoting.

Use this helper when you need to embed a literal value directly in SQL rather than using a bind parameter. This is useful for values that need to be visible in the SQL string itself.

Examples:

Quoting a string with special characters

quote("Let's learn SQL!") #=> "'Let''s learn SQL!'"

In an ERB template

INSERT INTO articles (title) VALUES(<%= quote(title) %>)

Parameters:

  • value (Object) —

    the value to quote (typically a String or Number)

Returns:

  • (String) —

    the SQL-safe quoted value

See Also:



38
39
40
 
# File 'lib/app_query/render_helpers.rb', line 38

def quote(value)
  ActiveRecord::Base.connection.quote(value)
end

#values(coll, skip_columns: false) {|item| ... } ⇒ String

Generates a SQL VALUES clause from a collection with automatic bind parameters.

Supports three input formats:

  1. Array of Arrays - Simple row data without column names
  2. Array of Hashes - Row data with automatic column name extraction
  3. Collection with block - Custom value transformation per row

TODO: Add types: parameter to cast bind placeholders (needed for UNION ALL where PG can't infer types). E.g. values([[1]], types: [:integer]) would generate VALUES (:b1::integer)

Examples:

Array of arrays (simplest form)

values([[1, "Title A"], [2, "Title B"]])
#=> "VALUES (:b1, :b2),\n(:b3, :b4)"
# binds: {b1: 1, b2: "Title A", b3: 2, b4: "Title B"}

Array of hashes (with automatic column names)

values([{id: 1, title: "Video A"}, {id: 2, title: "Video B"}])
#=> "(id, title) VALUES (:b1, :b2),\n(:b3, :b4)"

Hashes with mixed keys (NULL for missing values)

values([{title: "A"}, {title: "B", published_on: "2024-01-01"}])
#=> "(title, published_on) VALUES (:b1, NULL),\n(:b2, :b3)"

Skip columns for UNION ALL

SELECT id FROM articles UNION ALL <%= values([{id: 1}], skip_columns: true) %>
#=> "SELECT id FROM articles UNION ALL VALUES (:b1)"

With block for custom expressions

values(videos) { |v| [bind(v[:id]), quote(v[:title]), 'now()'] }
#=> "VALUES (:b1, 'Escaped Title', now()), (:b2, 'Other', now())"

In a CTE

WITH articles(id, title) AS (<%= values(data) %>)
SELECT * FROM articles

Parameters:

  • coll (Array<Array>, Array<Hash>) —

    the collection of row data

  • skip_columns (Boolean) (defaults to: false) —

    when true, omits the column name list (useful for UNION ALL or CTEs where column names are defined elsewhere)

Yields:

  • (item) —

    optional block to transform each item into an array of SQL expressions

Yield Parameters:

  • item (Object) —

    each item from the collection

Yield Returns:

  • (Array<String>) —

    array of SQL expressions for the row values

Returns:

  • (String) —

    the complete VALUES clause SQL fragment

See Also:



115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
 
# File 'lib/app_query/render_helpers.rb', line 115

def values(coll, skip_columns: false, &block)
  first = coll.first

  # For hash collections, collect all unique keys
  if first.is_a?(Hash) && !block
    all_keys = coll.flat_map(&:keys).uniq

    rows = coll.map do |row|
      vals = all_keys.map { |k| row.key?(k) ? collect_bind(row[k]) : "NULL" }
      "(#{vals.join(", ")})"
    end

    columns = skip_columns ? "" : "(#{all_keys.join(", ")}) "
    "#{columns}VALUES #{rows.join(",\n")}"
  else
    # Arrays or block - current behavior
    rows = coll.map do |item|
      vals = if block
        block.call(item)
      elsif item.is_a?(Array)
        item.map { |v| collect_bind(v) }
      else
        [collect_bind(item)]
      end
      "(#{vals.join(", ")})"
    end
    "VALUES #{rows.join(",\n")}"
  end
end