SQL-like syntax for array of objects

Question

I have an array of objects. I need to use an SQL-like condition WHERE field like '%value%' for some object fields in this array. How to do it?

Edited: For example I have array with Users and I need to find all users with first_name like ike and email like 123.

Edited2: I need method to get Users with first_name like smth and email like smth from ARRAY of my Users. Users have first_name and email.

Edited3: All my users are in database. But I have some business logic, at the end of this logic I have array with Users. Next I need to filter this array with some text: ike for first_name and 123 for email. How to do it?

Where do you get that array? Arrays do not have a query language, they are merely containers. Databases often have SQL and if that's the source of your data, then that is where you should apply the query. You could put your array into a database like SQLite, and query it, using the gem Sequel, which is a great ORM and makes it easy. — the Tin Man
– the Tin Man, Commented Jan 21, 2014 at 12:29
We need more info. Describe the attributes of the object. Obviously they must have first_name and email if you want filter by them, so why can't you use normal Ruby methods to ask them about themselves? — the Tin Man
– the Tin Man, Commented Jan 21, 2014 at 12:37
You have a database. "Next I need to filter this array with some text: ike for first_name and 123 for email." Do that in the database when you request the users, before you do anything else. That is what a SQL query does, it filters to retrieve a subset of an entire database, which is returned to your Ruby code as an array. It sounds like you don't understand how we use databases from a language like Ruby. — the Tin Man
– the Tin Man, Commented Jan 21, 2014 at 12:53
Have you looked at this question on the correct formatting of LIKE clauses in Rails queries? stackoverflow.com/questions/7051062/… — GSP
– GSP, Commented Jan 21, 2014 at 13:36

Abdo · Accepted Answer · 2014-01-21 12:54:32Z

1

arr = %w[hello quick bool boo foo]
arr.select { |x| x.include?("foo") }
=> ["bool", "boo", "foo"]

or in your case, if you have an array of objects, you can do:

x.first_name.include?("foo") && x.email.include?("123")

For more customization, you can use Array#select with Regexeps

answered Jan 21, 2014 at 12:54

Abdo

14.2k11 gold badges81 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bundacia · Accepted Answer · 2014-01-21 13:11:51Z

If you can just use ruby methods for this that do something like this:

User = Struct.new(:email, :first_name) # Just creating a cheap User class here

users = [
  User.new('[email protected]'  , 'ike'), 
  User.new('[email protected]', 'bob'), 
  User.new('[email protected]', 'ike'),
]

# results will be an array holding only the last element in users
results =  users.find_all do |user| 
  user.email      =~ /123/ and 
  user.first_name =~ /ike/
end

Writing your own sql parser seems like a pretty bad idea, but if you really need to parse simple SQL where clauses you could do something like this:

User = Struct.new(:email, :first_name) # Just creating a cheap User class here

users = [
    User.new('[email protected]'  , 'ike'), 
    User.new('[email protected]', 'bob'), 
    User.new('[email protected]', 'ike'),
]

def where(array, sql)
  sql = sql.gsub(/\s+AND\s+/, ' ') # remove AND's

  terms = Hash[ *sql.split(/\s+LIKE\s+| /) ] # turn "a LIKE 'b'" into {'a': "'b'"}

  array.find_all do |item|
    terms.all? do |attribute, matcher|
      matcher = matcher.gsub('%', '.*')         # convert %
      matcher = matcher.gsub(/^['"]|["']$/, '') # strip quotes 

      item.send(attribute) =~ /^#{matcher}$/
    end
  end
end

# results will be an array holding only the last element in users
results = where(users, "first_name LIKE '%ike%' AND email LIKE '%123%'")

This will only work for where clauses what only contain LIKE statements connected by AND's. Adding support for all valid SQL is left as an exercise for the reader, (or better yet, just left alone).

alpinweis · Accepted Answer · 2016-11-13 00:51:24Z

0

I've built a ruby gem uber_array to enable sql-like syntax for arrays of Hashes or Objects you might want to try.

require 'uber_array'

# Array of Hash elements with strings as keys
items = [
  { 'name' => 'Jack', 'score' => 999, 'active' => false },
  { 'name' => 'Jake', 'score' => 888, 'active' => true  },
  { 'name' => 'John', 'score' => 777, 'active' => true  }
]

uber_items = UberArray.new(items)

uber_items.where('name' => 'John')
uber_items.where('name' => /Ja/i)
uber_items.like('ja')
uber_items.where('name' => %w(Dave John Tom))
uber_items.where('score' => 999)
uber_items.where('score' => ->(s){s > 900})
uber_items.where('active' => true, 'score' => 800..900)

answered Nov 13, 2016 at 0:51

alpinweis

2693 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL-like syntax for array of objects

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related