/
querying.cr
172 lines (145 loc) · 5.73 KB
/
querying.cr
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
module Granite::Querying
class NotFound < Exception
end
module ClassMethods
# Entrypoint for creating a new object from a result set.
def from_rs(result : DB::ResultSet) : self
model = new
model.new_record = false
model.from_rs result
model
end
def raw_all(clause = "", params = [] of Granite::Columns::Type)
rows = [] of self
adapter.select(select_container, clause, params) do |results|
results.each do
rows << from_rs(results)
end
end
rows
end
# All will return all rows in the database. The clause allows you to specify
# a WHERE, JOIN, GROUP BY, ORDER BY and any other SQL92 compatible query to
# your table. The result will be a Collection(Model) object which lazy loads
# an array of instantiated instances of your Model class.
# This allows you to take full advantage of the database
# that you are using so you are not restricted or dummied down to support a
# DSL.
# Lazy load prevent running unnecessary queries from unused variables.
def all(clause = "", params = [] of Granite::Columns::Type, use_primary_adapter = true)
switch_to_writer_adapter if use_primary_adapter == true
Collection(self).new(->{ raw_all(clause, params) })
end
# First adds a `LIMIT 1` clause to the query and returns the first result
def first(clause = "", params = [] of Granite::Columns::Type)
all([clause.strip, "LIMIT 1"].join(" "), params, false).first?
end
def first!(clause = "", params = [] of Granite::Columns::Type)
first(clause, params) || raise NotFound.new("No #{{{@type.name.stringify}}} found with first(#{clause})")
end
# find returns the row with the primary key specified. Otherwise nil.
def find(value)
first("WHERE #{primary_name} = ?", [value])
end
# find returns the row with the primary key specified. Otherwise raises an exception.
def find!(value)
find(value) || raise Granite::Querying::NotFound.new("No #{{{@type.name.stringify}}} found where #{primary_name} = #{value}")
end
# Returns the first row found that matches *criteria*. Otherwise `nil`.
def find_by(**criteria : Granite::Columns::Type)
find_by criteria.to_h
end
# :ditto:
def find_by(criteria : Granite::ModelArgs)
clause, params = build_find_by_clause(criteria)
first "WHERE #{clause}", params
end
# Returns the first row found that matches *criteria*. Otherwise raises a `NotFound` exception.
def find_by!(**criteria : Granite::Columns::Type)
find_by!(criteria.to_h)
end
# :ditto:
def find_by!(criteria : Granite::ModelArgs)
find_by(criteria) || raise NotFound.new("No #{{{@type.name.stringify}}} found where #{criteria.map { |k, v| %(#{k} #{v.nil? ? "is NULL" : "= #{v}"}) }.join(" and ")}")
end
def find_each(clause = "", params = [] of Granite::Columns::Type, batch_size limit = 100, offset = 0, &)
find_in_batches(clause, params, batch_size: limit, offset: offset) do |batch|
batch.each do |record|
yield record
end
end
end
def find_in_batches(clause = "", params = [] of Granite::Columns::Type, batch_size limit = 100, offset = 0, &)
if limit < 1
raise ArgumentError.new("batch_size must be >= 1")
end
loop do
results = all "#{clause} LIMIT ? OFFSET ?", params + [limit, offset], false
break if results.empty?
yield results
offset += limit
end
end
# Returns `true` if a records exists with a PK of *id*, otherwise `false`.
def exists?(id : Number | String | Nil) : Bool
return false if id.nil?
exec_exists "#{primary_name} = ?", [id]
end
# Returns `true` if a records exists that matches *criteria*, otherwise `false`.
def exists?(**criteria : Granite::Columns::Type) : Bool
exists? criteria.to_h
end
# :ditto:
def exists?(criteria : Granite::ModelArgs) : Bool
exec_exists *build_find_by_clause(criteria)
end
# count returns a count of all the records
def count : Int32
scalar "SELECT COUNT(*) FROM #{quoted_table_name}", &.to_s.to_i
end
def exec(clause = "")
switch_to_writer_adapter
adapter.open(&.exec(clause))
end
def query(clause = "", params = [] of Granite::Columns::Type, &)
switch_to_writer_adapter
adapter.open { |db| yield db.query(clause, args: params) }
end
def scalar(clause = "", &)
switch_to_writer_adapter
adapter.open { |db| yield db.scalar(clause) }
end
private def exec_exists(clause : String, params : Array(Granite::Columns::Type)) : Bool
self.adapter.exists? quoted_table_name, clause, params
end
private def build_find_by_clause(criteria : Granite::ModelArgs)
keys = criteria.keys
criteria_hash = criteria.dup
clauses = keys.map do |name|
if criteria_hash.has_key?(name) && !criteria_hash[name].nil?
matcher = "= ?"
else
matcher = "IS NULL"
criteria_hash.delete name
end
"#{quoted_table_name}.#{quote(name.to_s)} #{matcher}"
end
{clauses.join(" AND "), criteria_hash.values}
end
end
# Returns the record with the attributes reloaded from the database.
#
# **Note:** this method is only defined when the `Spec` module is present.
#
# ```
# post = Post.create(name: "Granite Rocks!", body: "Check this out.")
# # record gets updated by another process
# post.reload # performs another find to fetch the record again
# ```
def reload
{% if !@top_level.has_constant? "Spec" %}
raise "#reload is a convenience method for testing only, please use #find in your application code"
{% end %}
self.class.find!(primary_key_value)
end
end