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 = 
"0.2.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
 
# File 'lib/sql-builder/builder.rb', line 24

def initialize(sql = "")
  @sql = sql
  @conditions = []
  @orders = []
  @groups = []
  @havings = []
  @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

#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"



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

def group(*args)
  case args.first
  when Array
    @groups += args.first.collect(&:to_s)
  else
    @groups += 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"



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

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

#limit(limit) ⇒ Object

Limit

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



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

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

#offset(offset) ⇒ Object

Offset See #limit



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

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

#order(condition) ⇒ Object

Order By

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



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

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"



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

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



130
131
132
133
134
 
# File 'lib/sql-builder/builder.rb', line 130

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

#to_sql ⇒ Object

Generate SQL



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/sql-builder/builder.rb', line 137

def to_sql
  sql_parts = [sql]
  if conditions.any?
    sql_parts << "WHERE " + conditions.join(" AND ")
  end
  if orders.any?
    sql_parts << "ORDER BY " + orders.join(", ")
  end
  if groups.any?
    sql_parts << "GROUP BY " + groups.join(", ")
  end
  if havings.any?
    sql_parts << "HAVING " + havings.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)



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

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

  self
end