0

I'm building a CRUD application that pulls data using Persistent and executes a number of fairly complicated queries, for instance using window functions. Since these aren't supported by either Persistent or Esqueleto, I need to use raw sql.

A good example is that I want to select rows in which the value does not deviate strongly from the previous value, so in pseudo-sql the condition is WHERE val - lag(val) <= x. I need to run this selection in SQL, rather than pulling all data and then filtering in Haskell, because otherwise I'd have way to much data to handle.

These queries return many columns. However, the RawSql instance maxes out at tuples with 8 elements. So now I am writing additional functions from9, to9, from10, to10 and so on. And after that, all these are converted using functions with type (Single a, Single b, ...) -> DesiredType. Even though this could be shortened using code generation, the approach is simply hacky and clearly doesn't feel like good Haskell. This concerns me because I think most of my queries will require rawSql.

Do you have suggestions on how to improve this? Currently, my main thought is to un-normalize the database and duplicate data, e.g. by including the lagged value as column, so that I can query the data with Esqueleto.

cgold
  • 4,075
  • 1
  • 12
  • 13
  • How many columns do you need? Would it make more sense to write `RawSql` instances for each of your `DesiredType`s instead? How many of those do you have? Alternatively, if you don't want to write instances for everything, you should be able to just stick with the existing tuple implementations and use nested tuples when things get too big (that's how the existing tuple instances are implemented, it seems). – DarthFennec May 29 '18 at 22:12
  • @DarthFennec Thanks! I have many queries with many columns, so I'd need to create a `fromX` function for each number of resulting columns. Thanks for the trick with nested tuples. But thinking through how it would work, these queries might simply not be a good use case for `rawSql` - if I am already doing all the work parsing a result using a lot of tuples of Singles, I might be better off starting with `withStmt :: (MonadSqlPersist m, MonadResource m) => Text -> [PersistValue] -> Source m [PersistValue]` and parse the resulting `[PersistValue]`. I'll see whether that approach works better. – cgold May 30 '18 at 02:33
  • If you are in any case writing raw sql then you might want to look at `hasql` which is specifically written for that use case. It is a mature and high quality library. – Mikkel May 31 '18 at 14:41

0 Answers0