Use CTE value for query offset

[ShawnToubeau]

Hi all!

I'm trying to use build a Jet equivalent query of this SQL

WITH rowNumCte AS (SELECT sub.row_num
                   FROM (SELECT id,
                                ROW_NUMBER() OVER (ORDER BY created_at desc) - 1 AS row_num
                         FROM completion
                         WHERE owner_id = 'cust_1') as sub
                   WHERE sub.id = 'completion_10')
SELECT *
FROM completion
WHERE owner_id = 'cust_1'
ORDER BY created_at desc
LIMIT 10 
OFFSET (SELECT row_num FROM rowNumCte) - 10 / 2;

Here I'm using a subquery in the CTE to get all records and their row number in table completion. The CTE then selects the exact record + row number I want. In my main query I then use the row number of that record as the offset.

Here's my attempt at a Jet query but I can't figure out how to use the CTE in the offset.

// get row numbers for all completions
subQuery := SELECT(
  Completion.ID,
  ROW_NUMBER().OVER(ORDER_BY(Completion.CreatedAt)).SUB(Int(1)).AS("row_num"), // subtract by 1 to be zero-indexed
).
  FROM(Completion).
  WHERE(Completion.OwnerID.EQ(String(ownerId))).
  AsTable("completionRowNumbers")

// for subquery join condition
rowNumId := Completion.ID.From(subQuery)

cteSelect := SELECT(
  subQuery.AllColumns(),
).FROM(
  subQuery.
    INNER_JOIN(Completion, Completion.ID.EQ(rowNumId))).
  // unsure if there's a way to do this without a raw expression
  WHERE(RawBool("completionRowNumbers.id = :string", RawArgs{":string": completionId}))

var rowNum = IntegerColumn("row_num")
rowNumCte := CTE("row_num", rowNum).AS(cteSelect)

stmt := WITH(rowNumCte)(SELECT(Completion.AllColumns).
  FROM(Completion).
  WHERE(Completion.OwnerID.EQ(String(ownerId))).
  ORDER_BY(Scenario.CreatedAt.DESC()).
  LIMIT(10).
  OFFSET() // not sure how to access the row number here

I looked through the wiki and the Jet code for similar examples but I didn't see anything. Apologies if this is the wrong way to ask general questions, please let me know if there's a better place to post this! Thanks :)