How can I make this sequel expression terser

Question

I have the following expression:

  result = where(
    Sequel.like(Sequel.function(:lower, Sequel.join([:first_name, :last_name], ' ')), "%#{query.name.downcase}%") |
      Sequel.like(Sequel.function(:lower, :email), "%#{query.name.downcase}%")
  )

Is there a way I can tell sequel to run the same like expression against multiple columns?

Yes, it can be written in Sequel. *HOW* is rusty in my head, though I've done something similar in the past. I'd ask this on Sequel-talk. Jeremy Evans hangs out there and will probably have some way-cool short-cut way of doing it. He roams through here periodically but you'll be going directly to him on the official list. — the Tin Man, Jul 30 '13 at 14:00
OK, *HOW* wasn't so rusty; See my answer. One of Sequel's huge strengths is its ability to create a query iteratively. AREL helps Active Record a lot, but for any non-Rails work I do, which is a lot of coding, I use Sequel. I'm teaching one of our DBAs how to create schemas using [Sequel migrations](http://sequel.rubyforge.org/rdoc/files/doc/migration_rdoc.html) so that database can be created on any of our DB hosts, regardless of the flavor of the DBM, as long as Sequel [supports it](http://sequel.rubyforge.org/rdoc/files/doc/opening_databases_rdoc.html). That's a huge win for DBAs too. — the Tin Man, Jul 30 '13 at 15:57

CBusBus · Answer 1 · 2013-07-30T14:22:14.777

0

You could use something along the lines of

columns.map{|c| "c LIKE '%<someVar>%'}.join("AND ")

this'llcreate a LIKE, AND list of all the columns that you require. For example

columns = ["age", "gender", "hair_colour"]
columns.map{|c| "c LIKE '%<someVar>%'}.join("AND ")

#=> age LIKE '%<someVar%' AND gender LIKE '%<someVar>%' AND hair_colour LIKE '%<someVar>%'

How you populate is dependent on how the rest of your code is structured but that's a suitable frame for it.

An alternative would be to use a similar method of array mapping but using where() within the map. The lazy calling will allow you to iterate over any criteria before running the query. This resolves the potential grievance mentioned by @the Tin Man

edited Jul 30 '13 at 14:22

answered Jul 30 '13 at 12:51

CBusBus

2,321
1
18
26

This generates a SQL partial, but that goes against the whole idea of an ORM like Sequel, which is supposed to remove us from needing to write code that ties us to a DBM. – the Tin Man Jul 30 '13 at 14:02
It's a neatand universally compatible (or at least LIKE exists in most, if not all Sql servers). ORM is meant to make your life easier and your code neater but there's always a time and place where taking some of the work on yourself is a suitable solution. Besides, take ActiveRecords :conditions from Ruby, it uses the same approach with the exception that you'd use [" ?,..", insertValueOne, insertValueTwo,...] – CBusBus Jul 30 '13 at 14:14
The only time we should take on the task of manually generating the query, or a portion of it, is when the ORM fails to do the right thing or it doesn't support that DBM. In Sequel's case, which is what the OP asked about, there are a lot of [supported DBMs](http://sequel.rubyforge.org/rdoc/files/doc/opening_databases_rdoc.html), and the language supports query-chaining, so you can start with a simple query, and iteratively build upon it until it's what is needed. While Active Record is popular because of Rails, it's hardly the only ORM in town, and, IMO, it's not Sequel's equal, but YMMV. – the Tin Man Jul 30 '13 at 15:51

the Tin Man · Answer 2 · 2013-07-30T16:18:56.010

Here's what I'd do:

First, data like email addresses and username fields for lookups should be normalized to lowercase in the database. This simplifies your coding greatly.

If you need to maintain the original case, such as for a user's first and last name for display, keep them as separate first and last name fields, and create a separate field where they're already joined and hashed for searching, and use the same hash in your code when you look in that field. If that's indexed it'd be a very fast lookup compared to using a like search.

Here's code to programmatically generate a where using pure Sequel, rather than generate a SQL partial and inject it into your query. Notice that Sequel is aware of SQLite's syntax, and automatically creates a where clause that works. The same would apply if you're connecting to a PostgreSQL, MySQL, Oracle, MSSQL, Sybase or other DBM that Sequel supports; You won't have to tweak your code just to make it work, you'd only adjust your DSN telling Sequel which driver to use and what the DB credentials are.

Building a test DB:

require 'sequel'

DB = Sequel.sqlite
DB.create_table :items do
  primary_key :id
  String :first_name
  String :last_name
  String :email
  String :attr1, :default => 'attr'
  String :attr2, :default => 'attr'
end

items = DB[:items]
%w[Jane Jim John Junior].each do |fn|
  items.insert(:first_name => fn, :last_name => 'Doe', :email => fn.downcase + 'doe@email.com')
end
puts items.all

And here we go...:

user_name = '%' + 'John Doe'.downcase + '%'
query = DB[:items].where(
  Sequel.ilike(
    Sequel.join([:first_name, :last_name], ' '),
    user_name
  )
)

columns = [:email, :attr1, :attr2]
columns.each do |col|
  query = query.or(
    Sequel.ilike(
      col,
      user_name.delete(' ')
    )
  )
end

puts query.sql

puts query.first

Running the code outputs the table's contents:

{:id=>1, :first_name=>"Jane", :last_name=>"Doe", :email=>"janedoe@email.com", :attr1=>"attr", :attr2=>"attr"}
{:id=>2, :first_name=>"Jim", :last_name=>"Doe", :email=>"jimdoe@email.com", :attr1=>"attr", :attr2=>"attr"}
{:id=>3, :first_name=>"John", :last_name=>"Doe", :email=>"johndoe@email.com", :attr1=>"attr", :attr2=>"attr"}
{:id=>4, :first_name=>"Junior", :last_name=>"Doe", :email=>"juniordoe@email.com", :attr1=>"attr", :attr2=>"attr"}

The SQL generated to query the DB:

SELECT * FROM `items` WHERE ((UPPER((`first_name` || ' ' || `last_name`)) LIKE UPPER('%john doe%') ESCAPE '\') OR (UPPER(`email`) LIKE UPPER('%johndoe%') ESCAPE '\') OR (UPPER(`attr1`) LIKE UPPER('%johndoe%') ESCAPE '\') OR (UPPER(`attr2`) LIKE UPPER('%johndoe%') ESCAPE '\'))

Formatting that for readability:

SELECT * FROM `items`
WHERE (
  (UPPER((`first_name` || ' ' || `last_name`)) LIKE UPPER('%john doe%') ESCAPE '\') OR
  (UPPER(`email`) LIKE UPPER('%johndoe%') ESCAPE '\') OR
  (UPPER(`attr1`) LIKE UPPER('%johndoe%') ESCAPE '\') OR
  (UPPER(`attr2`) LIKE UPPER('%johndoe%') ESCAPE '\')
)

And the resulting row after the query:

{:id=>3, :first_name=>"John", :last_name=>"Doe", :email=>"johndoe@email.com", :attr1=>"attr", :attr2=>"attr"}

It's not the most DRY code, but for an example it gets the point across.

How can I make this sequel expression terser

2 Answers2