I'm trying to select all Events from the database that contain a Tag in the Event's tags column.
Event is a model with a column tags that is defined in Yesod's model file as tags [Tag]. Tag is a model that is simply newtype Tag = Tag Text with a lot of derivings including PersistField which should allow Tag types to be stored as Text types.
I would like to define a query that selects all events that contain a given tag in their tags column. Something like:
getEventsByTag :: Tag -> Handler Value
getEventsByTag tag = do
events <- runDB findEvents :: Handler [Entity Event]
return $ object ["events" ==. events]
where
findEvents =
select $
from $ \event -> do
where_ $ (event ^. EventTags) `contains` (val tag)
return event
The part I'm not sure how to do is the contains function. It doesn't seem likely that I can use any postgres array functions since Persistent stores arrays as varchars in postgres. In which case I could get fancy with like. I was actually able to get the result I wanted with this query in postgres: select * from events where tags like '%"tag"%'; But I'm not sure how to translate this into Esqueleto's language. I tried using the like function but it requires both arguments to be instances of SqlString which I made Tag derive but had trouble figuring out how to make [Tag] derive without overlapping instances.
