using loop to add items to array

rubyonrails-talk
1

Hi,

I'm trying to find all rows in database where 'title' is a substring of my specified string. That means if my string is "whatever", then I want all rows with title being "whateve", "whatev", "whate", "what". etc.

Here's the function:

def self.superiors(currentitem)

    @array =     s = currentitem.title     charcount = (s.index('-')-1)..(s.length-1)

    for i in charcount       this = s[0..i]

      found = General.find(:all, :conditions => ["title = ?", this])       @array << found     end

    return @array

end

if I return 'found' it works, but of course returns the last value found. but returning @array doesn't. it seems that I'm just doing something wrong with the arrays?

Thanks for any help.

2

u can try like this

found = General.find(:all, :conditions => [“title =?”,‘%#{this}%’]) or General.find(:all, :conditions => [“title=?”,“‘%’”+this+“‘%’”])

:slight_smile:

3

oops a small modification

General.find(:all, :conditions => [“title=?”,“'%”+this+“%'”])

4

bala's suggestion is correct if you'd do the opposite of your search. That is having "ever" and finding "whatever", "forever" and so on.

For your kind of search I dont see an easier way right now (but I am sure there is, in ruby there's always an easier way for these things..:slight_smile: Nevertheless you should try to merge the arrays with

"@array += found", that will add entries to the existing @array

"@array << found" will create an array of array's since General.find returns an array, I'm not sure if that's what you want....

good luck! smn

5

bala's suggestion is correct if you'd do the opposite of your search. That is having "ever" and finding "forever", "whatever" and so on.

For your search I dont see an easier way, though I think there is one, as always with ruby ..:slight_smile: You shoul try to fill your array like this though:

@array += found, this will add the entries of found to your existing @array. @array << found, will create an array of arrays, I am not sure if that's what you want....

good luck! smn

6

pepa007 wrote:

Hi,

I'm trying to find all rows in database where 'title' is a substring of my specified string. That means if my string is "whatever", then I want all rows with title being "whateve", "whatev", "whate", "what". etc.

Here's the function:

def self.superiors(currentitem)

    @array =     s = currentitem.title     charcount = (s.index('-')-1)..(s.length-1)

    for i in charcount       this = s[0..i]

      found = General.find(:all, :conditions => ["title = ?", this])       @array << found     end

    return @array

end

if I return 'found' it works, but of course returns the last value found. but returning @array doesn't. it seems that I'm just doing something wrong with the arrays?

Thanks for any help.

Hm, if I understand correctly, you are wanting to do the reversal of a standard database LIKE search. So, in normal conditions, you'd be doing something like

select * from some_table where some_field like '%some_string%'

I just did a quick test on PostgreSQL, and you can do something like

select * from some_table where 'some_string' like '%' || some_field || '%"

I have a table that lists sports (baseball, football, soccer, etc) and I did the following query

select * from available_sports where 'SoccerMom' ilike '%' || name || '%'

and it returned 'Soccer' as I expected it to.

Note that || is the string concatenation operator in Postgres. Depending on your database server, you might also consider using a case-insensitive operator, which is ilike in Postgres.

Doing it in the database, you will only have one query. Using the method you are trying now, you will have a query for every variation of the search string.

Peace, Phillip

7

wow, pretty close, thanks! I didn't know I can switch parameters in LIKE clause this way...

BUT! I'm using MySql not Postgre and || operator means OR in MySql so the result of your query is very different in MySql :slight_smile: Using CONCAT(title,'%') didn't work but don't know why. just returned no results.

HOWEVER, it helped me find the solution which is REGEXP function

so my query looks like SELECT * FROM 'generals' WHERE '^title' REGEXP 'whatever'

and the RoR function looks like

  def self.superiors(currentitem)     @found = General.find_by_sql("SELECT * FROM `generals` WHERE '^"+currentitem.title+"' REGEXP title")   end

it seems to do exactly what I want, but it's much shorter...