Combine one or more conditions with AND

  Sql.and(["foo=?", 9], ["bar IS NULL"], ["baz in (?,?)", 123, 456])
  => ["(foo=?) AND (bar IS NULL) AND (baz in (?,?))", 9, 123, 456]

nil arguments are ignored. Returns nil if there are no arguments, or they are all nil.

[Source]

    # File lib/sql_helper.rb, line 27
27:   def self.and(*conds)
28:     combine("AND", *conds)
29:   end

Create a sql condition to match the given value. nil or empty string explicitly test for IS NULL in the database.

  Sql.eq("foo", 123) # => ["foo=?", 123]
  Sql.eq("foo", nil) # => ["foo IS NULL"]
  Sql.eq("foo", "")  # => ["foo IS NULL"]

[Source]

     # File lib/sql_helper.rb, line 109
109:   def self.eq(col, value)
110:     eq?(col, value) || ["#{col} IS NULL"]
111:   end

Create a sql condition to match the given value, or return nil if the value itself is nil or empty string.

  Sql.eq?("foo", 123) # => ["foo=?", 123]
  Sql.eq?("foo", nil) # => nil
  Sql.eq?("foo", "")  # => nil

[Source]

     # File lib/sql_helper.rb, line 93
 93:   def self.eq?(col, value)
 94:     case value
 95:     when nil, ""
 96:       nil
 97:     else
 98:       ["#{col}=?", value]
 99:     end
100:   end

General wrapper interface.

• If value is an Array, turn it into an IN condition
• If value looks like an IP address or subnet, expand it as such.
• If value contains % or _, turn it into a LIKE condition
• If value is nil or "", return an IS NULL condition
• Otherwise, make an equality condition

Examples:

  Sql.find("foo", 123)    # => ["foo=?", 123]
  Sql.find("foo", "bar%") # => ["foo LIKE ?", "bar%"]
  Sql.find("foo", nil)    # => ["foo IS NULL"]
  Sql.find("foo", "")     # => ["foo IS NULL"]
  Sql.find("foo", [1, 2]) # => ["foo IN (?,?)", 1, 2]
  Sql.find("foo", "192.0.2.123")
  # => ["foo IN (?,?,?,?,?,?,?,?,?)",
        "192.0.2.123",    "192.0.2.123/32", "192.0.2.120/30",
        "192.0.2.120/29", "192.0.2.112/28", "192.0.2.96/27",
        "192.0.2.64/26",  "192.0.2.0/25",   "192.0.2.0/24"]

[Source]

     # File lib/sql_helper.rb, line 249
249:   def self.find(col, value)
250:     in?(col, value) || find_ip?(col, value) || like(col, value)
251:   end

Returns a sql condition like find(), except returns nil if the value is nil or empty string. This is useful for building queries where parts are optional:

  @customers = Customer.find(:all, :conditions => Sql.and(
          Sql.find?("name", params[:name]),
          Sql.find?("postcode", params[:postcode]),
          Sql.find?("ip", params[:ip])
  ))

[Source]

     # File lib/sql_helper.rb, line 263
263:   def self.find?(col, value)
264:     in?(col, value) || find_ip?(col, value) || like?(col, value)
265:   end

SQL equality condition for IP addresses or subnets. For example, searching for 192.0.2.123 will match

• 192.0.2.123
• 192.0.2.123/32
• 192.0.2.120/30
• 192.0.2.120/29
• 192.0.2.112/28
• 192.0.2.96/27
• 192.0.2.64/26
• 192.0.2.0/25
• 192.0.2.0/24

and searching for 192.168.0.96/27 will match all IPs from 192.168.0.96 to 192.168.0.127 inclusive.

This only works for prefixes between /24 and /30.

Returns nil if value doesn’t look like an IP address, so you can choose your own fallback strategy.

[Source]

     # File lib/sql_helper.rb, line 188
188:   def self.find_ip?(col, value)
189:     case value
190: 
191:     when /\A\d+\.\d+\.\d+\.\d+\z/  # IP address
192:       require 'ipaddr'
193:       sql = ["#{col} IN (?,?", value, "#{value}/32"]
194:       addr = IPAddr.new(value)
195:       (24..30).each do |pfx|
196:          sql.first << ",?"
197:          sql << "#{addr.mask(pfx).to_s}/#{pfx}"
198:       end
199:       sql.first << ")"
200:       return sql
201: 
202:     when /\A((\d+\.\d+\.\d+)\.\d+)\/(\d+)\z/  # IP network
203:       require 'ipaddr'
204:       addr, addr24, pfx = $1, $2, $3.to_i
205:       case pfx
206:       when 24
207:         return ["#{col} LIKE ?", addr24 + ".%"]
208: 
209:       when (25..31)
210:         sql = ["#{col} IN (?", value]
211:         a1 = IPAddr.new(value).to_i
212:         a2 = a1 + (1 << (32 - pfx))
213:         (a1...a2).each do |a|
214:           sql.first << ",?"
215:           sql << IPAddr.new(a, Socket::AF_INET).to_s
216:         end
217:         sql.first << ")"
218:         return sql
219:         
220:       when 32
221:         return ["#{col} IN (?,?)", value, addr]
222:       end
223: 
224:     end
225: 
226:     # If it's not an IP address we can expand, return nil
227:     nil
228:   end

Create a find condition to match any value in an array, raising an exception if a non-array or empty array is passed.

[Source]

    # File lib/sql_helper.rb, line 82
82:   def self.in(col, values)
83:     in?(col, values) || (raise ArgumentError)
84:   end

Create a find condition to match any value in an array. Depending on whether the array contains any nil values, the result may be:

   ["col IN (?,?,?)", ...]
   ["col IN (?,?,?) OR col IS NULL", ...]
   ["col IS NULL"]

Returns nil a non-array is passed or the array is empty.

[Source]

    # File lib/sql_helper.rb, line 55
55:   def self.in?(col, values)
56:     return nil if !values.kind_of?(Array) || values.empty?
57:     sql = ["#{col} IN ("]
58:     null = false
59:     values.each do |v|
60:       case v
61:       when nil, ""
62:         null = true
63:       else
64:         sql.first << "?,"
65:         sql << v
66:       end
67:     end
68:     sql.first[-1] = ?)
69:     if !null
70:       # do nothing
71:     elsif sql.size > 1
72:       sql.first << " OR #{col} IS NULL"
73:     else
74:       sql = ["#{col} IS NULL"]
75:     end
76:     sql
77:   end

Return a ‘LIKE’ condition if the value contains % or _, or otherwise returns an equality test

  Sql.like("foo", "b%")  #=> ["foo LIKE ?", "b%"]
  Sql.like("foo", "abc") #=> ["foo=?", abc]
  Sql.like("foo", nil)   #=> ["foo IS NULL"]

[Source]

     # File lib/sql_helper.rb, line 165
165:   def self.like(col, value)
166:     like?(col, value) || ["#{col} IS NULL"]
167:   end

Return a ‘LIKE’ condition if the value contains % or _, or a standard equality, or nil if the value is nil or empty string.

  Sql.like?("foo", "b%")  #=> ["foo LIKE ?", "b%"]
  Sql.like?("foo", "abc") #=> ["foo=?", "abc"]
  Sql.like?("foo", nil)   #=> nil

[Source]

     # File lib/sql_helper.rb, line 149
149:   def self.like?(col, value)
150:     case value
151:     when /[%_]/
152:       ["#{col} LIKE ?", value]
153:     else
154:       eq?(col, value)
155:     end
156:   end

Create a sql condition to check the value is different to the one given. nil or empty string explicitly test for IS NOT NULL in the database.

  Sql.ne("foo", 123) # => ["foo!=? OR foo IS NULL", 123]
  Sql.ne("foo", nil) # => ["foo IS NOT NULL"]
  Sql.ne("foo", "")  # => ["foo IS NOT NULL"]

[Source]

     # File lib/sql_helper.rb, line 138
138:   def self.ne(col, value)
139:     ne?(col, value) || ["#{col} IS NOT NULL"]
140:   end

Create a sql condition to check that the col is not equal to the given value, or return nil if the value itself is nil or empty string.

  Sql.ne?("foo", 123) # => ["foo!=? OR foo IS NULL", 123]
  Sql.ne?("foo", nil) # => nil
  Sql.ne?("foo", "")  # => nil

[Source]

     # File lib/sql_helper.rb, line 121
121:   def self.ne?(col, value)
122:     case value
123:     when nil, ""
124:       nil
125:     else
126:       ["#{col}!=? OR #{col} IS NULL", value]
127:     end
128:   end

Negate a condition

   Sql.not("foo=bar")  # => ["NOT (foo=bar)"]

[Source]

    # File lib/sql_helper.rb, line 41
41:   def self.not(cond)
42:     cond = Array(cond).dup
43:     cond[0] = "NOT (#{cond[0]})"
44:     cond
45:   end

Combine one or more conditions with OR

[Source]

    # File lib/sql_helper.rb, line 33
33:   def self.or(*conds)
34:     combine("OR", *conds)
35:   end