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(Completion.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. Thanks :)

[go-jet]

Hi @ShawnToubeau ,
Hmm, CTE in the offset. This isn't supported. Feel free to reopen the issue, because this seems like a missing feature.
Is this for postgres db?

As a workaround, you can use a RawStatement. Or you can append manually offset on stmt.Sql() response.

// unsure if there's a way to do this without a raw expression
WHERE(RawBool("completionRowNumbers.id = :string", RawArgs{":string": completionId}))

https://github.com/go-jet/jet/wiki/Subquery#what-if-sub-query-projection-is-not-a-table-column

[ShawnToubeau]

Thanks for the response! Yes, this is for Postgres.

As a follow up question that might related, I split my query into 2 statements as a work around. I then updated my OFFSET to this

OFFSET GREATEST(0, 
                LEAST(5 - 10 / 2, # 5 is my row number, 10 is my limit
                      (select count(*) 
                       from completion 
                       where owner_id = 'cust_1') - 10)) # also my limit

because I need it to stay within the bounds of the record count. I translated it to this Jet statement:

countStmt := SELECT(COUNT(Completion.ID)).
			WHERE(Completion.OwnerID.EQ(String(ownerId)))
least := LEAST(Int(int64(rowNum - limit / 2)), countStmt - Int(limit)) // utilize count value
offset := GREATEST(Int(0), least)

Can I use the count returned by countStmt in my LEAST above?

And offset is typed as jet.Expression. Is there a way to convert this because OFFSET() expects a int64?

[go-jet]

Yeah, two queries will also work. Something like:

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)
rowNum := IntColumn("row_num").From(subQuery)

stmt := SELECT(
  subQuery.AllColumns(),
).FROM(
  subQuery.
    INNER_JOIN(Completion, Completion.ID.EQ(rowNumId)),
).WHERE(
  rowNumId.EQ(String(completionId)),
)

var dest {
	RowNum int
}

err := stmt.Query(db, &dest)

// calculate offset
offset := ... dest.RowNum ...

stmt2 := SELECT(
  Completion.AllColumns,
).FROM(
  Completion,
).WHERE(
  Completion.OwnerID.EQ(String(ownerId)),
).ORDER_BY(
  Completion.CreatedAt.DESC(),
).LIMIT(
 10,
).OFFSET(
 offset,
)