Class: Cybertrain::Relation

Inherits:
Object
  • Object
show all
Defined in:
cybertrain/relation.rb,
cybertrain/relation/aggregates.rb

Overview

A lazily built SELECT over one table: nothing runs until a method that needs rows (#count, #exists?, to_a, first, each, …).

Each generated model gets its own subclass, <Model>Relation, which adds the chainable query methods (where, not, where_sql, where_like, where_between, or, order, order_sql, limit, offset, distinct, none, having_sql, includes, preload), dup, and the methods that return records (to_a, each, first, last, find, find_by, find_by!, find_or_create_by, find_or_initialize_by, destroy_all, find_each, size, empty?, and preloaded, which a has_many reader uses after includes); see ArticleRelation. The model class answers each query method too (Article.where(...) is Article.all.where(...)). The methods documented here are the ones every relation shares.

The chain methods change the relation they are called on and return it (they do not copy it), so keep one relation per query:

scope = Comment.where(article_id: article.id)
recent = scope.order("created_at DESC").limit(10).to_a
# scope itself is now ordered and limited too

To branch several queries from one base, copy it first: dup (on the generated subclass, ArticleRelation#dup) answers an independent relation of the same class, as lazy as its source, and the two share no conditions afterwards (docs/design.md D72):

scope = Comment.where(article_id: article.id)
recent = scope.dup.order("created_at DESC").limit(10).to_a
approved = scope.dup.where(approved: true).count
# scope is still only `article_id = ?`

Implementation: the subclass’s chain methods wrap the setters below and return self, so Post.where(...).first is typed Post|nil: a base-class method that returned self would be typed as the base (spikes/NOTES.md rule 5), which is why these setters return nil.

Direct Known Subclasses

ArticleRelation, CommentRelation

Instance Method Summary collapse

Instance Method Details

#average(column) ⇒ Float?

The mean of column (AVG) as a Float, nil when no row matches or every value is NULL.

Examples:

Reading.where(kind: "a").average(:value)  # => 1.5

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

  • (Float, nil)

Raises:

  • (ArgumentError) —

    when column is not a column name



221
222
223
# File 'cybertrain/relation/aggregates.rb', line 221

def average(column)
  Cast.float_or_nil(aggregate_cell("average", "AVG", column))
end

#average_by(group, column) ⇒ Hash{String => Float, nil}

#average per value of group.

Examples:

Reading.all.having_sql("COUNT(*) > ?", [1]).average_by(:kind, :value)

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

  • (Hash{String => Float, nil})

Raises:

  • (ArgumentError) —

    when either is not a column name



367
368
369
# File 'cybertrain/relation/aggregates.rb', line 367

def average_by(group, column)
  float_cells(grouped_by("average_by", "AVG", group, column))
end

#average_sql(expr) ⇒ Float?

#average over a trusted SQL expression (see #sum_float_sql).

Examples:

Reading.all.average_sql("value * 2")

Parameters:

  • expr (String) —

    SQL text

Returns:

  • (Float, nil)


260
261
262
# File 'cybertrain/relation/aggregates.rb', line 260

def average_sql(expr)
  Cast.float_or_nil(aggregate_expr("AVG", expr))
end

#count ⇒ Integer

Counts the rows the relation would return: with a limit or offset the count runs over the windowed subquery (Post.limit(2).count is 2).

Examples:

Comment.where(article_id: article.id).count

Returns:



604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
# File 'cybertrain/relation.rb', line 604

def count
  return 0 if @none

  sql = if @distinct
          # Over the DISTINCT rows, not `SELECT DISTINCT 1` (one row); with
          # `*` every row is distinct, so this equals the plain count.
          inner = select_sql("*", "", @limit, @offset)
          "SELECT COUNT(*) AS n FROM (#{inner})"
        elsif @limit >= 0 || @offset > 0
          "SELECT COUNT(*) AS n FROM (#{select_sql("1", "", @limit, @offset)})"
        else
          select_sql("COUNT(*) AS n", "", -1, 0)
        end
  binds = @binds
  found = Cybertrain::DB.with { |c| c.execute(sql, binds) }
  Cast.int(found[0]["n"])
end

#count_by(column) ⇒ Hash{String => Integer}

How many matching rows hold each value of column (SELECT col, COUNT(*) ... GROUP BY col), keys through Cast.str in ascending order. NULL and "" share the key "". With a limit or offset the window is grouped (as #count counts it).

Examples:

Comment.all.count_by(:article_id)  # => {"1" => 2, "4" => 1}

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when column is not a column name



108
109
110
# File 'cybertrain/relation/aggregates.rb', line 108

def count_by(column)
  int_totals(grouped_cell("COUNT", Relation.checked_column("count_by", column), "*"))
end

#count_by_sql(expr) ⇒ Hash{String => Integer}

#count_by over a raw SQL expression, for buckets a column name cannot say. The expression is used as written, like ArticleRelation#order_sql’s: never build it from request data.

Examples:

hour buckets

Comment.all.count_by_sql("strftime('%Y-%m-%dT%H', created_at)")
# => {"2026-10-25T09" => 3, "2026-10-25T10" => 1}

Parameters:

  • expr (String) —

    SQL text

Returns:



121
122
123
# File 'cybertrain/relation/aggregates.rb', line 121

def count_by_sql(expr)
  int_totals(grouped_cell("COUNT", expr, "*"))
end

#delete_all ⇒ Integer

Deletes the matching rows and returns how many went, with one DELETE: no model is loaded and no callback runs. With a limit or offset only the rows of that window go (Post.limit(1).delete_all is one row, as count/exists? read the same window): SQLite has no DELETE … LIMIT in a default build, so the window is picked by an id subselect (the quoted primary key); the where binds appear once, inside it.

Examples:

Comment.where(article_id: article.id).delete_all

Returns:

  • (Integer) —

    the number of rows deleted



648
649
650
651
652
653
654
655
656
657
658
# File 'cybertrain/relation.rb', line 648

def delete_all
  return 0 if @none

  sql = +"DELETE FROM #{Relation.quote_ident(@table)}"
  sql << write_scope_sql
  binds = @binds
  Cybertrain::DB.with do |c|
    c.execute(sql, binds)
    c.changes
  end
end

#exists? ⇒ Boolean

Whether the relation matches any row (a SELECT 1 ... LIMIT 1). Conditions go on the relation, not the call: Post.where(slug: s).exists?.

Returns:

  • (Boolean)


626
627
628
629
630
631
632
633
634
635
636
# File 'cybertrain/relation.rb', line 626

def exists?
  return false if @limit == 0 || @none

  # Under distinct, `SELECT DISTINCT 1` is one row, so an offset would
  # skip past every match: probe whole rows, as page does.
  probe = @distinct ? "*" : "1 AS one"
  sql = select_sql(probe, "", 1, @offset)
  binds = @binds
  found = Cybertrain::DB.with { |c| c.execute(sql, binds) }
  !found.empty?
end

#ids ⇒ Array<Integer>

The ids of the matching rows, in the relation’s order (by id when it has none).

Examples:

Comment.where(article_id: article.id).ids  # => [3, 7]

Returns:



921
922
923
924
925
926
927
928
929
930
931
932
933
934
# File 'cybertrain/relation.rb', line 921

def ids
  id = Relation.quote_ident("id")
  columns = "#{id} AS v"
  sql = select_sql(columns, order_or_id, @limit, @offset)
  binds = @binds
  found = Cybertrain::DB.with { |c| c.execute(sql, binds) }
  out = Array.new(0) { 0 }
  i = 0
  while i < found.size
    out << Cast.int(found[i]["v"])
    i += 1
  end
  out
end

#increment(column, by: 1) ⇒ Integer

Adds by to column in every matching row with one UPDATE ... SET col = col + ?: no model is loaded, no callback or validation runs, updated_at is left alone. A limit or offset picks the rows by an id subselect, as #delete_all does.

Examples:

Article.where(id: article.id).increment(:views)
Stat.where(project_id: 1).increment(:calls, by: 5)

Parameters:

  • column (Symbol, String) —

    a column name

  • by (Integer, Float) (defaults to: 1)

Returns:

  • (Integer) —

    the number of rows updated

Raises:

  • (ArgumentError) —

    when column is not a column name, or is an encrypted column



790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
# File 'cybertrain/relation.rb', line 790

def increment(column, by: 1)
  quoted = Relation.checked_column("increment", column)
  # A payload plus 1 would destroy it.
  check_not_encrypted("increment", column.to_s)
  table = Relation.quote_ident(@table)
  sql = +"UPDATE #{table} SET #{quoted} = #{quoted} + ?"
  binds = [nil, 0, 0.0, ""]
  binds.clear
  binds << Cast.to_sql(by)
  sql << write_scope_sql
  i = 0
  while i < @binds.size
    binds << @binds[i]
    i += 1
  end
  Cybertrain::DB.with do |c|
    c.execute(sql, binds)
    c.changes
  end
end

#maximum_float(column) ⇒ Float?

#maximum_int of a float or decimal column, as a Float or nil.

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

  • (Float, nil)

Raises:

  • (ArgumentError) —

    when column is not a column name



198
199
200
# File 'cybertrain/relation/aggregates.rb', line 198

def maximum_float(column)
  Cast.float_or_nil(aggregate_cell("maximum_float", "MAX", column))
end

#maximum_float_by(group, column) ⇒ Hash{String => Float, nil}

#maximum_float per value of group.

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

  • (Hash{String => Float, nil})

Raises:

  • (ArgumentError) —

    when either is not a column name



343
344
345
# File 'cybertrain/relation/aggregates.rb', line 343

def maximum_float_by(group, column)
  float_cells(grouped_by("maximum_float_by", "MAX", group, column))
end

#maximum_float_sql(expr) ⇒ Float?

#maximum_float over a trusted SQL expression (see #sum_float_sql).

Parameters:

  • expr (String) —

    SQL text

Returns:

  • (Float, nil)


250
251
252
# File 'cybertrain/relation/aggregates.rb', line 250

def maximum_float_sql(expr)
  Cast.float_or_nil(aggregate_expr("MAX", expr))
end

#maximum_int(column) ⇒ Integer?

The largest value of an integer column (MAX), nil when no row matches or every value is NULL.

Examples:

Article.all.maximum_int(:views)  # => 10

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when column is not a column name



189
190
191
# File 'cybertrain/relation/aggregates.rb', line 189

def maximum_int(column)
  Cast.int_or_nil(aggregate_cell("maximum_int", "MAX", column))
end

#maximum_int_by(group, column) ⇒ Hash{String => Integer, nil}

#maximum_int per value of group.

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when either is not a column name



333
334
335
# File 'cybertrain/relation/aggregates.rb', line 333

def maximum_int_by(group, column)
  int_cells(grouped_by("maximum_int_by", "MAX", group, column))
end

#maximum_time(column) ⇒ Time?

#maximum_int of a datetime column, as a UTC Time or nil.

Examples:

Comment.where(article_id: article.id).maximum_time(:created_at)

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when column is not a column name



209
210
211
# File 'cybertrain/relation/aggregates.rb', line 209

def maximum_time(column)
  Cast.time_or_nil(aggregate_cell("maximum_time", "MAX", column))
end

#maximum_time_by(group, column) ⇒ Hash{String => Time, nil}

#maximum_time per value of group.

Examples:

the latest comment per article

Comment.all.maximum_time_by(:article_id, :created_at)

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

  • (Hash{String => Time, nil})

Raises:

  • (ArgumentError) —

    when either is not a column name



355
356
357
# File 'cybertrain/relation/aggregates.rb', line 355

def maximum_time_by(group, column)
  time_cells(grouped_by("maximum_time_by", "MAX", group, column))
end

#minimum_float(column) ⇒ Float?

#minimum_int of a float or decimal column, as a Float or nil.

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

  • (Float, nil)

Raises:

  • (ArgumentError) —

    when column is not a column name



167
168
169
# File 'cybertrain/relation/aggregates.rb', line 167

def minimum_float(column)
  Cast.float_or_nil(aggregate_cell("minimum_float", "MIN", column))
end

#minimum_float_by(group, column) ⇒ Hash{String => Float, nil}

#minimum_float per value of group.

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

  • (Hash{String => Float, nil})

Raises:

  • (ArgumentError) —

    when either is not a column name



313
314
315
# File 'cybertrain/relation/aggregates.rb', line 313

def minimum_float_by(group, column)
  float_cells(grouped_by("minimum_float_by", "MIN", group, column))
end

#minimum_float_sql(expr) ⇒ Float?

#minimum_float over a trusted SQL expression (see #sum_float_sql).

Parameters:

  • expr (String) —

    SQL text

Returns:

  • (Float, nil)


242
243
244
# File 'cybertrain/relation/aggregates.rb', line 242

def minimum_float_sql(expr)
  Cast.float_or_nil(aggregate_expr("MIN", expr))
end

#minimum_int(column) ⇒ Integer?

The smallest value of an integer column (MIN), nil when no row matches or every value is NULL. Under distinct the function reads MIN(DISTINCT col), as every aggregate here does. With a limit or offset the window is aggregated (as #count counts it).

Examples:

Article.where(published: true).minimum_int(:views)  # => 3

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when column is not a column name



158
159
160
# File 'cybertrain/relation/aggregates.rb', line 158

def minimum_int(column)
  Cast.int_or_nil(aggregate_cell("minimum_int", "MIN", column))
end

#minimum_int_by(group, column) ⇒ Hash{String => Integer, nil}

#minimum_int per value of group; a group whose values are all NULL maps to nil.

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when either is not a column name



303
304
305
# File 'cybertrain/relation/aggregates.rb', line 303

def minimum_int_by(group, column)
  int_cells(grouped_by("minimum_int_by", "MIN", group, column))
end

#minimum_time(column) ⇒ Time?

#minimum_int of a datetime column, as a UTC Time or nil (the stored text sorts as the time does).

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when column is not a column name



177
178
179
# File 'cybertrain/relation/aggregates.rb', line 177

def minimum_time(column)
  Cast.time_or_nil(aggregate_cell("minimum_time", "MIN", column))
end

#minimum_time_by(group, column) ⇒ Hash{String => Time, nil}

#minimum_time per value of group.

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

  • (Hash{String => Time, nil})

Raises:

  • (ArgumentError) —

    when either is not a column name



323
324
325
# File 'cybertrain/relation/aggregates.rb', line 323

def minimum_time_by(group, column)
  time_cells(grouped_by("minimum_time_by", "MIN", group, column))
end

#page(number, per: 50) ⇒ Page

Windows the relation to page number of per rows and returns where that page sits: LIMIT per OFFSET (number - 1) * per replaces any limit/offset set before, so call it last, after where and order. The rows themselves load when the relation does (to_a, each), as typed records. number is clamped to 1..Params.max_page (Config#max_page, 10000 by default), so the offset never passes (max_page - 1) * per; a page past the last row is empty.

Whether an older page exists is asked with one SELECT 1 ... LIMIT per + 1 over the same window, not a COUNT(*) over the whole table.

Examples:

@articles = Article.order("created_at DESC")
@page = @articles.page(params.page_number, per: 20)

Parameters:

  • number (Integer) —

    the 1-based page, usually Params#page_number

  • per (Integer) (defaults to: 50) —

    rows per page, 1 to Page::MAX_PER

Returns:

Raises:

  • (ArgumentError) —

    when per is out of range (a programmer error)



704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
# File 'cybertrain/relation.rb', line 704

def page(number, per: 50)
  drop_preloaded
  if per < 1 || per > Page::MAX_PER
    raise ArgumentError, "page: per must be from 1 to #{Page::MAX_PER} (got #{per})"
  end

  n = Page.clamp_number(number, Params.max_page)
  skip = (n - 1) * per
  # Under distinct, `SELECT DISTINCT 1` would be one row.
  probe = @distinct ? "*" : "1 AS one"
  sql = select_sql(probe, "", per + 1, skip)
  binds = @binds
  found = Cybertrain::DB.with { |c| c.execute(sql, binds) }
  @limit = per
  @offset = skip
  Page.new(n, per, found.size > per)
end

#pick(*columns) ⇒ Array<Integer, Float, String, Boolean, Time, Date, nil>?

The first row #pluck would return (LIMIT 1, the relation itself keeps its limit), or nil when nothing matches.

Examples:

Reading.order("id").pick(:id, :value)  # => [1, 1.0]

Parameters:

  • columns (Array<Symbol, String>) —

    two or more column names

Returns:

Raises:

  • (ArgumentError) —

    as #pluck does



93
94
95
96
# File 'cybertrain/relation/aggregates.rb', line 93

def pick(*columns)
  found = cell_rows("pick", columns, @limit == 0 ? 0 : 1)
  found.empty? ? nil : found[0]
end

#pluck(*columns) ⇒ Array<Array<Integer, Float, String, Boolean, Time, Date, nil>>

Two or more columns of the matching rows, in the relation’s order, one Array of cells per row. Each cell is cast by the column’s type (#column_type): an integer column gives an Integer, a float or decimal a Float, a boolean true/false, a datetime a UTC Time, a date a Date, any other (string, text, json, binary) the String stored; NULL is nil. One column has a typed reader instead (#pluck_str, #pluck_int, #pluck_float, #ids), since Spinel gives a method one return type.

Examples:

Reading.order("id").pluck(:observed_at, :value)
# => [[2026-10-01 09:00:00 UTC, 1.0], [2026-10-02 12:00:00 UTC, 2.0]]

Parameters:

  • columns (Array<Symbol, String>) —

    two or more column names

Returns:

Raises:

  • (ArgumentError) —

    with fewer than two columns, or a name that is not a column name



81
82
83
# File 'cybertrain/relation/aggregates.rb', line 81

def pluck(*columns)
  cell_rows("pluck", columns, @limit)
end

#pluck_float(column) ⇒ Array<Float>

#pluck_str as Floats (NULL is 0.0, through Cast.float).

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

  • (Array<Float>)

Raises:

  • (ArgumentError) —

    when column is not a column name



54
55
56
57
58
59
60
61
62
63
# File 'cybertrain/relation/aggregates.rb', line 54

def pluck_float(column)
  found = pluck_rows("pluck_float", column)
  out = Array.new(0) { 0.0 }
  i = 0
  while i < found.size
    out << Cast.float(found[i]["v"])
    i += 1
  end
  out
end

#pluck_int(column) ⇒ Array<Integer>

#pluck_str as Integers (NULL is 0, through Cast.int).

Examples:

Comment.where(article_id: article.id).pluck_int(:id)  # => [3, 7]

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when column is not a column name



38
39
40
41
42
43
44
45
46
47
# File 'cybertrain/relation/aggregates.rb', line 38

def pluck_int(column)
  found = pluck_rows("pluck_int", column)
  out = Array.new(0) { 0 }
  i = 0
  while i < found.size
    out << Cast.int(found[i]["v"])
    i += 1
  end
  out
end

#pluck_str(column) ⇒ Array<String>

One column of the matching rows, in the relation’s order, as Strings (NULL is "", a DATETIME its stored text). Every pluck reads the stored cells, so an encrypted column (encrypts) answers its payloads ("1:..."), not the text: load the records to decrypt.

Examples:

Article.all.order("title").pluck_str(:title)  # => ["Bye", "Hello"]

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

  • (Array<String>)

Raises:

  • (ArgumentError) —

    when column is not a column name



20
21
22
23
24
25
26
27
28
29
# File 'cybertrain/relation/aggregates.rb', line 20

def pluck_str(column)
  found = pluck_rows("pluck_str", column)
  out = Array.new(0) { "" }
  i = 0
  while i < found.size
    out << Cast.str(found[i]["v"])
    i += 1
  end
  out
end

#sum_float(column) ⇒ Float

The sum of column as a Float (SQLite’s TOTAL), 0.0 when there are no rows.

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

  • (Float)

Raises:

  • (ArgumentError) —

    when column is not a column name



144
145
146
# File 'cybertrain/relation/aggregates.rb', line 144

def sum_float(column)
  Cast.float(aggregate_cell("sum_float", "TOTAL", column))
end

#sum_float_by(group, column) ⇒ Hash{String => Float}

The sum of column per value of group as a Float (TOTAL).

Examples:

Reading.all.sum_float_by(:kind, :value)  # => {"a" => 3.0, "b" => 1.5}

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

  • (Hash{String => Float})

Raises:

  • (ArgumentError) —

    when either is not a column name



292
293
294
# File 'cybertrain/relation/aggregates.rb', line 292

def sum_float_by(group, column)
  float_totals(grouped_by("sum_float_by", "TOTAL", group, column))
end

#sum_float_sql(expr) ⇒ Float

#sum_float over a SQL expression (TOTAL(expr)), 0.0 when no row matches. The expression is used as written, like ArticleRelation#order_sql’s: trusted SQL, never built from request data (bind values through where).

Examples:

Line.where(order_id: order.id).sum_float_sql("price * quantity")

Parameters:

  • expr (String) —

    SQL text

Returns:

  • (Float)


234
235
236
# File 'cybertrain/relation/aggregates.rb', line 234

def sum_float_sql(expr)
  Cast.float(aggregate_expr("TOTAL", expr))
end

#sum_int(column) ⇒ Integer

The sum of column over the matching rows (SUM), 0 when there are none or every value is NULL. A REAL column’s sum is truncated: use #sum_float for those.

Examples:

Stat.where(project_id: 1).sum_int(:calls)

Parameters:

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when column is not a column name



134
135
136
# File 'cybertrain/relation/aggregates.rb', line 134

def sum_int(column)
  Cast.int(aggregate_cell("sum_int", "SUM", column))
end

#sum_int_by(group, column) ⇒ Hash{String => Integer}

The sum of an integer column per value of group (SUM), 0 for a group whose values are all NULL.

Examples:

Reading.all.sum_int_by(:kind, :views)  # => {"a" => 6, "b" => 7}

Parameters:

  • group (Symbol, String) —

    a column name

  • column (Symbol, String) —

    a column name

Returns:

Raises:

  • (ArgumentError) —

    when either is not a column name



280
281
282
# File 'cybertrain/relation/aggregates.rb', line 280

def sum_int_by(group, column)
  int_totals(grouped_by("sum_int_by", "SUM", group, column))
end

#to_sql ⇒ String

The SELECT this relation runs, with ? placeholders (see binds). Handy in a test or a log line.

Returns:

  • (String)


585
586
587
# File 'cybertrain/relation.rb', line 585

def to_sql
  select_sql("*", @order, @limit, @offset)
end

#update_all(hash) ⇒ Integer

Sets each column to its value in every matching row with one UPDATE ... SET a = ?, b = ?, each value cast by its column’s type as save stores it (typed_bind): a boolean as 1/0 (a form’s "false" too), a Hash or JSON text as JSON (invalid text as {}), a Date or YYYY-MM-DD text as that date, a Time or time text in the storage format, a decimal rounded to its scale, binary bytes as a BLOB, other text as save reads it ("abc" is 0 in an integer column). nil stores NULL in every column; "" stores NULL in an integer, float, decimal, boolean, date, datetime or json column (so a NOT NULL one refuses it, where save would store 0 / false / {}) and "" in a string, text or binary one; date or time text that does not parse stores NULL too. No model is loaded, no callback or validation runs, updated_at is left alone. A limit or offset picks the rows by an id subselect, as #delete_all does.

Examples:

Comment.where(article_id: article.id).update_all(approved: true)

Parameters:

  • hash (Hash{Symbol => Object}) —

    column => value

Returns:

  • (Integer) —

    the number of rows updated

Raises:

  • (ArgumentError) —

    on an empty Hash, a key that is not a column name, or an encrypted column (which only a save can encrypt)



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
912
913
# File 'cybertrain/relation.rb', line 879

def update_all(hash)
  raise ArgumentError, "update_all: no attributes" if hash.empty?
  return 0 if @none

  sets = Array.new(0) { "" }
  binds = [nil, 0, 0.0, "", DB::Blob.new("")]
  binds.clear
  hash.each do |key, value|
    quoted = Relation.checked_column("update_all", key)
    sets << "#{quoted} = ?"
    name = key.to_s
    check_not_encrypted("update_all", name)
    type = column_type(name)
    # A hand-written relation that knows no types binds as where does
    # (its binary_column? included).
    if type == :unknown
      binds << bind_for(name, value)
    else
      binds << Relation.typed_bind(type, decimal_scale(name), value)
    end
  end
  table = Relation.quote_ident(@table)
  set_list = sets.join(", ")
  sql = +"UPDATE #{table} SET #{set_list}"
  sql << write_scope_sql
  i = 0
  while i < @binds.size
    binds << @binds[i]
    i += 1
  end
  Cybertrain::DB.with do |c|
    c.execute(sql, binds)
    c.changes
  end
end

#update_all_sql(sql, binds = []) ⇒ Integer

Sets columns by SQL in every matching row with one UPDATE ... SET <sql>, for what #update_all’s Hash cannot say: a value computed from the row, or several columns decided together (every SET expression reads the row as it was before the statement). ? placeholders take binds in order (a Time as its storage text, true/false as 1/0), then the relation’s own. The SET list is SQL as written: quote names that need it, and leave encrypted columns alone. No model is loaded, no callback or validation runs, updated_at is left alone. A limit or offset picks the rows by an id subselect, as #delete_all does. (Rails’ update_all(["sql", binds]).)

Examples:

User.where(id: user.id).update_all_sql(
  "failed_attempts = CASE WHEN failed_attempts + 1 >= ? THEN 0 ELSE failed_attempts + 1 END", [10])

Parameters:

  • sql (String) —

    the SET list

  • binds (Array) (defaults to: []) —

    one value per ? in sql

Returns:

  • (Integer) —

    the number of rows updated

Raises:

  • (ArgumentError) —

    on an empty SET list or an Array bind



829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
# File 'cybertrain/relation.rb', line 829

def update_all_sql(sql, binds = [])
  raise ArgumentError, "update_all_sql: no SET list" if sql.strip.empty?

  all = [nil, 0, 0.0, "", DB::Blob.new("")]
  all.clear
  binds.each do |v|
    case v
    when Time then nil # before `when Array` (rule 8)
    when Array
      raise ArgumentError, "update_all_sql: an Array bind is not expanded"
    end
    all << Cast.to_sql(v)
  end
  return 0 if @none

  table = Relation.quote_ident(@table)
  text = +"UPDATE #{table} SET #{sql}"
  text << write_scope_sql
  i = 0
  while i < @binds.size
    all << @binds[i]
    i += 1
  end
  Cybertrain::DB.with do |c|
    c.execute(text, all)
    c.changes
  end
end