如何在Opaleye中实现这种模糊搜索sql查询？

Question

我有一个sql查询，我正试图将其转换为Opaleye。

我将简化我的代码，将重点放在我的问题上，即如何使用Opalyee进行模糊搜索。

Haskell (有一些现有的Opaleye结构)是

namesTable :: O.Table NamesColumnWrite NamesColumnRead
namesTable = O.Table "names" (pNames Names { id = O.optional "id"
                                            , licenseNumber = O.required "license_number"
                                            , fullName = O.required "full_name"
                                            })

import qualified Database.PostgreSQL.Simple as PGS

data Names' id' licenseNumber' fullName' =
    Names
        { id           :: id'
        , licenseNumber     :: licenseNumber'
        , fullName          :: fullName'
        } deriving (Show, Eq)

namesQuery :: Query NamesColumnRead
namesQuery = queryTable NamesTable

type NamesRead = Names' Int String String
type NamesWrite = Names' (Maybe Int) String String
type NamesColumnWrite = Names' (Maybe (O.Column O.PGInt4)) (O.Column O.PGText) (O.Column O.PGText)
type NamesColumnRead = Names' (O.Column O.PGInt4) (O.Column O.PGText) (O.Column O.PGText)

getNamesByFuzzyLicenseNumber :: PGS.Connection -> String -> IO [NamesRead]
getNamesByFuzzyLicenseNumber conn licNumber = do
  let query' = "SELECT id, license_number, full_name FROM get_names_by_fuzzy_license_number_fn(?)"
  PGS.query conn query' [licNumber]

而Sql是

CREATE OR REPLACE FUNCTION get_names_by_license_number_fuzzy_fn(p_license_number VARCHAR)
  RETURNS TABLE
    (id INT
    ,license_number VARCHAR
    ,full_name TEXT
    )
AS $$
  SELECT
    id,
    license_number,
    full_name
  FROM names
  WHERE license_number LIKE '%' || p_license_number || '%'
$$
LANGUAGE sql;

所谓“模糊搜索”，我指的是搜索任何包含搜索参数p_license_number值作为子字符串的许可号的行。

如何将此模糊搜索查询转换为Opaleye?

这将涉及删除Sql文件并将getNamesByLicenseNumber替换为Haskell。我想答案可能是

getNamesByLicenseNumber :: String -> Query NamesColumnRead
getNamesByLicenseNumber licNumber = proc () -> do
  names <- namesQuery -< ()
  restrict -< pgString licNumber .== licenseNumber (names :: NamesColumnRead)
  returnA -< names

除了模糊而不是严格的匹配。

Answer 1

我不知道你的问题到底是什么，因为你的问题看上去很好。有两件事我会改进的。首先，将字符串作为参数传递给QueryArr，而不是函数参数。其次，使用like而不是.==。

getNamesByLicenseNumber :: QueryArr (Column PGText) NamesColumnRead
getNamesByLicenseNumber = proc licNumber -> do
  names <- namesQuery -< ()
  restrict -< licNumber `like` licenseNumber (names :: NamesColumnRead)
  returnA -< names

这能做你想做的事吗？