Support ilike for Postgres

[gstreetmedia]

Waterline version:0.13.4
Node version:8.9
NPM version:5.0
Operating system:All

---

waterline/utils/query/private/normalize-constraint.js should support ILIKE. This would be a super simple change the the existing 'like' condition.

A simple update to:

else if (modifierKind === 'like' || modifierKind === 'ilike') {

[sailsbot]

Hi @gstreetmedia! It looks like you missed a step or two when you created your issue. Please edit your comment (use the pencil icon at the top-right corner of the comment box) and fix the following:

• Provide your Waterline version
• Provide your Node version
• Provide your NPM version
• Provide your Operating system
• Verify "I am experiencing a concrete technical issue with Waterline (ideas and feature proposals should follow the guide for proposing features and enhancements (http://bit.ly/sails-feature-guide), which involves making a pull request). If you're not 100% certain whether it's a bug or not, that's okay--you may continue. The worst that can happen is that the issue will be closed and we'll point you in the right direction."
• Verify "I have already searched for related issues, and found none open (if you found a related closed issue, please link to it in your post)."
• Verify "I can provide steps to reproduce this issue that others can follow."

As soon as those items are rectified, post a new comment (e.g. “Ok, fixed!”) below and we'll take a look. Thanks!

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact [email protected]

[sailsbot]

Sorry to be a hassle, but it looks like your issue is still missing some required info. Please double-check your initial comment and try again.

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact [email protected]

[gstreetmedia]

Ok, fixed

[oaksofmamre]

hey @gstreetmedia for some use-cases, you may be able to solve this by normalizing your data going into the database (or coming out out of it). This would work well with something like email addresses. But if this issue comes up a lot, and a high-quality search is important for the group you are working with, you can use something like elasticsearch (grab from npm and you can integrate in with sails, not waterline)

[johnabrams7]

@gstreetmedia @oaksofmamre Hey everyone, we're moving all the Waterline issues from to Sails (balderdashy/sails) for closer management of issues and exposure to the community. I see one response to the original questions, however feel free to let us know if this issue is still pending a resolution. Otherwise, thanks for all the help so far.

[illz]

I just ran into this issue - I want to search my database for a customer's name - unfortunately searching for "testing" using the model's find-where-contains yields a query of "WHERE lastName LIKE '%testing%'" which doesn't match "Testing" because postgres LIKE is case-sensitive. How can I force an "ILIKE" instead of "LIKE" ?
Edit: Had to go with a native query. Official support would be cool though.

[raqem]

Hi @gstreetmedia like @illz suggested with Sails V1 when it comes to case sensitivity your best option is to use a native query. Please check out this gitter chat where Mike explains the reasoning. Also if you haven't checked-out the gitter group before, you should! It's Sails most active community platform.

[abitoprakash]

This would be helpful. Would be happy to raise a pull request for this

@mikermcneil , @DominusKelvin

[eashaw]

@abitoprakash We'd be happy to take a look at a PR for this!

[abitoprakash]

Thanks for the update, @eashaw! I'd be happy to contribute. Since ILIKE is specific to PostgreSQL, I’m wondering if Waterline is still the best place to handle this, or should it be approached in sails-postgresql instead? I’d appreciate some guidance on how to proceed

[DominusKelvin]

Thanks for the update, @eashaw! I'd be happy to contribute. Since ILIKE is specific to PostgreSQL, I’m wondering if Waterline is still the best place to handle this, or should it be approached in sails-postgresql instead? I’d appreciate some guidance on how to proceed

That's a great observation. Can you share how the userland API will be for this?

[abitoprakash]

@DominusKelvin, I was thinking we could follow the approach used in sails-mongo and utilize the same meta parameter, makeLikeModifierCaseInsensitive, to tell sails-postgres to use ILIKE rather than LIKE.

An example would be

const musicCourses = await Course
  .find({ subject: { like: 'music' }})
  .meta({ makeLikeModifierCaseInsensitive: true });

[DominusKelvin]

@DominusKelvin, I was thinking we could follow the approach used in sails-mongo and utilize the same meta parameter, makeLikeModifierCaseInsensitive, to tell sails-postgres to use ILIKE rather than LIKE.

An example would be

const musicCourses = await Course

  .find({ subject: { like: 'music' }})

  .meta({ makeLikeModifierCaseInsensitive: true });

Oh I think I love that API, can we also add it globally via datastore.js?

[abitoprakash]

Thanks @DominusKelvin, I think we can add it in datastores as well, for now I have created a pull request - balderdashy/sails-postgresql#297 which checks for the flag in meta. Please review it when you have a chance, and let me know if this is the right approach. I took inspiration from build-mongo-where-clause.js

[abitoprakash]

Hi guys, any updates on this?

[DominusKelvin]

Hey all I haven't had the time review yet but I will this Friday!