Pistos/diaspora Issue #30: Switch from ILIKE to LOWER()'ed LIKE in Search Own Posts

gistfile1.txt

search own comments and posts:

ISSUE:

This was giving a 500 error on my pod running mysql:

for example, where the query was for "single":

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '%single%') ORDER BY created_at desc LIMIT 30' at line 1: SELECT  `posts`.* FROM `posts` WHERE (`posts`.author_id = 1) AND (text ILIKE '%single%') ORDER BY created_at desc LIMIT 30



PROPOSED SOLUTION:

Changed query from "ILIKE" to "LIKE and LOWER()-ing both input string and comments/posts" in app/controllers/posts_controller.rb around line 91.

From:

      @posts = current_user.posts.where('text ILIKE ?', "%#{q}%").order('created_at desc').limit(30)
      @comments = current_user.comments.where('text ILIKE ?', "%#{q}%").order('created_at desc').limit(30)

To:

      @posts = current_user.posts.where('LOWER(text) LIKE LOWER(?)', "%#{q}%").order('created_at desc').limit(30)
      @comments = current_user.comments.where('LOWER(text) LIKE LOWER(?)', "%#{q}%").order('created_at desc').limit(30)