Class: PGI::Dataset::Query

Inherits:
Object
  • Object
show all
Defined in:
lib/pgi/dataset/query.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(database, table, command, **options) ⇒ Query

Create instance of Query

Parameters:

  • database (PGI::DB) —

    a configured instance of DB

  • table (Symbol) —

    the name of the database table to operate on

  • command (String) —

    the command part of the query (default: SELECT * FROM <table>)

  • options (Hash) —

    hash of options: scope, where, params, limit, order, returning



14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/pgi/dataset/query.rb', line 14

def initialize(database, table, command, **options)
  @database  = database
  @table     = table
  @command   = command || "SELECT * FROM #{@table}"
  @scope     = options.fetch(:scope, nil)
  @where     = options.fetch(:where, nil)
  @params    = options.fetch(:params, [])
  @order     = options.fetch(:order, {})
  @limit     = options.fetch(:limit, 10)
  @returning = options.fetch(:returning, nil)
end

Instance Attribute Details

#params ⇒ Array (readonly)

Get the params for placeholder substitution

Returns:

  • (Array) —

    params



137
138
139
 
# File 'lib/pgi/dataset/query.rb', line 137

def params
  @params
end

Instance Method Details

#count ⇒ Integer

Get the number of records in a result set

Returns:

  • (Integer) 


189
190
191
192
193
 
# File 'lib/pgi/dataset/query.rb', line 189

def count
  @command = "SELECT COUNT(*) FROM #{@table}"
  @order   = {}
  first&.fetch("count", 0)
end

#each ⇒ Object

Loop through records in a result set



159
160
161
162
163
 
# File 'lib/pgi/dataset/query.rb', line 159

def each(&)
  @database
    .exec_stmt(Utils.stmt_name(@table, sql), sql, params)
    .each(&)
end

#explain ⇒ String

Explain some query

Returns:

  • (String) —

    Formatted string explaining query plan



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'lib/pgi/dataset/query.rb', line 168

def explain
  explain_sql = "EXPLAIN " << sql.dup.tap do |s|
    params.each_with_index do |x, i|
      x =
        case x
        when String
          "'#{x}'"
        else
          x
        end

      s.gsub!("$#{i + 1}", x.to_s)
    end
  end

  @database.exec(explain_sql)&.values&.join("\n")
end

#first ⇒ Hash

Get the first record in a result set

Returns:

  • (Hash) 


142
143
144
145
146
147
 
# File 'lib/pgi/dataset/query.rb', line 142

def first
  limit(1)
  @database
    .exec_stmt(Utils.stmt_name(@table, sql), sql, params)
    .first
end

#keyset(sort_by, cursor_id, sort_dir) ⇒ Query

Apply keyset pagination: orders by (sort_by, id) and, when a cursor id is given, seeks past that row. A nil cursor_id is the first page. The cursor predicate combines with any existing WHERE clause, so call it after #where. For sort_by == :id: WHERE id > $cursor_id For sort_by != :id: WHERE (sort_col, id) > (SELECT sort_col, id FROM table WHERE id = $cursor_id) Do not combine with a conflicting #order call — pages are only correct when the leading sort columns match the cursor predicate.

Parameters:

  • sort_by (Symbol) —

    the sort column

  • cursor_id (*, nil) —

    id of the last row from the previous page, or nil for the first page

  • sort_dir (Symbol) —

    :asc or :desc

Returns:

  • (Query) —

    return the Query instance (for method chaining)



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
 
# File 'lib/pgi/dataset/query.rb', line 97

def keyset(sort_by, cursor_id, sort_dir)
  order(sort_by, sort_dir)
  order(:id, sort_dir) unless sort_by.to_sym == :id
  return self unless cursor_id

  op     = sort_dir == :asc ? ">" : "<"
  id_col = Utils.sanitize_columns(:id, @table).first
  @params << cursor_id

  clause =
    if sort_by.to_sym == :id
      "#{id_col} #{op} $#{@params.size}"
    else
      sort_col = Utils.sanitize_columns(sort_by, @table).first
      "(#{sort_col}, #{id_col}) #{op} (SELECT #{sort_col}, #{id_col} FROM #{@table} WHERE #{id_col} = $#{@params.size})"
    end

  @where = @where ? "#{clause} AND (#{@where})" : clause
  self
end

#limit(number) ⇒ Query

Adds a LIMIT clause to the query

Parameters:

  • direction (Integer) —

    the direction the sort should take - can be either :desc or :asc

Returns:

  • (Query) —

    return the Query instance (for method chaining)

Raises:

  • (RuntimeError) —

    if the direction param is invalid



78
79
80
81
82
83
 
# File 'lib/pgi/dataset/query.rb', line 78

def limit(number)
  raise "LIMIT must be an integer or nil" unless number.nil? || number.is_a?(Integer)

  @limit = number
  self
end

#order(column, direction = :asc) ⇒ Query

Adds a ORDER BY clause to the query - suports multiple calls to the method

Parameters:

  • column (Symbol) —

    the columns

  • direction (Symbol) (defaults to: :asc) —

    the direction the sort should take - can be either :desc or :asc

Returns:

  • (Query) —

    return the Query instance (for method chaining)

Raises:

  • (RuntimeError) —

    if the direction param is invalid



66
67
68
69
70
71
 
# File 'lib/pgi/dataset/query.rb', line 66

def order(column, direction = :asc)
  raise "Invalid ORDER BY direction: #{direction.inspect}" unless %i[asc desc].include?(direction)

  @order[Utils.sanitize_columns(column, @table)] = direction.to_s.upcase
  self
end

#sql ⇒ String

Get the Query SQL string prepared for execution

Returns:

  • (String) —

    Query as a SQL string



121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/pgi/dataset/query.rb', line 121

def sql
  # Simple Scope implementation
  scope = @scope.dup
  scope << " AND " if scope && @where

  command = @command.dup
  command << " WHERE #{scope}#{@where}" if @where || scope
  command << " ORDER BY #{Array(@order).map { |x| x.join(" ") }.join(", ")}" unless @order.empty?
  command << " LIMIT #{@limit}" if @limit
  command << " RETURNING *" if @command =~ /^UPDATE|INSERT|DELETE/
  command
end

#to_a ⇒ Array

Get all the records in a result set

Returns:

  • (Array) —

    Array of records as Hashes



152
153
154
155
156
 
# File 'lib/pgi/dataset/query.rb', line 152

def to_a
  @database
    .exec_stmt(Utils.stmt_name(@table, sql), sql, params)
    .to_a
end

#to_s ⇒ String Also known as: inspect

Get a string representation of the instance

Returns:

  • (String) 


198
199
200
 
# File 'lib/pgi/dataset/query.rb', line 198

def to_s
  "#<PGI::Dataset::Query:#{object_id} @sql=#{sql} @params=#{params}>"
end

#where(clause = nil, params = []) ⇒ Query

Adds a WHERE clause to the query - can only be called once per query, so combine all conditions in a single call

Parameters:

  • clause (Hash, String) (defaults to: nil) —

    a Hash of table columns and values - or a string with placeholders

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

    list of values for placeholder substitution

Returns:

  • (Query) —

    return the Query instance (for method chaining)

Raises:

  • (RuntimeError) —

    if a WHERE clause is already set



33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/pgi/dataset/query.rb', line 33

def where(clause = nil, params = [])
  return self unless clause
  return self if clause.empty?

  raise "WHERE clause already set - combine conditions in a single call" if @where

  case clause
  when Hash
    clause = clause.map do |k, v|
      @params << v
      "#{Utils.sanitize_columns(k, @table).first} = $#{@params.size}"
    end.join(" AND ")
  when String
    raise "Use placeholders in WHERE clause" if clause =~ /=(?!\s*[?$])/

    offset = @params.size
    @params += params
    clause = clause.gsub(/([=<>]{1}\s{0,})(\?)/).with_index { |_, i| "#{Regexp.last_match(1)}$#{offset + i + 1}" }
  else
    raise "WHERE clause can either be a Hash or a String"
  end

  @where = clause

  self
end