find_by_like?

rubyonrails-talk
1

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?

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

2

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.

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

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: