SELECT _id, name,isApprovedExam,security,thumbnail,active,createdAt,modifiedAt,questions,users,revealFeedBack,passPercent,activityInfo,title,subTitle,marks,examCode,modeType,fromDateTime,timeDuration,startActivity,endActivity,instructions
FROM content
WHERE _type="activity" AND isApprovedExam=true AND active=true AND userId ="cjkkti4n700rn0a44fyt05fcs" AND LOWER(name) LIKE "%abcdyg%"
ORDER BY createdAt DESC LIMIT 10 OFFSET 0
For the above query there are many records exists ,which satisfies all the condition in the query .Still it is not returning any result.
And for this query it indexing is like
CREATE INDEX `activity` ON `content`(`_id`,`_type`,lower(`name`) DESC,`createdAt` DESC,`organizationId`,`userId`,`active`) WHERE (`_type` = "activity") WITH { "defer_build":true }
if I remove and LOWER(name) like “%abcdyg%"
the query is returning multiple records. But I have to filter with name field with LIKE operator.
Am I missing something in the query or index??
How can I use LIKE operator successfully in the above query?