Class: Simple::SQL::Scope

Inherits:
Object
  • Object
show all
Defined in:
lib/simple/sql/scope.rb,
lib/simple/sql/scope/count.rb,
lib/simple/sql/scope/order.rb,
lib/simple/sql/scope/filters.rb,
lib/simple/sql/scope/pagination.rb,
lib/simple/sql/scope/count_by_groups.rb

Overview

rubocop:disable Metrics/AbcSize rubocop:disable Metrics/MethodLength

Constant Summary collapse

SELF = 
self
EXACT_COUNT_THRESHOLD = 
10_000

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(sql, args = []) ⇒ Scope

Build a scope object

This call supports a few variants:

Simple::SQL::Scope.new("SELECT * FROM mytable")
Simple::SQL::Scope.new(table: "mytable", select: "*")

The second option also allows one to pass in more options, like the following:

Simple::SQL::Scope.new(table: "mytable", select: "*", where: { id: 1, foo: "bar" }, order_by: "id desc")



29
30
31
32
33
34
35
36
37
38
39
40
 
# File 'lib/simple/sql/scope.rb', line 29

def initialize(sql, args = [])
  expect! sql => [String, Hash]

  @sql     = nil
  @args    = args
  @filters = []

  case sql
  when String then @sql = sql
  when Hash then initialize_from_hash(sql)
  end
end

Instance Attribute Details

#args ⇒ Object (readonly)

Returns the value of attribute args.



15
16
17
 
# File 'lib/simple/sql/scope.rb', line 15

def args
  @args
end

#page ⇒ Object (readonly)

Returns the value of attribute page.



16
17
18
 
# File 'lib/simple/sql/scope.rb', line 16

def page
  @page
end

#per ⇒ Object (readonly)

Returns the value of attribute per.



16
17
18
 
# File 'lib/simple/sql/scope.rb', line 16

def per
  @per
end

Instance Method Details

#count ⇒ Object

Returns the exact count of matching records



5
6
7
8
 
# File 'lib/simple/sql/scope/count.rb', line 5

def count
  sql = order_by(nil).to_sql(pagination: false)
  ::Simple::SQL.ask("SELECT COUNT(*) FROM (#{sql}) _total_count", *args)
end

#count_by(sql_fragment) ⇒ Object 



38
39
40
41
42
43
 
# File 'lib/simple/sql/scope/count_by_groups.rb', line 38

def count_by(sql_fragment)
  sql = order_by(nil).to_sql(pagination: false)

  recs = ::Simple::SQL.all "SELECT #{sql_fragment} AS group, COUNT(*) AS count FROM (#{sql}) sq GROUP BY #{sql_fragment}", *args
  Hash[recs]
end

#enumerate_groups(sql_fragment) ⇒ Object

Potentially fast implementation of returning all different values for a specific group.

For example:

Scope.new("SELECT * FROM users").enumerate_groups("gender") -> [ "female", "male" ]

It is possible to enumerate over multiple attributes, for example:

scope.enumerate_groups fragment: "ARRAY[workflow, queue]"

In any case it is important that an index exists that the database can use to group by the sql_fragment, for example:

CREATE INDEX ix3 ON table((ARRAY[workflow, queue]));



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/simple/sql/scope/count_by_groups.rb', line 20

def enumerate_groups(sql_fragment)
  sql = order_by(nil).to_sql(pagination: false)

  _, max_cost = ::Simple::SQL.costs "SELECT MIN(#{sql_fragment}) FROM (#{sql}) sq", *args
  raise "enumerate_groups: takes too much time. Make sure to create a suitable index" if max_cost > 10_000

  groups = []
  var_name = "$#{@args.count + 1}"
  cur = ::Simple::SQL.ask "SELECT MIN(#{sql_fragment}) FROM (#{sql}) sq", *args

  while cur
    groups << cur
    cur = ::Simple::SQL.ask "SELECT MIN(#{sql_fragment}) FROM (#{sql}) sq"" WHERE #{sql_fragment} > #{var_name}", *args, cur
  end

  groups
end

#fast_count ⇒ Object

Returns the fast count of matching records

For counts larger than EXACT_COUNT_THRESHOLD this returns an estimate



13
14
15
16
17
18
19
 
# File 'lib/simple/sql/scope/count.rb', line 13

def fast_count
  estimate = estimated_count
  return estimate if estimate > EXACT_COUNT_THRESHOLD

  sql = order_by(nil).to_sql(pagination: false)
  ::Simple::SQL.ask("SELECT COUNT(*) FROM (#{sql}) _total_count", *args)
end

#fast_count_by(sql_fragment) ⇒ Object 



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/simple/sql/scope/count_by_groups.rb', line 45

def fast_count_by(sql_fragment)
  sql = order_by(nil).to_sql(pagination: false)

  _, max_cost = ::Simple::SQL.costs "SELECT COUNT(*) FROM (#{sql}) sq GROUP BY #{sql_fragment}", *args

  return count_by(sql_fragment) if max_cost < 10_000

  # iterate over all groups, estimating the count for each. If the count is
  # less than EXACT_COUNT_THRESHOLD we ask for the exact count in that and
  # similarily sparse groups.
  var_name = "$#{@args.count + 1}"

  counts = {}
  sparse_groups = []
  enumerate_groups(sql_fragment).each do |group|
    scope = ::Simple::SQL::Scope.new("SELECT * FROM (#{sql}) sq WHERE #{sql_fragment}=#{var_name}", *args, group)
    counts[group] = scope.send(:estimated_count)
    sparse_groups << group if estimated_count < EXACT_COUNT_THRESHOLD
  end

  # fetch exact counts in all sparse_groups
  unless sparse_groups.empty?
    sparse_counts = ::Simple::SQL.all <<~SQL, *args, sparse_groups
      SELECT #{sql_fragment} AS group, COUNT(*) AS count
      FROM (#{sql}) sq
      WHERE #{sql_fragment} = ANY(#{var_name})
      GROUP BY #{sql_fragment}
    SQL

    counts.update Hash[sparse_counts]
  end

  counts
end

#limit(count) ⇒ Object 



6
7
8
 
# File 'lib/simple/sql/scope/order.rb', line 6

def limit(count)
  duplicate.send(:limit!, count)
end

#order_by(sql_fragment) ⇒ Object 



2
3
4
 
# File 'lib/simple/sql/scope/order.rb', line 2

def order_by(sql_fragment)
  duplicate.send(:order_by!, sql_fragment)
end

#paginate(per:, page:) ⇒ Object

Set pagination



5
6
7
 
# File 'lib/simple/sql/scope/pagination.rb', line 5

def paginate(per:, page:)
  duplicate.send(:paginate!, per: per, page: page)
end

#paginated? ⇒ Boolean

Is this a paginated scope?

Returns:

  • (Boolean) 


10
11
12
 
# File 'lib/simple/sql/scope/pagination.rb', line 10

def paginated?
  not @per.nil?
end

#to_sql(pagination: :auto) ⇒ Object

generate a sql query

Raises:

  • (ArgumentError) 


78
79
80
81
82
83
84
85
86
87
 
# File 'lib/simple/sql/scope.rb', line 78

def to_sql(pagination: :auto)
  raise ArgumentError unless pagination == :auto || pagination == false

  sql = @sql
  sql = apply_filters(sql)
  sql = apply_order_and_limit(sql)
  sql = apply_pagination(sql, pagination: pagination)

  sql
end

#where(sql_fragment, arg = :__dummy__no__arg, placeholder: "?", jsonb: true) ⇒ Object

scope = Scope.new(“SELECT * FROM tablename”) scope = scope.where(id: 12) scope = scope.where(“id > ?”, 12)

In the second form the placeholder (usually a ‘?’) is being replaced with the numbered argument (since postgres is using $1, $2, etc.) If your SQL fragment uses ‘?’ as part of some fixed text you must use an alternative placeholder symbol:

scope = scope.where(“foo | ‘?’ = ‘^’”, match, placeholder: ‘^’)

If a hash is passed in as a search condition and the value to match is a hash, this is translated into a JSONB query, which matches each of the passed in keys against one of the passed in values.

scope = scope.where(metadata: { uid: 1, type: [“foo”, “bar”, “baz”] })

This feature can be disabled using the ‘jsonb: false` option.

scope = scope.where(metadata: { uid: 1 }, jsonb: false)



25
26
27
 
# File 'lib/simple/sql/scope/filters.rb', line 25

def where(sql_fragment, arg = :__dummy__no__arg, placeholder: "?", jsonb: true)
  duplicate.send(:where!, sql_fragment, arg, placeholder: placeholder, jsonb: jsonb)
end