Pagination breaks with edge ordering

[henrywoody]

• The issue is present in the latest release.
• I have searched the issues of this repository and believe that this is not a duplicate.

Current Behavior 😯

When ordering a connection using an edge order field and using a cursor offset, the constructed SQL query returns an error from the database.

For example:

GraphQL query:

query {
  myModelConnection(
    orderBy: {field: OTHER_MODELS_COUNT}
    first: 5,
    after: gqFp0wAAAAsAAAAAoXaR0wAAAAAAAAAB
  ) {
    pageInfo {
      endCursor
    }
    edges {
      node {
        id
      }
    }
  }
}

Constructed SQL query:

SELECT "my_model"."id", "t1"."other_models_count"
FROM "my_model" LEFT JOIN (
	SELECT "other_model"."my_model_id", COUNT(*) AS "other_models_count"
	FROM "other_model"
	GROUP BY "other_model"."my_model_id"
) AS "t1" ON "my_model"."id" = "t1"."my_model_id"
WHERE "my_model"."other_models_count" > $1 OR ("my_model"."other_models_count" = $2 AND "my_model"."id" > $3)
ORDER BY "t1"."other_models_count" NULLS FIRST, "my_model"."id" LIMIT 6

Notice the WHERE clause references the column other_models_count on the my_model table instead of on t1. The query works fine without giving a cursor offset as no WHERE clause is included on the SQL query. This issue happens when ordering by either type of edge order field: count and field (this example shows a count).

I've also set the engql.MultiOrder annotation on these models if that is relevant.

Expected Behavior 🤔

The query should construct a SQL query that does not produce an error by referencing the subquery table for the edge order field instead of the main table. Should look like this:

SELECT "my_model"."id", "t1"."other_models_count"
FROM "my_model" LEFT JOIN (
	SELECT "other_model"."my_model_id", COUNT(*) AS "other_models_count"
	FROM "other_model"
	GROUP BY "other_model"."my_model_id"
) AS "t1" ON "my_model"."id" = "t1"."my_model_id"
WHERE "t1"."other_models_count" > $1 OR ("t1"."other_models_count" = $2 AND "my_model"."id" > $3)
ORDER BY "t1"."other_models_count" NULLS FIRST, "my_model"."id" LIMIT 6

Steps to Reproduce 🕹

Entc options

• entgql
• namedges
• privacy
• sql/execquery

1. Define a schema with at least two models with an edge between them and set the edge to be an order field
2. Run a query that orders by the edge and uses a cursor offset.

Your Environment 🌎

Tech	Version
Go	1.22.2
Ent	0.13.1
Database	PostgreSQL
Driver	https://github.com/lib/pq

[henrywoody]

Related: #3747

Covers the same issue but without ordering by count (just ordering by edge field).

[henrywoody]

@a8m I believe I can fix this one with a bit of guidance.

I've found that the issue is because the joined table (t1) is defined by the order logic and the pagination logic has no access to the table. In the generated code, the <Model>OrderField type's toTerm function defines the order and joins the table (wrapping sqlgraph.OrderByNeighborsCount or sqlgraph.OrderByNeighborTerms). Then in the pagination logic, on this line:

ands = append(ands, sql.EQ(s.C(opts.Fields[j]), values[j]))

where the order table t1 should be used instead of s for accessing the column (s.C).

I believe the fix will require a somewhat substantial refactor to move the table join logic out of the ordering logic into a separate function so that the joined table (*sql.Table/*sql.Selector) can be returned and passed to both the order and pagination logic. I want to check with you before proceeding to check that this is the right course and to make sure that ordering without GQL pagination will work. What do you think? Any advice?