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 



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

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

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

#count_by_estimate(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
79
 
# File 'lib/simple/sql/connection/scope/count_by_groups.rb', line 45

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

  cost = @connection.estimate_cost "SELECT COUNT(*) FROM (#{sql}) sq GROUP BY #{sql_fragment}", *args

  return count_by(sql_fragment) if 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 = @connection.scope("SELECT * FROM (#{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 "      SELECT \#{sql_fragment} AS group, COUNT(*) AS count\n      FROM (\#{sql}) sq\n      WHERE \#{sql_fragment} = ANY(\#{var_name})\n      GROUP BY \#{sql_fragment}\n    SQL\n\n    counts.update Hash[sparse_counts]\n  end\n\n  counts\nend\n", *args, sparse_groups

#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
24
25
26
27
28
29
30
31
32
33
34
35
36
 
# 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)

  cost = @connection.estimate_cost "SELECT MIN(#{sql_fragment}) FROM (#{sql}) sq", *args
  raise "enumerate_groups(#{sql_fragment.inspect}) takes too much time. Make sure to create a suitable index" if cost > 10_000

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

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

  groups
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?



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.send(:where!, sql_fragment, arg, placeholder: placeholder, jsonb: jsonb)
end