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) ⇒ 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



333
334
335
336
337
338
339
340
341
 
# File 'lib/app_query.rb', line 333

def initialize(sql, name: nil, filename: nil, binds: {}, cast: true, cte_depth: 0)
  @sql = sql
  @name = name
  @filename = filename
  @binds = binds
  @cast = cast
  @cte_depth = cte_depth
  @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



318
319
320
 
# File 'lib/app_query.rb', line 318

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



318
319
320
 
# File 'lib/app_query.rb', line 318

def cast
  @cast
end

#cte_depth ⇒ Object (readonly)

Returns the value of attribute cte_depth.



343
344
345
 
# File 'lib/app_query.rb', line 343

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



318
319
320
 
# File 'lib/app_query.rb', line 318

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



318
319
320
 
# File 'lib/app_query.rb', line 318

def name
  @name
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



318
319
320
 
# File 'lib/app_query.rb', line 318

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



970
971
972
 
# File 'lib/app_query.rb', line 970

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



613
614
615
 
# File 'lib/app_query.rb', line 613

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



1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
 
# File 'lib/app_query.rb', line 1138

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



655
656
657
658
659
 
# File 'lib/app_query.rb', line 655

def column(c, s = nil, binds: {}, unique: false)
  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



676
677
678
 
# File 'lib/app_query.rb', line 676

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:



854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
 
# File 'lib/app_query.rb', line 854

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



593
594
595
 
# File 'lib/app_query.rb', line 593

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



1078
1079
1080
1081
1082
1083
1084
 
# File 'lib/app_query.rb', line 1078

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



941
942
943
 
# File 'lib/app_query.rb', line 941

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) ⇒ Object 



362
363
364
 
# File 'lib/app_query.rb', line 362

def deep_dup(sql: self.sql, name: self.name, filename: self.filename, binds: self.binds.dup, cast: self.cast, cte_depth: self.cte_depth)
  self.class.new(sql, name:, filename:, binds:, cast:, cte_depth:)
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:



798
799
800
801
802
803
804
805
806
807
808
809
810
 
# File 'lib/app_query.rb', line 798

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:



712
713
714
 
# File 'lib/app_query.rb', line 712

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

#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



696
697
698
 
# File 'lib/app_query.rb', line 696

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



734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
 
# File 'lib/app_query.rb', line 734

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:



513
514
515
 
# File 'lib/app_query.rb', line 513

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



628
629
630
 
# File 'lib/app_query.rb', line 628

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



1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
 
# File 'lib/app_query.rb', line 1102

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



1057
1058
1059
 
# File 'lib/app_query.rb', line 1057

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:



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

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



1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
 
# File 'lib/app_query.rb', line 1176

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



1046
1047
1048
 
# File 'lib/app_query.rb', line 1046

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:



463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
 
# File 'lib/app_query.rb', line 463

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)
    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? Also known as: first

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:



492
493
494
 
# File 'lib/app_query.rb', line 492

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:



571
572
573
 
# File 'lib/app_query.rb', line 571

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:



530
531
532
 
# File 'lib/app_query.rb', line 530

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:



552
553
554
555
556
557
 
# File 'lib/app_query.rb', line 552

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

#to_arel ⇒ Object 



345
346
347
348
349
350
351
352
 
# File 'lib/app_query.rb', line 345

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



1212
1213
1214
 
# File 'lib/app_query.rb', line 1212

def to_s
  @sql
end

#tokenizer ⇒ Tokenizer

Returns the tokenizer instance for this query.

Returns:



926
927
928
 
# File 'lib/app_query.rb', line 926

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:



919
920
921
 
# File 'lib/app_query.rb', line 919

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:



772
773
774
775
776
777
778
779
780
781
782
783
784
 
# File 'lib/app_query.rb', line 772

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



956
957
958
 
# File 'lib/app_query.rb', line 956

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



982
983
984
 
# File 'lib/app_query.rb', line 982

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



1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
 
# File 'lib/app_query.rb', line 1014

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



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

def with_sql(sql)
  deep_dup(sql:)
end