Search within topic is omitting results

I’ve been thinking of various ways to solve the problem.

1: change lib/search.rb line 484

posts = posts.where("posts.raw  || ' ' || u.username || ' ' || u.name ilike ?", "%#{@term}%")

to simply not test against users.name
Works, but eg. will not match searches for “Atwood” with “codinghorror”

2: add conditional
if null do this else if not null do this
Feels like bloat to me.

3: change lib/search.rb line 484 to

posts = posts.where("posts.raw  || ' ' || u.username || ' ' || quote_nullable(u.name) ilike ?", "%#{@term}%")

This essentially changes a NULL into the text string “NULL”
One possible drawback is if one were to search for the string “NULL” it would find posts by such members.
But IMHO this would be extreme edge case and I feel adding quote_nullable would suffice and eliminate any need to make code changes elsewhere.

http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

Tested on localhost and no side effects that I could see.

4 Likes