Class: Simple::SQL::Connection::Scope

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

Overview

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

Defined Under Namespace

Modules: Search

Constant Summary collapse

EXACT_COUNT_THRESHOLD = 
10_000
SELF = 
self

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

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

:nodoc:



41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/simple/sql/connection/scope.rb', line 41

def initialize(sql, args = [], connection:) # :nodoc:
  expect! sql => [String, Hash]

  @connection = connection

  @sql   = nil
  @args  = args
  @where = []

  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.



35
36
37
 
# File 'lib/simple/sql/connection/scope.rb', line 35

def args
  @args
end

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



34
35
36
 
# File 'lib/simple/sql/connection/scope.rb', line 34

def connection
  @connection
end

#page ⇒ Object (readonly)

Returns the value of attribute page.



36
37
38
 
# File 'lib/simple/sql/connection/scope.rb', line 36

def page
  @page
end

#per ⇒ Object (readonly)

Returns the value of attribute per.



36
37
38
 
# File 'lib/simple/sql/connection/scope.rb', line 36

def per
  @per
end

#table_name ⇒ Object

when initialized with a Hash: contains table name



39
40
41
 
# File 'lib/simple/sql/connection/scope.rb', line 39

def table_name
  @table_name
end

Instance Method Details

#all(into: :struct) ⇒ Object 



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

def all(into: :struct)
  connection.all(self, into: into)
end

#count ⇒ Object

Returns the exact count of matching records



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

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

  @connection.ask("SELECT COUNT(*) FROM (#{sql}) _total_count", *args)
end

#count_by(sql_fragment) ⇒ Object 



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/simple/sql/connection/scope/count_by_groups.rb', line 25

def count_by(sql_fragment)
  expect! sql_fragment => String

  sql = order_by(nil).to_sql(pagination: false)

  recs = @connection.all "SELECT COUNT(*) AS count, #{sql_fragment} AS group FROM (#{sql}) sq GROUP BY #{sql_fragment}", *args

  # if we count by a single value (e.g. `count_by("role_id")`) each entry in recs consists of an array [group_value, count].
  # The resulting Hash will have entries of group_value => count.
  if recs.first&.length == 2
    recs.each_with_object({}) do |count_and_group, hsh|
      count, group = *count_and_group
      hsh[group] = count
    end
  else
    recs.each_with_object({}) do |count_and_group, hsh|
      count, *group = *count_and_group
      hsh[group] = count
    end
  end
end

#count_by_estimate(sql_fragment) ⇒ Object 



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
# File 'lib/simple/sql/connection/scope/count_by_groups.rb', line 75

def count_by_estimate(sql_fragment)
  expect! sql_fragment => String

  return count_by(sql_fragment) unless use_count_by_estimate?(sql_fragment)

  # iterate over all groups, estimating the count for each.
  #
  # For larger groups we'll use that estimate - preventing a full table scan.
  # Groups smaller than EXACT_COUNT_THRESHOLD are counted exactly - in the
  # hope that this query can be answered from an index.

  #
  # Usually Simple::SQL.all normalizes each result row into its first value,
  # if the row only consists of a single value. Here, however, we don't
  # know the width of a group; so to understand this we just add a dummy
  # value to the sql_fragment and then remove it again.
  #
  groups = enumerate_groups("1 AS __dummy__, #{sql_fragment}")
  groups = groups.each(&:shift)

  # no groups? well, then...
  return {} if groups.empty?

  #
  # The estimating code only works for groups of size 1. This is a limitation
  # of simple-sql - for larger groups we would have to be able to encode arrays
  # of arrays on their way to the postgres server. We are not able to do that
  # currently.
  #
  group_size = groups.first&.length
  if group_size > 1
    return count_by(sql_fragment)
  end

  # The code below only works for groups of size 1
  groups = groups.map(&:first)

  #
  # Now we estimate the count of entries in each group. For large groups we
  # just use the estimate - because it is usually pretty close to being correct.
  # Small groups are collected in the `sparse_groups` array, to be counted
  # exactly later on.
  #

  counts = {}

  sparse_groups = []
  base_sql = order_by(nil).to_sql(pagination: false)

  var_name = "$#{@args.count + 1}"

  groups.each do |group|
    scope = @connection.scope("SELECT * FROM (#{base_sql}) sq WHERE #{sql_fragment}=#{var_name}", args + [group])

    estimated_count = scope.send(:estimated_count)
    counts[group] = 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 = @connection.all <<~SQL, *args, sparse_groups
      SELECT #{sql_fragment} AS group, COUNT(*) AS count
      FROM (#{base_sql}) sq
      WHERE #{sql_fragment} = ANY(#{var_name})
      GROUP BY #{sql_fragment}
    SQL

    counts.update Hash[sparse_counts]
  end

  counts
end

#count_estimate ⇒ Object

Returns the fast count of matching records

For counts larger than EXACT_COUNT_THRESHOLD this returns an estimate



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

def count_estimate
  estimate = estimated_count
  return estimate if estimate > EXACT_COUNT_THRESHOLD

  sql = order_by(nil).to_sql(pagination: false)
  @connection.ask("SELECT COUNT(*) FROM (#{sql}) _total_count", *args)
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
 
# File 'lib/simple/sql/connection/scope/count_by_groups.rb', line 20

def enumerate_groups(sql_fragment)
  sql = order_by(nil).to_sql(pagination: false)
  @connection.all "SELECT DISTINCT #{sql_fragment} FROM (#{sql}) sq", *args
end

#explain ⇒ Object

returns the query plan as a Hash.



23
24
25
26
27
 
# File 'lib/simple/sql/connection/scope/count.rb', line 23

def explain
  sql = to_sql(pagination: false)
  explanation = @connection.ask("EXPLAIN (FORMAT JSON) #{sql}", *args).first || {}
  explanation["Plan"]
end

#first(into: :struct) ⇒ Object 



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

def first(into: :struct)
  connection.ask(self, into: into)
end

#limit(limit) ⇒ Object

Raises:

  • (ArgumentError) 


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

def limit(limit)
  raise ArgumentError, "limit must be >= 0" unless limit >= 0

  duplicate.send(:limit!, limit)
end

#offset(offset) ⇒ Object

Raises:

  • (ArgumentError) 


12
13
14
15
16
 
# File 'lib/simple/sql/connection/scope/order.rb', line 12

def offset(offset)
  raise ArgumentError, "offset must be >= 0" unless offset >= 0

  duplicate.send(:offset!, offset)
end

#order_by(sql_fragment) ⇒ Object 



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

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

#paginate(per:, page:) ⇒ Object

Set pagination

Raises:

  • (ArgumentError) 


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

def paginate(per:, page:)
  raise ArgumentError, "per must be > 0" unless per > 0
  raise ArgumentError, "page must be > 0" unless page > 0

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

#paginated? ⇒ Boolean

Is this a paginated scope?

Returns:

  • (Boolean) 


13
14
15
 
# File 'lib/simple/sql/connection/scope/pagination.rb', line 13

def paginated?
  not @per.nil?
end

#print(*args, io: STDOUT, width: :auto) ⇒ Object 



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

def print(*args, io: STDOUT, width: :auto)
  connection.print(self, *args, io: io, width: width)
end

#search(filters, dynamic_column: nil, table_name: nil) ⇒ Object 



3
4
5
6
7
8
9
10
11
 
# File 'lib/simple/sql/connection/scope/search.rb', line 3

def search(filters, dynamic_column: nil, table_name: nil)
  table_name ||= self.table_name
  raise "Cannot run search without a table_name setting. Please set table_name on this scope." unless table_name

  column_types = connection.reflection.column_types(table_name)
  dynamic_column ||= column_types.detect { |_column_name, column_type| column_type == "jsonb" }.first

  Search.search(self, filters, dynamic_column: dynamic_column, column_types: column_types)
end

#to_sql(pagination: :auto) ⇒ Object

generate a sql query

Raises:

  • (ArgumentError) 


94
95
96
97
98
99
100
101
102
103
 
# File 'lib/simple/sql/connection/scope.rb', line 94

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

  sql = @sql
  sql = apply_where(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/connection/scope/where.rb', line 25

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

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



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

def where!(first_arg, arg = :__dummy__no__arg, placeholder: "?", jsonb: true)
  if arg != :__dummy__no__arg
    where_sql_with_argument!(first_arg, arg, placeholder: placeholder)
  elsif first_arg.is_a?(Hash)
    where_hash!(first_arg, jsonb: jsonb)
  else
    where_sql!(first_arg)
  end

  self
end