search

bitemyapp / esqueleto

New home of Esqueleto, please file issues so we can get things caught up!
BSD 3-Clause "New" or "Revised" License
376 stars 108 forks source link

Examples: Materialized Views #97

Open FintanH opened 6 years ago

FintanH commented 6 years ago

Flesh out materialized views started on bitemyapp/materialized-view-example

parsonsmatt commented 5 years ago

If you'd like to get a PR together then we can track discussion in that instead :)

rinn7e commented 2 weeks ago

Might not be related to this issue itself, but if the other is searching on how to use "Materialized Views" with esqueleto, this is what I do.

-- In a query file
messagesWithFilterProfileIds :: SqlQuery ...
messagesWithFilterProfileIds = ...

-- In a db type file
share [mkPersist sqlSettings] [persistLowerCase|
  MessagesWithProfileIds sql=messages_with_filter_profile_ids
    messageId              MessageId
    ...
    deriving Eq Ord Show
|]

-- In migration
createView_messagesWithFilterProfileIds :: MonadUnliftIO m => SqlPersistT m ()
createView_messagesWithFilterProfileIds = do
  (query, params) <- renderQueryToText SELECT messagesWithFilterProfileIds
  rawExecute ("DROP MATERIALIZED VIEW IF EXISTS messages_with_filter_profile_ids") params
  rawExecute ("CREATE MATERIALIZED VIEW messages_with_filter_profile_ids as " <> query) params

migrate :: MonadUnliftIO m => SqlPersistT m ()
migrate = do
  runMigration migration
  _ <- createView_messagesWithFilterProfileIds
  pure ()