sbditto85 sbditto85 - 1 year ago 61
MySQL Question

Esqueleto: How to execute a query with more then 16 columns?

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.


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.


if you look at the documentation for

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download