Persistent - how to filter on a field of a record column

Question

I'm in the middle of trying to build my first "real" Haskell app, an API using Servant, where I'm using Persistent for the database backend. But I've run into a problem when trying to use Persistent to make a certain type of database query.

My real problem is of course a fair bit more involved, but the essence of the problem I have run up against can be explained like this. I have a record type such as:

data Foo = Foo { a :: Int, b :: Int }

derivePersistField "Foo"

which I am including in an Entity like this:

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
    Thing
        foo Foo
|]

And I need to be able to query for items in my database for which their Foo value has its a field greater than some aMin that is provided. (The intention is that it will actually be provided by the API request in a query string.)

For ordinary queries, say for an Int field Bar, I could simply do selectList [ThingBar >=. aMin] [], but I'm drawing a blank as to what to put in the filter list in order to extract the field from the record and do a comparison with it. Even though this feels like the sort of thing that Haskell should be able to do rather easily. It feels like there should be a Functor involved here that I can just fmap the a accessor over, but the relevant type, as far as I can tell from the documentation and the tutorial, is EntityField Thing defined by a GADT (actually generated by Template Haskell from the share call above), which in this case would have just one constructor yielding an EntityField Thing Foo, which it doesn't seem possible to make a Functor instance out of.

But without that, I'm drawing a blank as to how to deal with this, since the LHS of a combinator like >=. has to be an EntityField value, which stops me from trying to apply functions to the database value before comparing.

Since I know someone is going to say it (and most of you will be thinking it) - yes, in this toy example I could just as easily make the a and b into separate fields in my database table, and solve the problem that way. As I said, this is somewhat simplified, and in my real application doing it that way would feel unsatisfactory for a number of reasons. And it doesn't solve my wider question of, essentially, how to be able to do arbitrary transformations on the data before querying. [Edit: I have now gone with this approach, in order to move forward with my project, but as I said it's not totally satisfactory, and I'm still waiting for an answer to my general question - even if that's just "sorry it's not possible", as I increasingly suspect, I would appreciate a good explanation.]

I'm beginning to suspect this may simply be impossible because the data is ultimately stored in an SQL database and SQL simply isn't as expressive as Haskell - but what I'm trying to do, at least with record types (I confess I don't know how derivePersistField marshals these to SQL data types) doesn't seem too unreasonable so I feel I should ask if there are any workarounds for this, or do I really have to decompose my records into a bunch of separate fields if I want to query them individually.

[If there are any other libraries which can help then feel free to recommend them - I did look into Esqueleto but decided I didn't need it for this project, although that was before I ran into this problem. Would that be something that could help with this kind of query?]

Answer 1

You can use the -ddump-splices compiler flag to dump the code being generated by derivePersistField (and all the other Template Haskell calls). You may need to pass -fforce-recomp, too, if ghc doesn't think the file needs to be recompiled.

If you do, you'll see that the method persistent uses to marshal the Foo datatype to and from SQL is to use its read and show instances to store it as a text string. (This is actually explained in the documentation on Custom Fields.) This also means that a query like:

stuff <- selectList [ThingFoo >=. Foo 3 0] []

actually does string comparison at the SQL level, so Thing (Foo 10 2) wouldn't pass through this filter, because the string "Foo 10 2" sorts before "Foo 3 0".

In other words, you're pretty much out of luck here. Custom fields created by derivePersistField aren't really meant to be used for anything more sophisticated than the example from the Yesod documentation:

data Employment = Employed | Unemployed | Retired

The only way you can examine their structure would be to pass in raw SQL to parse the string field for use in a query, and that would be much uglier than whatever you're doing now and presumably no more efficient than querying for all records at the SQL level and doing the filtering in plain Haskell code on the result list.