nostpy-relay icon indicating copy to clipboard operation
nostpy-relay copied to clipboard

Update search query to use ILIKE operator

Open UTXOnly opened this issue 9 months ago • 1 comments

Proposed fix for #99

  • Replaces LIKE operator to use ILIKE operator for searches to be case-insensitive

UTXOnly avatar Mar 30 '25 14:03 UTXOnly

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

gasaichandesu avatar Apr 11 '25 23:04 gasaichandesu