Find_by_like?

Rails
1

Hi all,

Last night a friend and I decided to try and implement a search feature
for a website (one we are using to learn Rails). We have a membership
database and thought it would be good to search for members based on
their surname (starting easy and working up) - this was pretty easy:

Member.find_all_by_surname(params[:query])

OK - What if you don’t know the exact surname, or what a list of every
member with “cla” in their surname?

Our solution late last night was:

Member.find(:all, :conditions => [“surname LIKE ?”, “%” +
params[:query] + “%”])

It works but it does not seem very “rails like”, so this morning I went
looking for a :like or find_by_surname_like - it seems none exist.

So my question is this - how do other people implement these kind of
searches?

Is these a case to be made for adding “like” support to rails?

If so - what should it look like? Here are some ideas:

Member.find_by_surname_like(“cla”)
Member.find_all_by_surname_like(“cla”)
Member.find_surname_like(“cla”)
Member.find_like_surname(“cla”)
Member.find(:like => {“Surname“ => “cla”})

Personally I prefer the first one. Josh S. has a good article on
how dynamic finders work
(http://blog.hasmanythrough.com/2006/08/13/how-dynamic-finders-work), so
this would make an interesting project for me to get my hands dirty in
Rails code. I also notice a proposed fulltext search on the wiki
(http://wiki.rubyonrails.org/rails/pages/FullTextSearch).

But before I have a go at this - how do you think something like this
should work?

2

Our solution late last night was:

Member.find(:all, :conditions => [“surname LIKE ?”, “%” +
params[:query] + “%”])

It works but it does not seem very “rails like”, so this morning I went
looking for a :like or find_by_surname_like - it seems none exist.

So my question is this - how do other people implement these kind of
searches?

Like you’ve done above, but I tend to usee:

[“surname LIKE ?”, “%{params[:query]}%”]

Is these a case to be made for adding “like” support to rails?

Not for me, but then again, I’m not everybody :slight_smile: One potential problem
is
once you add like someone is going to want regex (ie. the ~ operator in
postgres for example) and they’ll probably also want “NOT LIKE” and then
there’s the whole issue of case [in]sensitivity between databases…

I’m just saying that there’s a lot of options and they tend to get
somewhat database specific so maybe it’s a hard thing to generalize.

Also, if you did to “like”, I’d also like “begins_with” that left off
the
initial ‘%’… and so on :slight_smile:

If so - what should it look like? Here are some ideas:

Member.find_by_surname_like(“cla”)
Member.find_all_by_surname_like(“cla”)
Member.find_surname_like(“cla”)
Member.find_like_surname(“cla”)

How are you going to distinguish these methods operating on a model with
a
column “surname” vs a column “surname_like”. Granted that’s an odd name
for a table column, but it could happen.

Member.find(:like => { Surname => “cla”})

This seems so close to just using the :conditions I don’t see the
point…

Personally I prefer the first one. Josh S. has a good article on
how dynamic finders work
(has_many :through - How dynamic finders work), so
this would make an interesting project for me to get my hands dirty in
Rails code. I also notice a proposed fulltext search on the wiki
(http://wiki.rubyonrails.org/rails/pages/FullTextSearch).

But before I have a go at this - how do you think something like this
should work?

For me, no, but don’t let me stop you :slight_smile:

-philip

3

Philip H. wrote:

Like you’ve done above, but I tend to usee:

[“surname LIKE ?”, “%{params[:query]}%”]

That’s a little more readable.

Is these a case to be made for adding “like” support to rails?

Not for me, but then again, I’m not everybody :slight_smile: One potential problem
is
once you add like someone is going to want regex (ie. the ~ operator in
postgres for example) and they’ll probably also want “NOT LIKE” and then
there’s the whole issue of case [in]sensitivity between databases…

I’m just saying that there’s a lot of options and they tend to get
somewhat database specific so maybe it’s a hard thing to generalize.

Also, if you did to “like”, I’d also like “begins_with” that left off
the
initial ‘%’… and so on :slight_smile:

Ughh.

If so - what should it look like? Here are some ideas:

Member.find_by_surname_like(“cla”)
Member.find_all_by_surname_like(“cla”)
Member.find_surname_like(“cla”)
Member.find_like_surname(“cla”)

How are you going to distinguish these methods operating on a model with
a
column “surname” vs a column “surname_like”. Granted that’s an odd name
for a table column, but it could happen.

Fair point, but this is not much different to accidentally overriding
any other existing method.

A bit more searching revealed TextSearch
(http://wiki.rubyonrails.org/rails/pages/TextSearch), which seems like a
more elegant solution in a lot of ways…

4

Matt J. wrote:

On 1/16/07, Andrew S. [email protected] wrote:

OK - What if you don’t know the exact surname, or what a list of every
member with “cla” in their surname?

You might want to check out acts_as_ferret - it is overkill for just
looking at
surnames, but your introduction seemed to indicate you’ll want to search
for
more things real-soon-now…

Yes - the next step would have been to widen the search to include other
columns, tables, models, etc.

This plugin has successfully killed any notion of building my own.
Please move along, nothing to see here…

5

On 1/16/07, Andrew S. [email protected] wrote:

OK - What if you don’t know the exact surname, or what a list of every
member with “cla” in their surname?

You might want to check out acts_as_ferret - it is overkill for just
looking at
surnames, but your introduction seemed to indicate you’ll want to search
for
more things real-soon-now…

–
Matt J.
[email protected]
President/Technical Director, Acme Art Company (acmeartco.org)

6

Also, if you did to “like”, I’d also like “begins_with” that left off the
initial ‘%’… and so on :slight_smile:

Ughh.

:slight_smile:

Fair point, but this is not much different to accidentally overriding any
other existing method.

Sort of… overwriting an existing method that is known about is one
thing, but since this would potentially be over writing a method that is
dynamically created (or handled by method_missing) if and only if the
model happens to have the right column name could make for a lot of head
banging :frowning:

A bit more searching revealed TextSearch
(http://wiki.rubyonrails.org/rails/pages/TextSearch), which seems like a more
elegant solution in a lot of ways…

Oooh… that’s nice… maybe a variation/extension of that so you
wouldn’t
have to pick a set of columns, but could do a dynamic clone of say:

Model.search_by_surname

That removes any ambiguity… and lets you do
Model.search_by_surname_and_first_name

which would come in kind of handy :slight_smile:

7

Philip H. wrote:

Oooh… that’s nice… maybe a variation/extension of that so you
wouldn’t
have to pick a set of columns, but could do a dynamic clone of say:

Model.search_by_surname

That removes any ambiguity… and lets you do
Model.search_by_surname_and_first_name

which would come in kind of handy :slight_smile:

Seems like a good idea and should be pretty easy to copy over the
dynamic finder code and make any adjustments needed.

I will give it a go in the next week or two - a great way to learn is by
trying to solve problems :slight_smile: