Class: AppQuery::Q

Inherits:
Object
  • Object
show all
Defined in:
lib/app_query.rb

Overview

Query object for building, rendering, and executing SQL queries.

Q wraps a SQL string (optionally with ERB templating) and provides methods for query execution, CTE manipulation, and result handling.

Method Groups

  • Rendering — Process ERB templates to produce executable SQL.
  • Query Execution — Execute queries against the database. These methods wrap the equivalent ActiveRecord::Base.connection methods (select_all, insert, update, delete).
  • Query Introspection — Inspect and analyze the structure of the query.
  • Query Transformation — Create modified copies of the query. All transformation methods are immutable—they return a new Q instance and leave the original unchanged.
  • CTE Manipulation — Add, replace, or reorder Common Table Expressions (CTEs). Like transformation methods, these return a new Q instance.

Examples:

Basic query

AppQuery("SELECT * FROM users WHERE id = $1").select_one(binds: [1])

ERB templating

AppQuery("SELECT * FROM users WHERE name = <%= bind(name) %>")
  .render(name: "Alice")
  .select_all

CTE manipulation

AppQuery("WITH base AS (SELECT 1) SELECT * FROM base")
  .append_cte("extra AS (SELECT 2)")
  .select_all

Instance Attribute Summary collapse

Rendering collapse

Query Execution collapse

Query Introspection collapse

Query Transformation collapse

CTE Manipulation collapse

Instance Method Summary collapse

Constructor Details

#initialize(sql, name: nil, filename: nil, binds: {}, cast: true, cte_depth: 0, row_builder: nil) ⇒ Q

Creates a new query object.

Examples:

Simple query

Q.new("SELECT * FROM users")

With ERB and binds

Q.new("SELECT * FROM users WHERE id = :id", binds: {id: 1})

Parameters:

  • sql (String) —

    the SQL query string (may contain ERB)

  • name (String, nil) (defaults to: nil) —

    optional name for logging

  • filename (String, nil) (defaults to: nil) —

    optional filename for ERB error reporting

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters for the query

  • cast (Boolean, Hash, Array) (defaults to: true) —

    type casting configuration



391
392
393
394
395
396
397
398
399
400
 
# File 'lib/app_query.rb', line 391

def initialize(sql, name: nil, filename: nil, binds: {}, cast: true, cte_depth: 0, row_builder: nil)
  @sql = sql
  @name = name
  @filename = filename
  @binds = binds
  @cast = cast
  @cte_depth = cte_depth
  @row_builder = row_builder || RowBuilder.new
  @binds = binds_with_defaults(sql, binds)
end

Instance Attribute Details

#binds ⇒ String, ... (readonly)

Returns:

  • (String, nil) —

    optional name for the query (used in logs)

  • (String) —

    the SQL string

  • (Array, Hash) —

    bind parameters

  • (Boolean, Hash, Array) —

    casting configuration



367
368
369
 
# File 'lib/app_query.rb', line 367

def binds
  @binds
end

#cast ⇒ String, ... (readonly)

Returns:

  • (String, nil) —

    optional name for the query (used in logs)

  • (String) —

    the SQL string

  • (Array, Hash) —

    bind parameters

  • (Boolean, Hash, Array) —

    casting configuration



367
368
369
 
# File 'lib/app_query.rb', line 367

def cast
  @cast
end

#cte_depth ⇒ Object (readonly)

Returns the value of attribute cte_depth.



402
403
404
 
# File 'lib/app_query.rb', line 402

def cte_depth
  @cte_depth
end

#filename ⇒ String, ... (readonly)

Returns:

  • (String, nil) —

    optional name for the query (used in logs)

  • (String) —

    the SQL string

  • (Array, Hash) —

    bind parameters

  • (Boolean, Hash, Array) —

    casting configuration



367
368
369
 
# File 'lib/app_query.rb', line 367

def filename
  @filename
end

#name ⇒ String, ... (readonly)

Returns:

  • (String, nil) —

    optional name for the query (used in logs)

  • (String) —

    the SQL string

  • (Array, Hash) —

    bind parameters

  • (Boolean, Hash, Array) —

    casting configuration



367
368
369
 
# File 'lib/app_query.rb', line 367

def name
  @name
end

#row_builder ⇒ RowBuilder

Middleware extension point. The RowBuilder is a composable pipeline: middlewares append transformers with q.row_builder << ->(row) { … } and the result is applied to every row everywhere Q exposes rows (entries, first, last, take, take_last, with_select(...).first, …). Propagated through #deep_dup (with an independent copy) so chained queries inherit the pipeline but don't mutate the parent's.

Returns:



376
377
378
 
# File 'lib/app_query.rb', line 376

def row_builder
  @row_builder
end

#sql ⇒ String, ... (readonly)

Returns:

  • (String, nil) —

    optional name for the query (used in logs)

  • (String) —

    the SQL string

  • (Array, Hash) —

    bind parameters

  • (Boolean, Hash, Array) —

    casting configuration



367
368
369
 
# File 'lib/app_query.rb', line 367

def sql
  @sql
end

Instance Method Details

#add_binds(**binds) ⇒ Q

Returns a new query with binds added.

Examples:

query = AppQuery("SELECT :foo, :bar", binds: {foo: 1})
query.add_binds(bar: 2).binds
# => {foo: 1, bar: 2}

Parameters:

  • binds (Hash, nil) —

    the bind parameters to add

Returns:

  • (Q) —

    a new query object with the added binds



1040
1041
1042
 
# File 'lib/app_query.rb', line 1040

def add_binds(**binds)
  deep_dup(binds: self.binds.merge(binds))
end

#any?(s = nil, binds: {}) ⇒ Boolean

Returns whether any rows exist in the query result.

Uses EXISTS which stops at the first matching row, making it more efficient than count > 0 for large result sets.

Examples:

Check if query has results

AppQuery("SELECT * FROM users").any?
# => true

Check with filtering

AppQuery("SELECT * FROM users").any?("SELECT * FROM :_ WHERE admin")
# => false

Parameters:

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before checking

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

Returns:

  • (Boolean) —

    true if at least one row exists



674
675
676
 
# File 'lib/app_query.rb', line 674

def any?(s = nil, binds: {})
  with_select(s).select_all("SELECT EXISTS(SELECT 1 FROM :_) e", binds:).column("e").first
end

#append_cte(cte) ⇒ Q

Appends a CTE to the end of the WITH clause.

If the query has no CTEs, wraps it with WITH. If the query already has CTEs, adds the new CTE at the end.

Examples:

Adding a CTE to a simple query

AppQuery("SELECT 1").append_cte("foo AS (SELECT 2)")
# => "WITH foo AS (SELECT 2) SELECT 1"

Appending to existing CTEs

AppQuery("WITH bar AS (SELECT 2) SELECT * FROM bar")
  .append_cte("foo AS (SELECT 1)")
# => "WITH bar AS (SELECT 2), foo AS (SELECT 1) SELECT * FROM bar"

Parameters:

  • cte (String) —

    the CTE definition (e.g., "foo AS (SELECT 1)")

Returns:

  • (Q) —

    a new query object with the appended CTE



1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
 
# File 'lib/app_query.rb', line 1208

def append_cte(cte)
  # early raise when cte is not valid sql
  add_recursive, to_append = Tokenizer.tokenize(cte, state: :lex_append_cte).then do |tokens|
    [!recursive? && tokens.find { _1[:t] == "RECURSIVE" },
      tokens.reject { _1[:t] == "RECURSIVE" }]
  end

  if cte_names.none?
    with_sql("WITH #{cte}\n#{self}")
  else
    nof_ctes = cte_names.size

    with_sql(tokens.map do |token|
      nof_ctes -= 1 if token[:t] == "CTE_SELECT"

      if nof_ctes.zero?
        nof_ctes -= 1
        token[:v] + to_append.map { _1[:v] }.join
      elsif token[:t] == "WITH" && add_recursive
        token[:v] + add_recursive[:v]
      else
        token[:v]
      end
    end.join)
  end
end

#column(c, s = nil, binds: {}, unique: false) ⇒ Array

Returns an array of values for a single column.

Wraps the query in a CTE and selects only the specified column, which is more efficient than fetching all columns via select_all.column(name). The column name is safely quoted, making this method safe for user input.

Examples:

Extract a single column

AppQuery("SELECT id, name FROM users").column(:name)
# => ["Alice", "Bob", "Charlie"]

With additional filtering

AppQuery("SELECT * FROM users").column(:email, "SELECT * FROM :_ WHERE active")
# => ["alice@example.com", "bob@example.com"]

Extract unique values

AppQuery("SELECT * FROM products").column(:category, unique: true)
# => ["Electronics", "Clothing", "Home"]

Parameters:

  • c (String, Symbol) —

    the column name to extract

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before extracting

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

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

    whether to have unique values

Returns:

  • (Array) —

    the column values

Raises:

  • (ArgumentError) —

    if the column doesn't exist in the (optionally selected) query. Pre-validating catches typos consistently across databases — e.g. without this, SQLite's "double-quoted strings" quirk would silently return rows of the column-name as a string.



721
722
723
724
725
726
727
728
729
 
# File 'lib/app_query.rb', line 721

def column(c, s = nil, binds: {}, unique: false)
  available = column_names(s, binds:)
  unless available.include?(c.to_s)
    raise ArgumentError, "Unknown column #{c.inspect}. Available: #{available.inspect}."
  end
  quoted = quote_column(c)
  select_expr = unique ? "DISTINCT #{quoted}" : quoted
  with_select(s).select_all("SELECT #{select_expr} AS column FROM :_", binds:).column("column")
end

#column_names(s = nil, binds: {}) ⇒ Array<String>

Returns the column names from the query without fetching any rows.

Uses LIMIT 0 to get column metadata efficiently.

Examples:

Get column names

AppQuery("SELECT id, name, email FROM users").column_names
# => ["id", "name", "email"]

From a CTE

AppQuery("WITH t(a, b) AS (VALUES (1, 2)) SELECT * FROM t").column_names
# => ["a", "b"]

Parameters:

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before extracting

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

Returns:

  • (Array<String>) —

    the column names



746
747
748
 
# File 'lib/app_query.rb', line 746

def column_names(s = nil, binds: {})
  with_select(s).select_all("SELECT * FROM :_ LIMIT 0", binds:).columns
end

#copy_to(s = nil, format: :csv, header: true, delimiter: nil, dest: nil, binds: {}) ⇒ String, ...

Executes COPY TO STDOUT for efficient data export.

PostgreSQL-only. Uses raw connection for streaming. Raises an error when used with SQLite or other non-PostgreSQL adapters.

Examples:

Return as string

csv = AppQuery[:users].copy_to

Write to file path

AppQuery[:users].copy_to(dest: "export.csv")

Write to IO object

File.open("export.csv", "w") { |f| query.copy_to(dest: f) }

Export in Rails controller

respond_to do |format|
   format.html do
     @invoices = query.entries

     render :index
   end

   format.csv do
     response.headers['Content-Type'] = 'text/csv'
     response.headers['Content-Disposition'] = 'attachment; filename="invoices.csv"'

     query.unpaginated.copy_to(dest: response.stream)
   end
 end

Rails runner

bin/rails runner "puts Export::ProductsQuery.new.copy_to" > tmp/products.csv

Parameters:

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before extracting

  • format (:csv, :text, :binary) (defaults to: :csv) —

    output format (default: :csv)

  • header (Boolean) (defaults to: true) —

    include column headers (default: true, CSV only)

  • delimiter (Symbol, nil) (defaults to: nil) —

    field delimiter - :tab, :comma, :pipe, :semicolon (default: format's default)

  • dest (String, IO, nil) (defaults to: nil) —

    destination - file path, IO object, or nil to return string

  • binds (Hash) (defaults to: {}) —

    bind parameters

Returns:

  • (String, Integer, nil) —

    CSV string if dest: nil, bytes written if dest: path, nil if dest: IO

Raises:



924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
 
# File 'lib/app_query.rb', line 924

def copy_to(s = nil, format: :csv, header: true, delimiter: nil, dest: nil, binds: {})
  raw_conn = ActiveRecord::Base.connection.raw_connection
  unless raw_conn.respond_to?(:copy_data)
    raise Error, "copy_to requires PostgreSQL (current adapter does not support COPY)"
  end

  allowed_formats = %i[csv text binary]
  unless allowed_formats.include?(format)
    raise ArgumentError, "Invalid format: #{format.inspect}. Allowed: #{allowed_formats.join(", ")}"
  end

  delimiters = {tab: "\t", comma: ",", pipe: "|", semicolon: ";"}
  if delimiter
    if !delimiters.key?(delimiter)
      raise ArgumentError, "Invalid delimiter: #{delimiter.inspect}. Allowed: #{delimiters.keys.join(", ")}"
    elsif format == :binary
      raise ArgumentError, "Delimiter not allowed for format :binary"
    end
  end

  add_binds(**binds).with_select(s).render({}).then do |aq|
    options = ["FORMAT #{format.to_s.upcase}"]
    options << "HEADER" if header && format == :csv
    options << "DELIMITER E'#{delimiters[delimiter]}'" if delimiter

    inner_sql = ActiveRecord::Base.sanitize_sql_array([aq.to_s, aq.binds])
    copy_sql = "COPY (#{inner_sql}) TO STDOUT WITH (#{options.join(", ")})"

    case dest
    when NilClass
      output = +""
      raw_conn.copy_data(copy_sql) do
        while (row = raw_conn.get_copy_data)
          output << row
        end
      end
      # pg returns ASCII-8BIT, but CSV/text is UTF-8; binary stays as-is
      (format == :binary) ? output : output.force_encoding(Encoding::UTF_8)
    when String
      bytes = 0
      File.open(dest, "wb") do |f|
        raw_conn.copy_data(copy_sql) do
          while (row = raw_conn.get_copy_data)
            bytes += f.write(row)
          end
        end
      end
      bytes
    else
      raw_conn.copy_data(copy_sql) do
        while (row = raw_conn.get_copy_data)
          dest.write(row)
        end
      end
      nil
    end
  end
end

#count(s = nil, binds: {}) ⇒ Integer

Returns the count of rows from the query.

Wraps the query in a CTE and selects only the count, which is more efficient than fetching all rows via select_all.count.

Examples:

Simple count

AppQuery("SELECT * FROM users").count
# => 42

Count with filtering

AppQuery("SELECT * FROM users")
  .with_select("SELECT * FROM :_ WHERE active")
  .count
# => 10

Parameters:

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before counting

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

Returns:

  • (Integer) —

    the count of rows



654
655
656
 
# File 'lib/app_query.rb', line 654

def count(s = nil, binds: {})
  with_select(s).select_all("SELECT COUNT(*) c FROM :_", binds:).column("c").first
end

#cte(name) ⇒ Q

Returns a new query focused on the specified CTE.

Wraps the query to select from the named CTE, allowing you to inspect or test individual CTEs in isolation.

Examples:

Focus on a specific CTE

query = AppQuery("WITH published AS (SELECT * FROM articles WHERE published) SELECT * FROM published")
query.cte(:published).entries

Chain with other methods

ArticleQuery.new.cte(:active_articles).take(5)

Parameters:

  • name (Symbol, String) —

    the CTE name to select from

Returns:

  • (Q) —

    a new query selecting from the CTE

Raises:

  • (ArgumentError) —

    if the CTE doesn't exist



1148
1149
1150
1151
1152
1153
1154
 
# File 'lib/app_query.rb', line 1148

def cte(name)
  name = name.to_s
  unless cte_names.include?(name)
    raise ArgumentError, "Unknown CTE #{name.inspect}. Available: #{cte_names.inspect}"
  end
  with_select("SELECT * FROM #{quote_table(name)}")
end

#cte_names ⇒ Array<String>

Returns the names of all CTEs (Common Table Expressions) in the query.

Examples:

AppQuery("WITH a AS (SELECT 1), b AS (SELECT 2) SELECT * FROM a, b").cte_names
# => ["a", "b"]

Quoted identifiers are returned without quotes

AppQuery('WITH "special*name" AS (SELECT 1) SELECT * FROM "special*name"').cte_names
# => ["special*name"]

Returns:

  • (Array<String>) —

    the CTE names in order of appearance



1011
1012
1013
 
# File 'lib/app_query.rb', line 1011

def cte_names
  tokens.filter { _1[:t] == "CTE_IDENTIFIER" }.map { _1[:v].delete_prefix('"').delete_suffix('"') }
end

#deep_dup(sql: self.sql, name: self.name, filename: self.filename, binds: self.binds.dup, cast: self.cast, cte_depth: self.cte_depth, row_builder: self.row_builder.dup) ⇒ Object 



421
422
423
 
# File 'lib/app_query.rb', line 421

def deep_dup(sql: self.sql, name: self.name, filename: self.filename, binds: self.binds.dup, cast: self.cast, cte_depth: self.cte_depth, row_builder: self.row_builder.dup)
  self.class.new(sql, name:, filename:, binds:, cast:, cte_depth:, row_builder:)
end

#delete(binds: {}) ⇒ Integer

Executes a DELETE query.

Examples:

With named binds

AppQuery("DELETE FROM videos WHERE id = :id").delete(binds: {id: 1})

With positional binds

AppQuery("DELETE FROM videos WHERE id = $1").delete(binds: [1])

Parameters:

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters for the query

Returns:

  • (Integer) —

    the number of deleted rows

Raises:



868
869
870
871
872
873
874
875
876
877
878
879
880
 
# File 'lib/app_query.rb', line 868

def delete(binds: {})
  with_binds(**binds).render({}).then do |aq|
    sql = if ActiveRecord::VERSION::STRING.to_f >= 7.1
      aq.to_arel
    else
      ActiveRecord::Base.sanitize_sql_array([aq.to_s, **aq.binds])
    end
    ActiveRecord::Base.connection.delete(sql, name)
  end
rescue NameError => e
  raise e unless e.instance_of?(NameError)
  raise UnrenderedQueryError, "Query is ERB. Use #render before deleting."
end

#entries ⇒ Array<Hash>

Executes the query and returns results as an Array of Hashes.

Shorthand for select_all(...).entries. Accepts the same arguments as #select_all.

Examples:

AppQuery("SELECT * FROM users").entries
# => [{"id" => 1, "name" => "Alice"}, {"id" => 2, "name" => "Bob"}]

Returns:

  • (Array<Hash>) —

    the query results as an array

See Also:



782
783
784
 
# File 'lib/app_query.rb', line 782

def entries(...)
  select_all(...).entries
end

#first ⇒ Object 



555
 
# File 'lib/app_query.rb', line 555

def first(...) = select_one(...)

#ids(s = nil, binds: {}) ⇒ Array

Returns an array of id values from the query.

Convenience method equivalent to column(:id). More efficient than fetching all columns via select_all.column("id").

Examples:

Get all user IDs

AppQuery("SELECT * FROM users").ids
# => [1, 2, 3]

With filtering

AppQuery("SELECT * FROM users").ids("SELECT * FROM :_ WHERE active")
# => [1, 3]

Parameters:

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before extracting

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

Returns:

  • (Array) —

    the id values



766
767
768
 
# File 'lib/app_query.rb', line 766

def ids(s = nil, binds: {})
  column(:id, s, binds:)
end

#insert(binds: {}, returning: nil) ⇒ Integer, Object

Executes an INSERT query.

Examples:

With values helper

articles = [{title: "First", created_at: Time.current}]
AppQuery(<<~SQL).render(articles:).insert
  INSERT INTO articles(title, created_at) <%= values(articles) %>
SQL

With returning (Rails 7.1+)

AppQuery("INSERT INTO users(name) VALUES($1)")
  .insert(binds: ["Alice"], returning: "id, created_at")

Parameters:

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters for the query

  • returning (String, nil) (defaults to: nil) —

    columns to return (Rails 7.1+ only)

Returns:

  • (Integer, Object) —

    the inserted ID or returning value

Raises:

  • (UnrenderedQueryError) —

    if the query contains unrendered ERB

  • (ArgumentError) —

    if returning is used with Rails < 7.1



804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
 
# File 'lib/app_query.rb', line 804

def insert(binds: {}, returning: nil)
  # ActiveRecord::Base.connection.insert(sql, name, _pk = nil, _id_value = nil, _sequence_name = nil, binds, returning: nil)
  if returning && ActiveRecord::VERSION::STRING.to_f < 7.1
    raise ArgumentError, "The 'returning' option requires Rails 7.1+. Current version: #{ActiveRecord::VERSION::STRING}"
  end

  with_binds(**binds).render({}).then do |aq|
    sql = if ActiveRecord::VERSION::STRING.to_f >= 7.1
      aq.to_arel
    else
      ActiveRecord::Base.sanitize_sql_array([aq.to_s, **aq.binds])
    end
    if ActiveRecord::VERSION::STRING.to_f >= 7.1
      ActiveRecord::Base.connection.insert(sql, name, returning:)
    else
      ActiveRecord::Base.connection.insert(sql, name)
    end
  end
rescue NameError => e
  # Prevent any subclasses, e.g. NoMethodError
  raise e unless e.instance_of?(NameError)
  raise UnrenderedQueryError, "Query is ERB. Use #render before select-ing."
end

#last(s = nil, binds: {}, cast: self.cast) ⇒ Hash?

Executes the query and returns the last row.

Uses OFFSET to skip to the last row without changing the query order. Note: This requires counting all rows first, so it's less efficient than #first for large result sets.

Examples:

AppQuery("SELECT * FROM users ORDER BY created_at").last
# => {"id" => 42, "name" => "Zoe"}

Parameters:

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before fetching

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

  • cast (Boolean, Hash, Array) (defaults to: self.cast) —

    type casting configuration

Returns:

  • (Hash, nil) —

    the last row as a hash, or nil if no results

See Also:



573
574
575
 
# File 'lib/app_query.rb', line 573

def last(s = nil, binds: {}, cast: self.cast)
  take_last(1, s, binds:, cast:).first
end

#none?(s = nil, binds: {}) ⇒ Boolean

Returns whether no rows exist in the query result.

Inverse of #any?. Uses EXISTS for efficiency.

Examples:

Check if query is empty

AppQuery("SELECT * FROM users WHERE admin").none?
# => true

Parameters:

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before checking

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

Returns:

  • (Boolean) —

    true if no rows exist



689
690
691
 
# File 'lib/app_query.rb', line 689

def none?(s = nil, binds: {})
  !any?(s, binds:)
end

#prepend_cte(cte) ⇒ Q

Prepends a CTE to the beginning of the WITH clause.

If the query has no CTEs, wraps it with WITH. If the query already has CTEs, adds the new CTE at the beginning.

Examples:

Adding a CTE to a simple query

AppQuery("SELECT 1").prepend_cte("foo AS (SELECT 2)")
# => "WITH foo AS (SELECT 2) SELECT 1"

Prepending to existing CTEs

AppQuery("WITH bar AS (SELECT 2) SELECT * FROM bar")
  .prepend_cte("foo AS (SELECT 1)")
# => "WITH foo AS (SELECT 1), bar AS (SELECT 2) SELECT * FROM bar"

Parameters:

  • cte (String) —

    the CTE definition (e.g., "foo AS (SELECT 1)")

Returns:

  • (Q) —

    a new query object with the prepended CTE



1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
 
# File 'lib/app_query.rb', line 1172

def prepend_cte(cte)
  # early raise when cte is not valid sql
  to_append = Tokenizer.tokenize(cte, state: :lex_prepend_cte).then do |tokens|
    recursive? ? tokens.reject { _1[:t] == "RECURSIVE" } : tokens
  end

  if cte_names.none?
    with_sql("WITH #{cte}\n#{self}")
  else
    split_at_type = recursive? ? "RECURSIVE" : "WITH"
    with_sql(tokens.map do |token|
      if token[:t] == split_at_type
        token[:v] + to_append.map { _1[:v] }.join
      else
        token[:v]
      end
    end.join)
  end
end

#recursive? ⇒ Boolean

Checks if the query uses RECURSIVE CTEs.

Examples:

AppQuery("WITH RECURSIVE t AS (...) SELECT * FROM t").recursive?
# => true

Returns:

  • (Boolean) —

    true if the query contains WITH RECURSIVE



1127
1128
1129
 
# File 'lib/app_query.rb', line 1127

def recursive?
  !!tokens.find { _1[:t] == "RECURSIVE" }
end

#render(vars = {}) ⇒ Q

Renders the ERB template with the given variables.

Processes ERB tags in the SQL and collects any bind parameters created by helpers like RenderHelpers#bind and RenderHelpers#values.

Examples:

Rendering with variables

AppQuery("SELECT * FROM users WHERE name = <%= bind(name) %>")
  .render(name: "Alice")
# => Q with SQL: "SELECT * FROM users WHERE name = :b1"
#    and binds: {b1: "Alice"}

Using instance variables

AppQuery("SELECT * FROM users WHERE active = <%= @active %>")
  .render(active: true)

vars are available as local and instance variable.

# This fails as `ordering` is not provided:
AppQuery(<<~SQL).render
  SELECT * FROM articles
  <%= order_by(ordering) %>
SQL

# ...but this query works without `ordering` being passed to render:
AppQuery(<<~SQL).render
  SELECT * FROM articles
  <%= @ordering.presence && order_by(ordering) %>
SQL
# NOTE that `@ordering.present? && ...` would render as `false`.
# Use `@ordering.presence` instead.

Parameters:

  • vars (Hash) (defaults to: {}) —

    variables to make available in the ERB template

Returns:

  • (Q) —

    a new query object with rendered SQL and collected binds

See Also:



462
463
464
465
466
467
468
469
 
# File 'lib/app_query.rb', line 462

def render(vars = {})
  vars ||= {}
  helper = render_helper(vars)
  sql = to_erb.result(helper.get_binding)
  collected = helper.collected_binds

  with_sql(sql).add_binds(**collected)
end

#replace_cte(cte) ⇒ Q

Replaces an existing CTE with a new definition.

Examples:

AppQuery("WITH foo AS (SELECT 1) SELECT * FROM foo")
  .replace_cte("foo AS (SELECT 2)")
# => "WITH foo AS (SELECT 2) SELECT * FROM foo"

Parameters:

  • cte (String) —

    the new CTE definition (must have same name as existing CTE)

Returns:

  • (Q) —

    a new query object with the replaced CTE

Raises:

  • (ArgumentError) —

    if the CTE name doesn't exist in the query



1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
 
# File 'lib/app_query.rb', line 1246

def replace_cte(cte)
  add_recursive, to_append = Tokenizer.tokenize(cte, state: :lex_recursive_cte).then do |tokens|
    [!recursive? && tokens.find { _1[:t] == "RECURSIVE" },
      tokens.reject { _1[:t] == "RECURSIVE" }]
  end

  cte_name = to_append.find { _1[:t] == "CTE_IDENTIFIER" }&.[](:v)
  unless cte_names.include?(cte_name)
    raise ArgumentError, "Unknown cte #{cte_name.inspect}. Options: #{cte_names}."
  end
  cte_ix = cte_names.index(cte_name)

  return self unless cte_ix

  cte_found = false

  with_sql(tokens.map do |token|
    if cte_found ||= token[:t] == "CTE_IDENTIFIER" && token[:v] == cte_name
      unless (cte_found = (token[:t] != "CTE_SELECT"))
        next to_append.map { _1[:v] }.join
      end

      next
    elsif token[:t] == "WITH" && add_recursive
      token[:v] + add_recursive[:v]
    else
      token[:v]
    end
  end.join)
end

#select ⇒ String?

Returns the SELECT clause of the query.

Examples:

AppQuery("SELECT id, name FROM users").select
# => "SELECT id, name FROM users"

Returns:

  • (String, nil) —

    the SELECT clause, or nil if not found



1116
1117
1118
 
# File 'lib/app_query.rb', line 1116

def select
  tokens.find { _1[:t] == "SELECT" }&.[](:v)
end

#select_all(s = nil, binds: {}, cast: self.cast) ⇒ Result

Executes the query and returns all matching rows.

Examples:

(Named) binds

AppQuery("SELECT * FROM users WHERE id = :id").select_all(binds: {id: 1})

With type casting (shorthand)

AppQuery("SELECT published_on FROM articles")
  .select_all(cast: {"published_on" => :date})

With type casting (explicit)

AppQuery("SELECT metadata FROM products")
  .select_all(cast: {"metadata" => ActiveRecord::Type::Json.new})

Override SELECT clause

AppQuery("SELECT * FROM users").select_all("COUNT(*)")

Parameters:

  • select (String, nil) —

    override the SELECT clause

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

  • cast (Boolean, Hash, Array) (defaults to: self.cast) —

    type casting configuration

Returns:

  • (Result) —

    the query results with optional type casting

Raises:



522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
 
# File 'lib/app_query.rb', line 522

def select_all(s = nil, binds: {}, cast: self.cast)
  add_binds(**binds).with_select(s).render({}).then do |aq|
    sql = if ActiveRecord::VERSION::STRING.to_f >= 7.1
      aq.to_arel
    else
      ActiveRecord::Base.sanitize_sql_array([aq.to_s, aq.binds])
    end
    ActiveRecord::Base.connection.select_all(sql, aq.name).then do |result|
      Result.from_ar_result(result, cast, row_builder: aq.row_builder)
    end
  end
rescue NameError => e
  # Prevent any subclasses, e.g. NoMethodError
  raise e unless e.instance_of?(NameError)
  raise UnrenderedQueryError, "Query is ERB. Use #render before select-ing."
end

#select_one(s = nil, binds: {}, cast: self.cast) ⇒ Hash?

Executes the query and returns the first row.

Examples:

AppQuery("SELECT * FROM users WHERE id = :id").select_one(binds: {id: 1})
# => {"id" => 1, "name" => "Alice"}

Parameters:

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

  • select (String, nil) —

    override the SELECT clause

  • cast (Boolean, Hash, Array) (defaults to: self.cast) —

    type casting configuration

Returns:

  • (Hash, nil) —

    the first row as a hash, or nil if no results

See Also:



551
552
553
 
# File 'lib/app_query.rb', line 551

def select_one(s = nil, binds: {}, cast: self.cast)
  with_select(s).select_all("SELECT * FROM :_ LIMIT 1", binds:, cast:).first
end

#select_value(s = nil, binds: {}, cast: self.cast) ⇒ Object?

Executes the query and returns the first value of the first row.

Examples:

AppQuery("SELECT COUNT(*) FROM users").select_value
# => 42

Parameters:

  • binds (Hash, nil) (defaults to: {}) —

    named bind parameters

  • select (String, nil) —

    override the SELECT clause

  • cast (Boolean, Hash, Array) (defaults to: self.cast) —

    type casting configuration

Returns:

  • (Object, nil) —

    the first value, or nil if no results

See Also:



632
633
634
 
# File 'lib/app_query.rb', line 632

def select_value(s = nil, binds: {}, cast: self.cast)
  select_one(s, binds:, cast:)&.values&.first
end

#take(n, s = nil, binds: {}, cast: self.cast) ⇒ Array<Hash> Also known as: limit

Executes the query and returns the first n rows.

Examples:

AppQuery("SELECT * FROM users ORDER BY created_at").take(5)
# => [{"id" => 1, ...}, {"id" => 2, ...}, ...]

Parameters:

  • n (Integer) —

    the number of rows to return

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before taking

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

  • cast (Boolean, Hash, Array) (defaults to: self.cast) —

    type casting configuration

Returns:

  • (Array<Hash>) —

    the first n rows as an array of hashes

See Also:



590
591
592
 
# File 'lib/app_query.rb', line 590

def take(n, s = nil, binds: {}, cast: self.cast)
  with_select(s).select_all("SELECT * FROM :_ LIMIT #{n.to_i}", binds:, cast:).entries
end

#take_last(n, s = nil, binds: {}, cast: self.cast) ⇒ Array<Hash>

Executes the query and returns the last n rows.

Uses OFFSET to skip to the last n rows without changing the query order. Note: This requires counting all rows first, so it's less efficient than #take for large result sets.

Examples:

AppQuery("SELECT * FROM users ORDER BY created_at").take_last(5)
# => [{"id" => 38, ...}, {"id" => 39, ...}, ...]

Parameters:

  • n (Integer) —

    the number of rows to return

  • s (String, nil) (defaults to: nil) —

    optional SELECT to apply before taking

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters to add

  • cast (Boolean, Hash, Array) (defaults to: self.cast) —

    type casting configuration

Returns:

  • (Array<Hash>) —

    the last n rows as an array of hashes

See Also:



612
613
614
615
616
617
618
 
# File 'lib/app_query.rb', line 612

def take_last(n, s = nil, binds: {}, cast: self.cast)
  offset_expr = greatest("(SELECT COUNT(*) FROM :_) - #{n.to_i}", "0")
  with_select(s).select_all(
    "SELECT * FROM :_ LIMIT #{n.to_i} OFFSET #{offset_expr}",
    binds:, cast:
  ).entries
end

#to_arel ⇒ Object 



404
405
406
407
408
409
410
411
 
# File 'lib/app_query.rb', line 404

def to_arel
  if binds.presence
    Arel::Nodes::BoundSqlLiteral.new sql, [], binds
  else
    # TODO: add retryable? available from >=7.1
    Arel::Nodes::SqlLiteral.new(sql)
  end
end

#to_s ⇒ String

Returns the SQL string.

Returns:

  • (String) —

    the SQL query string



1282
1283
1284
 
# File 'lib/app_query.rb', line 1282

def to_s
  @sql
end

#tokenizer ⇒ Tokenizer

Returns the tokenizer instance for this query.

Returns:



996
997
998
 
# File 'lib/app_query.rb', line 996

def tokenizer
  @tokenizer ||= Tokenizer.new(to_s)
end

#tokens ⇒ Array<Hash>

Returns the tokenized representation of the SQL.

Returns:

  • (Array<Hash>) —

    array of token hashes with :t (type) and :v (value) keys

See Also:



989
990
991
 
# File 'lib/app_query.rb', line 989

def tokens
  @tokens ||= tokenizer.run
end

#update(binds: {}) ⇒ Integer

Executes an UPDATE query.

Examples:

With named binds

AppQuery("UPDATE videos SET title = 'New' WHERE id = :id")
  .update(binds: {id: 1})

With positional binds

AppQuery("UPDATE videos SET title = $1 WHERE id = $2")
  .update(binds: ["New Title", 1])

Parameters:

  • binds (Hash, nil) (defaults to: {}) —

    bind parameters for the query

Returns:

  • (Integer) —

    the number of affected rows

Raises:



842
843
844
845
846
847
848
849
850
851
852
853
854
 
# File 'lib/app_query.rb', line 842

def update(binds: {})
  with_binds(**binds).render({}).then do |aq|
    sql = if ActiveRecord::VERSION::STRING.to_f >= 7.1
      aq.to_arel
    else
      ActiveRecord::Base.sanitize_sql_array([aq.to_s, **aq.binds])
    end
    ActiveRecord::Base.connection.update(sql, name)
  end
rescue NameError => e
  raise e unless e.instance_of?(NameError)
  raise UnrenderedQueryError, "Query is ERB. Use #render before updating."
end

#with_binds(**binds) ⇒ Q Also known as: replace_binds

Returns a new query with different bind parameters.

Examples:

query = AppQuery("SELECT :foo, :bar", binds: {foo: 1})
query.with_binds(bar: 2).binds
# => {foo: nil, bar: 2}

Parameters:

  • binds (Hash, nil) —

    the bind parameters

Returns:

  • (Q) —

    a new query object with the binds replaced



1026
1027
1028
 
# File 'lib/app_query.rb', line 1026

def with_binds(**binds)
  deep_dup(binds:)
end

#with_cast(cast) ⇒ Q

Returns a new query with different cast settings.

Examples:

query = AppQuery("SELECT created_at FROM users")
query.with_cast(false).select_all  # disable casting

Parameters:

  • cast (Boolean, Hash, Array) —

    the new cast configuration

Returns:

  • (Q) —

    a new query object with the specified cast settings



1052
1053
1054
 
# File 'lib/app_query.rb', line 1052

def with_cast(cast)
  deep_dup(cast:)
end

#with_select(sql) ⇒ Q

Returns a new query with a modified SELECT statement.

Wraps the current SELECT in a numbered CTE and applies the new SELECT. CTEs are named _, _1, _2, etc. Use :_ in the new SELECT to reference the previous result.

Examples:

Single transformation

AppQuery("SELECT * FROM users").with_select("SELECT COUNT(*) FROM :_")
# => "WITH _ AS (\n  SELECT * FROM users\n)\nSELECT COUNT(*) FROM _"

Chained transformations

AppQuery("SELECT * FROM users")
  .with_select("SELECT * FROM :_ WHERE active")
  .with_select("SELECT COUNT(*) FROM :_")
# => WITH _ AS (SELECT * FROM users),
#         _1 AS (SELECT * FROM _ WHERE active)
#    SELECT COUNT(*) FROM _1

Parameters:

  • sql (String, nil) —

    the new SELECT statement (nil returns self)

Returns:

  • (Q) —

    a new query object with the modified SELECT



1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
 
# File 'lib/app_query.rb', line 1084

def with_select(sql)
  return self if sql.nil?

  # First CTE is "_", then "_1", "_2", etc.
  current_cte = (cte_depth == 0) ? "_" : "_#{cte_depth}"

  # Replace :_ with the current CTE name
  processed_sql = sql.gsub(/:_\b/, current_cte)

  # Wrap current SELECT in numbered CTE (indent all lines, strip trailing whitespace)
  indented_select = select.rstrip.gsub("\n", "\n  ")
  new_cte = "#{current_cte} AS (\n  #{indented_select}\n)"

  append_cte(new_cte).then do |q|
    # Replace the SELECT token with processed_sql and increment depth
    new_sql = q.tokens.each_with_object([]) do |token, acc|
      v = (token[:t] == "SELECT") ? processed_sql : token[:v]
      acc << v
    end.join
    q.deep_dup(sql: new_sql, cte_depth: cte_depth + 1)
  end
end

#with_sql(sql) ⇒ Q

Returns a new query with different SQL.

Parameters:

  • sql (String) —

    the new SQL string

Returns:

  • (Q) —

    a new query object with the specified SQL



1060
1061
1062
 
# File 'lib/app_query.rb', line 1060

def with_sql(sql)
  deep_dup(sql:)
end