[SOLVED] How to apply a function before comparison in an esqueleto query

How to apply a function before comparison in an esqueleto query

For a query simple as that

runDb . select . from $ \cell -> do
  where_ $ cell ^. CellCode ==. val "x"
  return cell

I want to apply a function before the comparison of the field value with "x". The reason is that the cell code has trailing spaces in the database and nothing easier than trimming them away, e.g. with strip from Data.Text. However, my initial approach of using fmap (twice) resulted in

No Instance for (Functor SqlExpr)

I know that there are functions provides by Esqueleto, like just, that accomplish similar things specifically (I couldn't find the implementation of just, though).

Is there a way to apply any function on the packed value?

While writing: in my specific case, I just might want to use like.

EDIT: Added the specific function I want to apply.

Solution

See here for a post that adds the postgresql function trim:

import Database.Esqueleto.Internal.Sql

trim :: (IsString s) => SqlExpr (Value s) -> SqlExpr (Value s) -> SqlExpr (Value s)
trim pattern target =
    unsafeSqlFunction "trim" (unsafeSqlBinOp "FROM" pattern target)

(If you're not using postgres, you may need to consult the documentation from your database to find if it supports something similar.)

unsafeSqlFunction can be used to import any function your database supports, but it is unsafe because you have the responsibility to make sure the type signature is actually what your database expects. The name will be copied literally to your SQL.

unsafeSqlBinOp is similar, but it defines a binary operation: unsafeSqlBinOp "FROM" "a" "b" is translated into the SQL "a" FROM "b".

With this, you should be able to do:

runDb . select . from $ \cell -> do
    where_ $ trim " " (cell ^. CellCode) ==. val "x"
    return cell