1

Well basically it boils down to is it possible to have a query that returns more then 16 columns in esqueleto?

I have a legacy db that I have to interact with and it requires joining tables with 30-40 columns then picking out like 20 of the possible 80-100 columns that I need so I'm not pulling columns that are large unless needed. It would seem that with esqueleto I am limited to 16 columns? Is there any way to overcome that without orphan instances?

Thanks in advance!

PS if a contrived example is necessary I will provide one.

UPDATE

I found in the docs that you can do tuples of tuples which essentially allows me to get more the 16 columns, then take those nested tuples and pattern match on the results to convert it to the data type i need or use the individual values as needed.

FYI

if you look at the documentation for SqlSelect if you look at the instances then look ALL the way to the right you will see a 'hint' that is what helped me know i could do tuples of tuples.

sbditto85
  • 1,485
  • 14
  • 21
  • You can always use the `rawSql` function from the `persistent` library. Another way would be to just create a PR for Esqueleto which returns around 20 tuples. But I guess this is not the answer you are looking for. :) – Sibi Oct 10 '16 at 16:50
  • rawSql seemed to have a limit of 8 columns unless i am mistaken (i've been digging around this question for a while) – sbditto85 Oct 10 '16 at 16:54
  • yup only 8 https://hackage.haskell.org/package/persistent-2.2.4/docs/Database-Persist-Sql.html#t:RawSql – sbditto85 Oct 10 '16 at 16:59

1 Answers1

2

You can create a custom data type to represent the result of that query, which will have as many fields as you want. You then need to implement the relevant type classes for that data (PersistEntity seems to be the main one). This gives you an opportunity to provide relevant/meaningful names to the fields as well, to help prevent potential errors.

data MyResult
    = MyResult
    { fieldOne :: SomeType
    , fieldTwo :: OtherType
    , ...
    , fieldSixteen :: Text
    }

and instead of returning the (a, b, c, ..., e), you'd return MyResult a b c ... e.

As a convenience, you'll likely want to define the result using Persistent's Entity definition quasiquoter. You'll want to use that definition in a manner that it doesn't try to generate migrations for that.

ephrion
  • 2,687
  • 1
  • 14
  • 17
  • "As a convenience, you'll likely want to define the result using Persistent's Entity definition quasiquoter. You'll want to use that definition in a manner that it doesn't try to generate migrations for that." I'm not sure what that means. I'm rather new to haskell/yesod/persistent ... is that the `share [mkPersist sqlSettings, mkMigrate "migrateAll"] $(persistFileWith lowerCaseSettings "config/models")` part of yesod? in the Model.hs? – sbditto85 Oct 10 '16 at 18:12
  • Yes, but you don't want to `mkMigrate` since this isn't a table definition, but a definition of a return type on query. So you'd have a `share [mkPersist sqlSettings] [persistLowerCase| {- your model definition -} |]` in addition to that. – ephrion Oct 10 '16 at 18:29
  • Cool, I'll try it out and most likely mark your answer as correct. Hopefully i get get it to work. – sbditto85 Oct 10 '16 at 18:36
  • UPDATE: i got to a point where it wanted me to implement `SqlSelect` (https://hackage.haskell.org/package/esqueleto-2.4.3/docs/Database-Esqueleto-Internal-Sql.html#t:SqlSelect) which I could not figure out how to do. However it does say you can return tuples of tuples which might work out ...... to be continued – sbditto85 Oct 10 '16 at 22:48
  • FINAL UPDATE: I tried to implement the SqlSelect class but couldn't figure it out in an hour or so and decided to just use nested tuples and then convert that to a datatype. – sbditto85 Oct 11 '16 at 14:59
  • Marked this as the answer because if i could figure out how to implement `SqlSelect` I think it would indeed "just work" – sbditto85 Oct 11 '16 at 15:07