Class: SQLBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/sql-builder/builder.rb,
lib/sql-builder.rb,
lib/sql-builder/version.rb

Overview

SQLBuilder write the complex SQL as DSL

Example:

query = SQLBuilder.new("SELECT * FROM users")
  .where("name = ?", "hello world")
  .where("status != ?", 1)
  .order("created_at desc")
  .order("id asc")
  .page(1).per(20)
  .to_sql

Constant Summary collapse

VERSION = 
"1.0.0"

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(sql = "") ⇒ SQLBuilder

Create a new SQLBuilder

Example

query = SQLBuilder.new("SELECT users.*, user_profiles.avatar FROM users INNER JOIN user_profiles ON users.id = user_profiles.id")
query.to_sql
# => "SELECT users.*, user_profiles.avatar FROM users INNER JOIN user_profiles ON users.id = user_profiles.id"



24
25
26
27
28
29
30
31
32
33
 
# File 'lib/sql-builder/builder.rb', line 24

def initialize(sql = "")
  @sql = sql
  @conditions = []
  @orders = []
  @groups = []
  @havings = []
  @ors = []
  @limit_options = {}
  @page_options = {per_page: 20}
end

Instance Attribute Details

#conditions ⇒ Object (readonly)

Returns the value of attribute conditions.



14
15
16
 
# File 'lib/sql-builder/builder.rb', line 14

def conditions
  @conditions
end

#groups ⇒ Object (readonly)

Returns the value of attribute groups.



14
15
16
 
# File 'lib/sql-builder/builder.rb', line 14

def groups
  @groups
end

#havings ⇒ Object (readonly)

Returns the value of attribute havings.



14
15
16
 
# File 'lib/sql-builder/builder.rb', line 14

def havings
  @havings
end

#limit_options ⇒ Object (readonly)

Returns the value of attribute limit_options.



14
15
16
 
# File 'lib/sql-builder/builder.rb', line 14

def limit_options
  @limit_options
end

#orders ⇒ Object (readonly)

Returns the value of attribute orders.



14
15
16
 
# File 'lib/sql-builder/builder.rb', line 14

def orders
  @orders
end

#ors ⇒ Object

Returns the value of attribute ors.



15
16
17
 
# File 'lib/sql-builder/builder.rb', line 15

def ors
  @ors
end

#page_options ⇒ Object (readonly)

Returns the value of attribute page_options.



14
15
16
 
# File 'lib/sql-builder/builder.rb', line 14

def page_options
  @page_options
end

#sql ⇒ Object (readonly)

Returns the value of attribute sql.



14
15
16
 
# File 'lib/sql-builder/builder.rb', line 14

def sql
  @sql
end

Instance Method Details

#group(*args) ⇒ Object

Group By

Allows to specify a group attribute:

query.group("name as new_name, age").to_sql
# => "GROUP BY name as new_name, age"

or

query.group("name", "age").to_sql # => "GROUP BY name, age"
query.group(:name, :age).to_sql # => "GROUP BY name, age"
query.group(["name", "age"]).to_sql # => "GROUP BY name, age"
query.group("name").group("age").to_sql # => "GROUP BY name, age"



94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/sql-builder/builder.rb', line 94

def group(*args)
  @groups += case args.first
  when Array
    args.first.collect(&:to_s)
  else
    args.collect(&:to_s)
  end

  @groups.uniq!
  self
end

#having(*condition) ⇒ Object

Having

query.group("name").having("count(name) > ?", 5).to_sql
# => "GROUP BY name HAVING count(name) > 5"



111
112
113
114
 
# File 'lib/sql-builder/builder.rb', line 111

def having(*condition)
  havings << sanitize_sql_for_assignment(condition)
  self
end

#limit(limit) ⇒ Object

Limit

query.offset(3).limit(10).to_sql
# => "LIMIT 10 OFFSET 3"



74
75
76
77
78
 
# File 'lib/sql-builder/builder.rb', line 74

def limit(limit)
  limit_options[:offset] ||= 0
  limit_options[:limit] = limit.to_i
  self
end

#offset(offset) ⇒ Object

Offset See #limit



65
66
67
68
 
# File 'lib/sql-builder/builder.rb', line 65

def offset(offset)
  limit_options[:offset] = offset.to_i
  self
end

#or(other) ⇒ Object

Or

query.or(query.where(num: 1)).to_sql # => "OR num = 1"



120
121
122
123
124
125
126
127
128
 
# File 'lib/sql-builder/builder.rb', line 120

def or(other)
  if other.is_a?(SQLBuilder)
    ors << other.ors if other.ors.any?
    ors << other.conditions if other.conditions.any?
  else
    raise ArgumentError, "You have passed #{other.class.name} object to #or. Pass an SQLBuilder object instead."
  end
  self
end

#order(condition) ⇒ Object

Order By

query.order("name asc").order("created_at desc").to_sql
# => "ORDER BY name asc, created_at desc"



58
59
60
61
 
# File 'lib/sql-builder/builder.rb', line 58

def order(condition)
  orders << sanitize_sql_for_order(condition)
  self
end

#page(page_no) ⇒ Object

Pagination

query.page(1).per(12).to_sql # => "LIMIT 12 OFFSET 0"
query.page(2).per(12).to_sql # => "LIMIT 12 OFFSET 12"



134
135
136
137
138
139
140
141
 
# File 'lib/sql-builder/builder.rb', line 134

def page(page_no)
  page_options[:page] = page_no
  page_options[:per_page] ||= 10

  limit_options[:offset] = page_options[:per_page].to_i * (page_options[:page].to_i - 1)
  limit_options[:limit] = page_options[:per_page].to_i
  self
end

#per(per_page) ⇒ Object

Set per_page limit See #page



145
146
147
148
149
 
# File 'lib/sql-builder/builder.rb', line 145

def per(per_page)
  page_options[:per_page] = per_page
  page(page_options[:page])
  self
end

#to_sql ⇒ Object

Generate SQL



152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
 
# File 'lib/sql-builder/builder.rb', line 152

def to_sql
  sql_parts = [sql]
  if conditions.any?
    sql_parts << "WHERE " + conditions.flatten.join(" AND ")
  end
  if ors.any?
    sql_parts = extract_sql_parts(sql_parts, ors)
  end
  if orders.any?
    sql_parts << "ORDER BY " + orders.flatten.join(", ")
  end
  if groups.any?
    sql_parts << "GROUP BY " + groups.flatten.join(", ")
  end
  if havings.any?
    sql_parts << "HAVING " + havings.flatten.join(" AND ")
  end
  if limit_options[:limit]
    sql_parts << "LIMIT " + limit_options[:limit].to_s
  end
  if limit_options[:limit] && limit_options[:offset]
    sql_parts << "OFFSET " + limit_options[:offset].to_s
  end
  sql_parts.join(" ")
end

#where(*condition) ⇒ Object

Add ‘AND` condition

query.where("name = ?", params[:name]).where("age >= ?", 18)

or

count_query.where(query)



42
43
44
45
46
47
48
49
50
51
52
 
# File 'lib/sql-builder/builder.rb', line 42

def where(*condition)
  case condition.first
  when SQLBuilder
    query_scope = condition.first
    @conditions = query_scope.conditions
  else
    conditions << sanitize_sql_for_assignment(condition)
  end

  self
end