Haskell Persistent BackendFilter

Question

I'm trying to use the match operator of Sqlite3 FTS3/4 tables with Persistent (in Yesod).

I've succeeded in creating my own match operator:

-- | Implements the `match` operator. This operator is specific to Sqlite3 and
--   is used to look for keywords in FTS3/4/5 tables.
match :: EntityField record Text    -- ^ Field to filter on
      -> Text                       -- ^ Text to compare with
      -> Filter record              -- ^ Resulting filter
match field val = Filter field (Left val) (BackendSpecificFilter "match")

It works fine but does not allow to use a very specific (strange?) feature of Sqlite3 FTS3/4 tables: you can specify the table name instead of just a column name. The effect is that the match operator will look for the searched terms in every column of the table.

It means you can write queries like:

SELECT *
FROM   tablename
WHERE  tablename MATCH "hello";

Such queries are described in the Sqlite3 FTS3/4 documentation https://sqlite.org/fts3.html#simple_fts_queries

Reading the Persistent documentation and the Filter definition, it might be possible to create this filter using a BackendFilter but I have not found any example of how actually using it.

What also puzzled me is the use of the type family BackendSpecificFilter which is used as a constructor in PersistFilter.

I would like to be able to write queries like:

mkPersist persistSettings [persist|
    User
        forename String
        surname String
        bio String
|]

users <- runDB $ selectList [ User `matchAll` searchedTerms ] []

Can someone show me the right way to use BackFilter in this case?

Thanks

zigazou zigazou · Accepted Answer · 2018-07-10T15:46:31

From what I investigated, it is not possible to create a BackendFilter as easily as a BackendSpecificFilter. The first is meant to be interpreted specifically by the database layer meaning one would have to patch the persistent-sqlite package in order to allow what I described.

Haskell Persistent BackendFilter

1 Answers