class OrderQuery::SQL::Where

Builds where clause for searching around a record in an order space.

Constants

NULLS_ORD
RAY_OP
WHERE_IDENTITY

Attributes

point[R]

Public Class Methods

new(point) click to toggle source

@param [OrderQuery::Point] point

# File lib/order_query/sql/where.rb, line 10
def initialize(point)
  @point   = point
  @columns = point.space.columns
end

Public Instance Methods

build(side, strict = true) click to toggle source

Join column pairs with OR, and nest within each other with AND @param [:before or :after] side @return [query, parameters] WHERE columns matching records strictly

before / after this one.

sales < 5 OR
sales = 5 AND (
  invoice < 3 OR
  invoices = 3 AND (
    ... ))
# File lib/order_query/sql/where.rb, line 25
def build(side, strict = true)
  # generate pairs of terms such as sales < 5, sales = 5
  terms = @columns.map.with_index do |col, i|
    be_strict = i != @columns.size - 1 ? true : strict
    [where_side(col, side, be_strict), where_tie(col)].reject do |x|
      x == WHERE_IDENTITY
    end
  end
  # group pairwise with OR, and nest with AND
  query = foldr_terms terms.map { |pair| join_terms 'OR', *pair }, 'AND'
  if ::OrderQuery.wrap_top_level_or
    # wrap in a redundant AND clause for performance
    query = wrap_top_level_or query, terms, side
  end
  query
end

Protected Instance Methods

foldr_terms(terms, sql_operator) click to toggle source

@param [String] sql_operator SQL operator @return [query, params] terms right-folded with sql_operator

[A, B, C, ...] -> A AND (B AND (C AND ...))
# File lib/order_query/sql/where.rb, line 47
def foldr_terms(terms, sql_operator)
  foldr_i WHERE_IDENTITY, terms do |a, b, i|
    join_terms sql_operator, a, (i > 1 ? wrap_term_with_parens(b) : b)
  end
end
join_terms(op, *terms) click to toggle source

joins terms with an operator, empty terms are skipped @return [query, parameters]

# File lib/order_query/sql/where.rb, line 55
def join_terms(op, *terms)
  [terms.map(&:first).reject(&:empty?).join(" #{op} "),
   terms.map(&:second).reduce([], :+)]
end
where_eq(col, value = point.value(col)) click to toggle source
# File lib/order_query/sql/where.rb, line 120
def where_eq(col, value = point.value(col))
  if value.nil?
    ["#{col.column_name} IS NULL", []]
  else
    ["#{col.column_name} = ?", [value]]
  end
end
where_in(col, values) click to toggle source
# File lib/order_query/sql/where.rb, line 107
def where_in(col, values)
  join_terms 'OR',
             (values.include?(nil) ? where_eq(col, nil) : WHERE_IDENTITY),
             case (non_nil_values = values - [nil]).length
             when 0
               WHERE_IDENTITY
             when 1
               where_eq col, non_nil_values
             else
               ["#{col.column_name} IN (?)", [non_nil_values]]
             end
end
where_null(col, mode, strict) click to toggle source
# File lib/order_query/sql/where.rb, line 131
def where_null(col, mode, strict)
  if strict && col.nulls_direction(mode == :before) != :last
    ["#{col.column_name} IS NOT NULL", []]
  else
    WHERE_IDENTITY
  end
end
where_ray(col, from, mode, strict) click to toggle source
# File lib/order_query/sql/where.rb, line 139
def where_ray(col, from, mode, strict)
  ["#{col.column_name} "\
   "#{RAY_OP[col.direction(mode == :before)]}#{'=' unless strict} ?",
   [from]].tap do |ray|
    if col.nullable? && col.nulls_direction(mode == :before) == :last
      ray[0] = "(#{ray[0]} OR #{col.column_name} IS NULL)"
    end
  end
end
where_side(col, side, strict, value = point.value(col)) click to toggle source

@param [:before or :after] side @return [query, params] return query fragment for column values

before / after the current one.
# File lib/order_query/sql/where.rb, line 97
def where_side(col, side, strict, value = point.value(col))
  if col.order_enum
    where_in col, col.enum_side(value, side, strict)
  elsif value.nil?
    where_null col, side, strict
  else
    where_ray col, value, side, strict
  end
end
where_tie(col) click to toggle source

@return [query, params] tie-breaker unless column is unique

# File lib/order_query/sql/where.rb, line 86
def where_tie(col)
  if col.unique?
    WHERE_IDENTITY
  else
    where_eq(col)
  end
end
wrap_term_with_parens(t) click to toggle source
# File lib/order_query/sql/where.rb, line 60
def wrap_term_with_parens(t)
  ["(#{t[0]})", t[1]]
end
wrap_top_level_or(query, terms, side) click to toggle source

Wrap top level OR clause to help DB with using the index Before:

(sales < 5 OR
  (sales = 5 AND ...))

After:

(sales <= 5 AND
 (sales < 5 OR
    (sales = 5 AND ...)))

Read more at github.com/glebm/order_query/issues/3

# File lib/order_query/sql/where.rb, line 73
def wrap_top_level_or(query, terms, side)
  top_term_i = terms.index(&:present?)
  if top_term_i && terms[top_term_i].length == 2 &&
     !(col = @columns[top_term_i]).order_enum
    join_terms 'AND',
               where_side(col, side, false),
               wrap_term_with_parens(query)
  else
    query
  end
end

Private Instance Methods

foldr_i(z, xs) { |a, b, i| ... } click to toggle source

Inject with index from right to left, turning [a, b, c] into a + (b + c) Passes an index to the block, counting from the right Read more about folds:

# File lib/order_query/sql/where.rb, line 158
def foldr_i(z, xs)
  xs.reverse_each.each_with_index.inject(z) { |b, (a, i)| yield a, b, i }
end