using loop to add items to array

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.

u can try like this

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

:slight_smile:

oops a small modification

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

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

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

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

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...