Method: Sequel::Dataset#grep

Defined in:

lib/sequel/dataset/query.rb

#grep(columns, patterns, opts = OPTS) ⇒ Object

Match any of the columns to any of the patterns. The terms can be strings (which use LIKE) or regular expressions if the database supports that. Note that the total number of pattern matches will be Array(columns).length * Array(terms).length, which could cause performance issues.

Options (all are boolean):

:all_columns

All columns must be matched to any of the given patterns.

:all_patterns

All patterns must match at least one of the columns.

:case_insensitive

Use a case insensitive pattern match (the default is case sensitive if the database supports it).

If both :all_columns and :all_patterns are true, all columns must match all patterns.

Examples:

dataset.grep(:a, '%test%')
# SELECT * FROM items WHERE (a LIKE '%test%' ESCAPE '\')

dataset.grep([:a, :b], %w'%test% foo')
# SELECT * FROM items WHERE ((a LIKE '%test%' ESCAPE '\') OR (a LIKE 'foo' ESCAPE '\')
#   OR (b LIKE '%test%' ESCAPE '\') OR (b LIKE 'foo' ESCAPE '\'))

dataset.grep([:a, :b], %w'%foo% %bar%', all_patterns: true)
# SELECT * FROM a WHERE (((a LIKE '%foo%' ESCAPE '\') OR (b LIKE '%foo%' ESCAPE '\'))
#   AND ((a LIKE '%bar%' ESCAPE '\') OR (b LIKE '%bar%' ESCAPE '\')))

dataset.grep([:a, :b], %w'%foo% %bar%', all_columns: true)
# SELECT * FROM a WHERE (((a LIKE '%foo%' ESCAPE '\') OR (a LIKE '%bar%' ESCAPE '\'))
#   AND ((b LIKE '%foo%' ESCAPE '\') OR (b LIKE '%bar%' ESCAPE '\')))

dataset.grep([:a, :b], %w'%foo% %bar%', all_patterns: true, all_columns: true)
# SELECT * FROM a WHERE ((a LIKE '%foo%' ESCAPE '\') AND (b LIKE '%foo%' ESCAPE '\')
#   AND (a LIKE '%bar%' ESCAPE '\') AND (b LIKE '%bar%' ESCAPE '\'))

# File 'lib/sequel/dataset/query.rb', line 343

def grep(columns, patterns, opts=OPTS)
  column_op = opts[:all_columns] ? :AND : :OR
  if opts[:all_patterns]
    conds = Array(patterns).map do |pat|
      SQL::BooleanExpression.new(column_op, *Array(columns).map{|c| SQL::StringExpression.like(c, pat, opts)})
    end
    where(SQL::BooleanExpression.new(:AND, *conds))
  else
    conds = Array(columns).map do |c|
      SQL::BooleanExpression.new(:OR, *Array(patterns).map{|pat| SQL::StringExpression.like(c, pat, opts)})
    end
    where(SQL::BooleanExpression.new(column_op, *conds))
  end
end