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"

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

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)

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

api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html#method-i-sanitize_sql_for_assignment

# 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

api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html#method-i-sanitize_sql_hash_for_assignment

# 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