Handling List-types with Esqueleto

sql

database-operations

list-types

sql-queries

byAnton Shumikhin·Dec 18, 2024

To efficiently query list-types in Esqueleto, lean on the in_ function together with valList. Here's a versatile pattern for searching within an Entity's field:

fetchByList :: [FieldValue] -> IO [Entity]
fetchByList values = runDb $ select $
  from $ \entity -> do
  where_ $ (entity ^. EntityField) `in_` valList values
  return entity

In this query, rows are returned from Entity where EntityField matches any element in values. valList readies the Haskell list for Esqueleto, while in_ conducts filtering based on this list, comparable to the SQL's IN.

Considerations for list operations

Esqueleto provides a type-safe way to build SQL queries through Haskell. However, when working with advanced list operations, you may encounter situations requiring the brute force of classic SQL for performance fit or the means to use custom SQL expressions for unorthodox queries.

Maximizing performance

When it comes to certain operations, and speed rules all, SQL's raw capability can prove invaluable:

runDb $ rawSql "SELECT * FROM Entity WHERE EntityField IN ?" [PersistList values]

Yes, SQL, you're still the fastest kid in the town!

Adopting custom queries

Operational needs sometimes are outside the reach of predetermined Esqueleto functions, extending an invitation to additional custom queries or SQL expressions, yet comprehensively wrapped around Haskell for safety. Try not to get lost in custom queries, breadcrumbs might help!

Participation maters

Feature requests, ideas for enhancements, and other constructive discussions can be had at Esqueleto's GitHub repository. This is your chance to influence the future of this library!

Taking decisions accordingly

Prioritize your operation based on its usage frequency in your application. Assess whether it's worth getting the Esqueleto approach or revisiting classic SQL. Just make sure not to flip a coin for this!

Setting expectations right

Its important to understand that currently, not all SQL functionalities are mirrored by Esqueleto. However, with the right mapping, we can seamlessly integrate most of them.

Tackling list-types in real-life scenarios

In practice, working with list-types often lead us to specific use cases like filtering, joining, or aggregating data. Here we will touch upon effective methods to handle different scenarios with pragmatism in mind.

The art of filtering

As we go beyond the in_ operator, sometimes the need for more advanced filtering emerges. Intersection or difference, anyone?

fetchIntersecting :: [FieldValue] -> IO [Entity]
fetchIntersecting values = runDb $ select $
  from $ \entity -> do
  where_ $ not_ $ isEmpty $ arrayOverlap (entity ^. EntityFieldArray) (valList values)
  return entity

When joins meet list-types

On occasion, dealing with list-types across different tables demands a JOIN operation. This can bring a subquery to the table for harmonious list interaction:

fetchJoinList :: IO [(EntityParent, [EntityChild])]
fetchJoinList = runDb $ select $
  from $ \(parent `LeftOuterJoin` child) -> do
  on $ child ?. EntityChildParentId ==. just (parent ^. EntityParentId)
  groupBy (parent ^. EntityParentId)
  return (parent, collect (child ?. EntityChildField))

Aggregate to elucidate

Manage aggregations with a little help from friends like groupBy and countRows:

fetchAggregatedList :: IO [(Value FieldValue, Value Int)]
fetchAggregatedList = runDb $ select $
  from $ \entity -> do
  groupBy (entity ^. EntityField)
  return (entity ^. EntityField, countRows)

Just play nice with GROUP BY!

Pitfalls & remedies

Keep a wary eye open for the spooky N+1 query problems which can sneak in when performing separate queries for each element in a list. Batch those or utilize more intricate joins to put an end to their haunting!