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



292
293
294
295
296
297
298
299
300
 
# File 'lib/app_query.rb', line 292

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



277
278
279
 
# File 'lib/app_query.rb', line 277

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



277
278
279
 
# File 'lib/app_query.rb', line 277

def cast
  @cast
end

#cte_depth ⇒ Object (readonly)

Returns the value of attribute cte_depth.



302
303
304
 
# File 'lib/app_query.rb', line 302

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



277
278
279
 
# File 'lib/app_query.rb', line 277

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



277
278
279
 
# File 'lib/app_query.rb', line 277

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



277
278
279
 
# File 'lib/app_query.rb', line 277

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



881
882
883
 
# File 'lib/app_query.rb', line 881

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



528
529
530
 
# File 'lib/app_query.rb', line 528

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



1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
 
# File 'lib/app_query.rb', line 1050

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



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

def column(c, s = nil, binds: {}, unique: false)
  quoted_column = ActiveRecord::Base.connection.quote_column_name(c)
  with_select(s).select_all("SELECT #{unique ? "DISTINCT" : ""} #{quoted_column} AS column FROM :_", binds:).column("column")
end

#columns(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").columns
# => ["id", "name", "email"]

From a CTE

AppQuery("WITH t(a, b) AS (VALUES (1, 2)) SELECT * FROM t").columns
# => ["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



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

def columns(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

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:



765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
 
# File 'lib/app_query.rb', line 765

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



508
509
510
 
# File 'lib/app_query.rb', line 508

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



989
990
991
992
993
994
995
996
 
# File 'lib/app_query.rb', line 989

def cte(name)
  name = name.to_s
  unless cte_names.include?(name)
    raise ArgumentError, "Unknown CTE #{name.inspect}. Available: #{cte_names.inspect}"
  end
  quoted = ActiveRecord::Base.connection.quote_table_name(name)
  with_select("SELECT * FROM #{quoted}")
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



852
853
854
 
# File 'lib/app_query.rb', line 852

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 



321
322
323
 
# File 'lib/app_query.rb', line 321

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:



712
713
714
715
716
717
718
719
720
721
722
723
724
 
# File 'lib/app_query.rb', line 712

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:



626
627
628
 
# File 'lib/app_query.rb', line 626

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



610
611
612
 
# File 'lib/app_query.rb', line 610

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



648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
 
# File 'lib/app_query.rb', line 648

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

#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



543
544
545
 
# File 'lib/app_query.rb', line 543

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



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

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



968
969
970
 
# File 'lib/app_query.rb', line 968

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:



362
363
364
365
366
367
368
369
 
# File 'lib/app_query.rb', line 362

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



1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
 
# File 'lib/app_query.rb', line 1088

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



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

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:



422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
 
# File 'lib/app_query.rb', line 422

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:



451
452
453
 
# File 'lib/app_query.rb', line 451

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:



486
487
488
 
# File 'lib/app_query.rb', line 486

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:



469
470
471
 
# File 'lib/app_query.rb', line 469

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

#to_arel ⇒ Object 



304
305
306
307
308
309
310
311
 
# File 'lib/app_query.rb', line 304

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



1124
1125
1126
 
# File 'lib/app_query.rb', line 1124

def to_s
  @sql
end

#tokenizer ⇒ Tokenizer

Returns the tokenizer instance for this query.

Returns:



837
838
839
 
# File 'lib/app_query.rb', line 837

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:



830
831
832
 
# File 'lib/app_query.rb', line 830

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:



686
687
688
689
690
691
692
693
694
695
696
697
698
 
# File 'lib/app_query.rb', line 686

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



867
868
869
 
# File 'lib/app_query.rb', line 867

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



893
894
895
 
# File 'lib/app_query.rb', line 893

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



925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
 
# File 'lib/app_query.rb', line 925

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



901
902
903
 
# File 'lib/app_query.rb', line 901

def with_sql(sql)
  deep_dup(sql:)
end