I'm trying to translate the following SQL to Esqueleto:
SELECT id, task_id, author_id
FROM scenario
INNER JOIN ( SELECT task_id as tId, author_id as aId, MAX(last_update) as lastUp
FROM scenario
GROUP BY task_id, author_id
) t
ON task_id = tId AND author_id = aId AND last_update = lastUp
To do a sub-query, you have to use subList_select.
I couldn't figure out a way to combine it with the pattern matching in:
from $ \(s `InnerJoin` ?subQueryhere?) -> do ...
So I tried with where_
instead:
where_ (s ^. ScenarioTaskId ==. (subList_select $
from $ \s' -> do
groupBy (s' ^. ScenarioTaskId, s' ^. ScenarioAuthorId)
return s'
) ^. ScenarioTaskId)
However, this doesn't compile since subList_select
returns a expr (ValueList a)
instead of a expr (Entity Scenario)
.
I was struggling with a similar thing.
You can use the 'experimental' module of the library (docs at https://hackage.haskell.org/package/esqueleto-3.4.2.2/docs/Database-Esqueleto-Experimental.html) which is a bit more powerful than (and subtly different from) normal esqueleto.
The main differences are around from
- you explicitely tell it what you are joining and there are quite a few options. You also need an extra extension turned on to use @TableName
tags when you want to talk about tables.
I'd be tempted to rewrite everything into experimental except the errors that come out of it are trickier to sort out as you end up using more do notation.
Anyway here's an approximation of what you might need:
(scenario :& (taskId, authorId) <-
from $ Table @Scenario `InnerJoin` SubQuery (do
scenario <- from $ Table @Scenario
groupBy (scenario ^. ScenarioTaskId, scenario ^. ScenarioAuthorId)
return (scenario ^. ScenarioTaskId, scenario ^. ScenarioAuthorId, max_(scenario ^. ScenarioLastUpdate))
)
`on` (\(scenario :& (taskId, authorId)) ->
(just (scenario ^. ScenarioTaskId) ==. just taskId) &&.
(just (scenario ^. ScenarioAuthorId) ==. authorId) &&.
(just (scenario ^. ScenarioLastUpdate) ==. lastUp)
)
return (scenario ^. ScenarioId, taskId, authorId)
Some adding/removing of just
s in the on
clause might be necessary! I found what I needed there very unintuitive.
Also be careful you use max_
(esqueleto) and not max
(standard library) otherwise you'll have other confusing errors!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With