class SQLBuilder
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
Constants
- VERSION
Attributes
conditions[R]
groups[R]
havings[R]
limit_options[R]
orders[R]
ors[RW]
page_options[R]
sql[R]
Public Class Methods
new(sql = "")
click to toggle source
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"
# 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
Public Instance Methods
group(*args)
click to toggle source
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"
# 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)
click to toggle source
Having
query.group("name").having("count(name) > ?", 5).to_sql # => "GROUP BY name HAVING count(name) > 5"
# File lib/sql-builder/builder.rb, line 111 def having(*condition) havings << sanitize_sql_for_assignment(condition) self end
limit(limit)
click to toggle source
Limit
query.offset(3).limit(10).to_sql # => "LIMIT 10 OFFSET 3"
# 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)
click to toggle source
Offset See limit
# File lib/sql-builder/builder.rb, line 65 def offset(offset) limit_options[:offset] = offset.to_i self end
or(other)
click to toggle source
Or
query.or(query.where(num: 1)).to_sql # => "OR num = 1"
# 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)
click to toggle source
Order By
query.order("name asc").order("created_at desc").to_sql # => "ORDER BY name asc, created_at desc"
# File lib/sql-builder/builder.rb, line 58 def order(condition) orders << sanitize_sql_for_order(condition) self end
page(page_no)
click to toggle source
Pagination
query.page(1).per(12).to_sql # => "LIMIT 12 OFFSET 0" query.page(2).per(12).to_sql # => "LIMIT 12 OFFSET 12"
# 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)
click to toggle source
Set per_page limit See page
# 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()
click to toggle source
Generate SQL
# 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)
click to toggle source
Add `AND` condition
query.where("name = ?", params[:name]).where("age >= ?", 18)
or
count_query.where(query)
# 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
Private Instance Methods
extract_sql_parts(sql_parts, ors)
click to toggle source
# File lib/sql-builder/builder.rb, line 205 def extract_sql_parts(sql_parts, ors) if ors.is_a?(Array) ors.each do |single_or| next unless single_or.is_a?(Array) if begin single_or[0][0].is_a?(Array) rescue false end extract_sql_parts(sql_parts, single_or) else sql_parts << "OR " + single_or.flatten.join(" AND ") end end end sql_parts end
sanitize_sql_array(ary)
click to toggle source
# File lib/sql-builder/builder.rb, line 196 def sanitize_sql_array(ary) ActiveRecord::Base.send(:sanitize_sql_array, ary) end
sanitize_sql_for_assignment(assignments)
click to toggle source
# File lib/sql-builder/builder.rb, line 181 def sanitize_sql_for_assignment(assignments) case assignments.first when Hash then sanitize_sql_hash_for_assignment(assignments.first) else sanitize_sql_array(assignments) end end
sanitize_sql_for_order(ary)
click to toggle source
# File lib/sql-builder/builder.rb, line 200 def sanitize_sql_for_order(ary) return ary if ActiveRecord.version < Gem::Version.new("5.0.0") ActiveRecord::Base.send(:sanitize_sql_for_order, ary) end
sanitize_sql_hash_for_assignment(attrs)
click to toggle source
# File lib/sql-builder/builder.rb, line 190 def sanitize_sql_hash_for_assignment(attrs) attrs.map do |attr, value| sanitize_sql_array(["#{attr} = ?", value]) end end