Update search query to use ILIKE operator
Proposed fix for #99
- Replaces
LIKEoperator to useILIKEoperator for searches to be case-insensitive
Thank you for this PR, I got my hands on to it and my testing showed that global search is much more reliable now. However I found one edge case here not related to this issue.
Let's see generated SQL:
SELECT * FROM events WHERE kind = ANY(ARRAY [0])
AND EXISTS (
SELECT 1 FROM
jsonb_array_elements(tags) as elem WHERE elem::text ILIKE '<query>'
OR content ILIKE '%<query>%')
ORDER BY created_at DESC LIMIT 100 ;
We are iterating through tags array and checking conditions. However if tags array is empty then we will never hit content ILIKE condition and miss some events. I reproduced this issue on our relay and we are missing some user profiles
The fix here would be to move OR content ILIKE <query> outside of EXISTS subquery. Please let me know if I should create another issue for that so you can merge this PR and close previous issue
I will try to make this fix myself. Unfortunately I do not have much experience in this area but I will try my best