Yesod Esqueleto - How can I express selects with inner pagination?
<p>I am doing a paginated resource, which will require an inner select, which I've already designed in sql terms. It has the following structure:</p>

<pre><code>select * 
  from (
    select w.*, d.distance
      from `Work` w
      inner join AdrDistance d on d.nhood2 = w.nhood
    where d.nhood1 = 1 -- this will be a variable
    order by d.distance
    limit 0, 10 -- this will be pagination
  ) w
  inner join WImage wi on wi.`work` = w.id
</code></pre>

<p>My entity definitions:</p>

<pre><code>Work
  ...

WImage
  work WorkId
  url Text

AdrNhood
  city AdrCityId
  name Text maxlen=100
  lat Double
  lng Double

-- This is a view with a computed column I used for ordering
AdrDistance
  nhood1 AdrNhoodId
  nhood2 AdrNhoodId
  distance Distance -- type Distance = Int - in Meters
</code></pre>

<p><strong>How can I define such a select in <code>Esqueleto</code>, which would resemble such structure (by doing one single query of course)?</strong></p>

<p><strong>Update</strong></p>

<p>I tried to follow this path:</p>

<pre><code>worksByNhood nId offset' limit' = 
  from $ \wi -&gt; do
  (w, d) &lt;- from $ \(w `InnerJoin` d) -&gt; do
    on $ d ^. AdrDistanceNhood2 ==. w ^. WorkNhood
    where_ (d ^. AdrDistanceNhood1 ==. val nId)
    orderBy [asc (d ^. AdrDistanceDistance)]
    offset offset'
    limit limit'
    return (w, d)
  where_ (wi ^. WImageWork ==. w ^. WorkId)
  return (w, d ^. AdrDistanceDistance, wi)
</code></pre>

<p>But it didn't drive me to the correct solution. If someone can help me (even saying that I would be better doing several selects because what I am trying is not viable in <code>Esqueleto</code>), please, comment or answer my question.</p>