12

I would like to do a LIKE query in persistent, I'm using sqlite. The yesod book gives an example of using raw SQL to do it, but says:

you can express a LIKE operator directly in the normal syntax due to a feature added in Persistent 0.6, which allows backend-specific operators

I couldn't find an example of that, though. Would somebody have an example of what it would mean to use a specific operator like LIKE with selectList or something equivalent?

Thanks!

JP Moresmau
  • 7,388
  • 17
  • 31

1 Answers1

10

I know I've used it before, but I can't remember where. Anyway, a simple example (not GHC-checked, apologies) would be:

selectList [Filter PersonName (Left $ PersistText "%Michael%") (BackendSpecificFilter "ILIKE")] []

Obviously you can create some helper function, e.g.:

icontains field val = Filter field (Left $ PersistText $ T.concat ["%", val, "%"]) (BackendSpecificFilter "ILIKE")
selectList [Personname `icontains` "Michael"] []
Michael Snoyman
  • 31,100
  • 3
  • 48
  • 77
  • 1
    Thanks. The code does not compile, as you feared, but you were not far: Filter field (Left $ T.concat ["%", val, "%"]) (Filter seems to take a Either Text and not Either PersistText) – JP Moresmau Jun 20 '12 at 18:39
  • 3
    Neither of these worked for me, here's what did: `like field val = Filter field (Left $ T.concat ["%", val, "%"]) (BackendSpecificFilter "like")` – Vlad the Impala Apr 25 '13 at 07:10
  • 1
    Future readers who want the easiest way to do `ILIKE` in Haskell and PostgreSQL, use [this](https://hackage.haskell.org/package/esqueleto-2.5.3/docs/Database-Esqueleto.html#v:ilike). – Jezen Thomas Mar 24 '18 at 14:29
  • Where is this T.concat from? – FtheBuilder Mar 29 '21 at 19:26
  • `Data.Text` from the `text` package. – Michael Snoyman Mar 31 '21 at 04:10
  • This is what worked for me: ```icontains :: EntityField r T.Text -> T.Text -> Filter r``` ```icontains field val = Filter field (FilterValue $ T.concat ["%", val, "%"]) (BackendSpecificFilter "ilike")``` – Sergiu Starciuc Jan 05 '22 at 12:56