Feature request: Ability to refer to joined tables columns in sorting keys

We are dealing with a situation where we want to use stable cursors on a joined SQL query. A simplified case looks like this:

SELECT * 
FROM things 
  LEFT JOIN publish_dates 
    ON things.id = publish_dates.thing_id 
  ORDER BY publish_dates.timestamp

Right now this doesn't seems possible with the current implementation.

It would be great if we could do this:

GraphQL::Connections::Stable.new(
  Things.joins(:publish_dates).all, 
  keys: [:"publish_dates.timestamp"]
)

Right now we built a POC locally that work just for our case, but I think it could be useful for the project as a whole.

Hi! Yes indeed, sounds very useful, could you please make a PR with your implementation? 🙂

Hi, as I mentioned, I can't really provide a PR at this time, as we have just solved it for our specific use case, but I can past you the monkey patched code :)

# frozen_string_literal: true

module GraphQL
  module Connections
    module Keyset
      # Implements keyset pagination by two fields with asc order
      class Asc < ::GraphQL::Connections::Keyset::Base
        def has_previous_page
          if last
            nodes.any? &&
              items.where(_field.eq(nodes.first[field_key]))
                .where(arel_table[primary_key].lt(nodes.first[primary_key]))
                .or(items.where(_field.lt(nodes.first[field_key])))
                .exists?
          elsif after
            items
              .where(_field.lt(after_cursor_date))
              .or(
                items.where(_field.eq(after_cursor_date))
                  .where(arel_table[primary_key].lt(after_cursor_primary_key))
              ).exists?
          else
            false
          end
        end

        def has_next_page
          if first
            nodes.any? &&
              items.where(_field.eq(nodes.last[field_key]))
                .where(arel_table[primary_key].gt(nodes.last[primary_key]))
                .or(items.where(_field.gt(nodes.last[field_key])))
                .exists?
          elsif before
            items
              .where(_field.gt(before_cursor_date))
              .or(
                items.where(_field.eq(before_cursor_date))
                  .where(arel_table[primary_key].gt(before_cursor_primary_key))
              ).exists?
          else
            false
          end
        end

        private

        # standard:disable Metrics/AbcSize, Metrics/MethodLength
        def limited_relation
          scope = sliced_relation
          nodes = []

          if first
            nodes |= scope
              .reorder(_field.asc, arel_table[primary_key].asc)
              .limit(first).to_a
          end

          if last
            nodes |= scope
              .reorder(_field.desc, arel_table[primary_key].desc)
              .limit(last).to_a.reverse!
          end

          nodes
        end

        def sliced_relation_after(relation)
          relation
            .where(_field.eq(after_cursor_date))
            .where(arel_table[primary_key].gt(after_cursor_primary_key))
            .or(relation.where(_field.gt(after_cursor_date)))
        end

        def sliced_relation_before(relation)
          relation
            .where(_field.eq(before_cursor_date))
            .where(arel_table[primary_key].lt(before_cursor_primary_key))
            .or(relation.where(_field.lt(before_cursor_date)))
        end

        def _field
          if field_key.to_s.include?(".")
            table, field = field_key.to_s.split('.')
            Arel::Table.new(table.to_sym)[field.to_sym]
          else
            arel_table[field_key]
          end
        end
      end

      # Implements keyset pagination by two fields with desc order
      class Desc < ::GraphQL::Connections::Keyset::Base
        def has_previous_page
          if last
            nodes.any? &&
              items.where(_field.eq(nodes.first[field_key]))
                .where(arel_table[primary_key].gt(nodes.first[primary_key]))
                .or(items.where(_field.gt(nodes.first[field_key])))
                .exists?
          elsif after
            items
              .where(_field.gt(after_cursor_date))
              .or(
                items.where(_field.eq(after_cursor_date))
                  .where(arel_table[primary_key].gt(after_cursor_primary_key))
              ).exists?
          else
            false
          end
        end

        def has_next_page
          if first
            nodes.any? &&
              items.where(_field.eq(nodes.last[field_key]))
                .where(arel_table[primary_key].lt(nodes.last[primary_key]))
                .or(items.where(_field.lt(nodes.last[field_key])))
                .exists?
          elsif before
            items
              .where(_field.lt(before_cursor_date))
              .or(
                items.where(_field.eq(before_cursor_date))
                  .where(arel_table[primary_key].lt(before_cursor_primary_key))
              ).exists?
          else
            false
          end
        end

        # standard:disable Metrics/AbcSize, Metrics/MethodLength
        def cursor_for(item)
          cursor = [item[field_key], item[primary_key]].map { |value| serialize(value) }.join(@separator)
          cursor = encode(cursor) if opaque_cursor
          cursor
        end

        private

        def limited_relation
          scope = sliced_relation
          nodes = []

          if first
            nodes |= scope
              .reorder(_field.desc, arel_table[primary_key].desc)
              .limit(first)
              .tap { |a|
                puts a.to_sql
              }
              .to_a
          end

          if last
            nodes |= scope
              .reorder(_field.asc, arel_table[primary_key].asc)
              .limit(last)
              .to_a.reverse!
          end

          nodes
        end

        def sliced_relation_after(relation)
          relation
            .where(_field.eq(after_cursor_date))
            .where(arel_table[primary_key].lt(after_cursor_primary_key))
            .or(relation.where(_field.lt(after_cursor_date)))
        end

        def sliced_relation_before(relation)
          relation
            .where(_field.eq(before_cursor_date))
            .where(arel_table[primary_key].gt(before_cursor_primary_key))
            .or(relation.where(_field.gt(before_cursor_date)))
        end

        def _field
          if field_key.to_s.include?(".")
            table, field = field_key.to_s.split('.')
            Arel::Table.new(table.to_sym)[field.to_sym]
          else
            arel_table[field_key]
          end
        end
      end
    end
  end
end

Issues with this code:

Still untested, this has not been used in production yet
It is possible for ActiveRecord to change the name of a table it joins, this code does not take that into account

We ran into this exact issue. We needed to sort and paginate by columns from joined tables (e.g., authors.name, categories.title) as well as computed columns (a CASE expression for priority ordering).

We found a workaround that doesn't require patching the gem. The idea is to materialize the aliased columns via a subquery, then tell ActiveRecord the columns exist with .select("table.*"):

def build_connection(posts, sort_by)
  # 1. Add JOINs and SELECT aliases as needed
  case sort_by[:field]
  when "author_name"
    posts = posts
      .left_joins(:author)
      .select("posts.*", "authors.name AS author_name")
  when "category_title"
    posts = posts
      .left_joins(:category)
      .select("posts.*", "categories.title AS category_title")
  when "priority_order"
    posts = posts.select("posts.*, (CASE
        WHEN posts.priority = 'high' THEN 1
        WHEN posts.priority = 'medium' THEN 2
        WHEN posts.priority = 'low' THEN 3
      END) as priority_order")
  end

  # 2. Wrap in subquery to materialize aliased columns as real columns
  result = Post.from("(#{posts.to_sql}) as posts")

  # 3. This is the key line — it makes Stable aware of the materialized columns
  result = result.select("posts.*")

  # 4. Now Stable can reference the aliased columns in cursor WHERE clauses
  GraphQL::Connections::Stable.new(
    result,
    desc: sort_by[:direction] == "DESC",
    keys: [sort_by[:field], :id]
  )
end

Why it works: The subquery materializes the aliased columns (e.g., author_name, category_title) as real columns in the result set. The .select("posts.*") tells ActiveRecord that all columns — including the materialized ones — are available on the relation. Stable can then reference them in its cursor WHERE clauses (WHERE (author_name, id) > (?, ?)) without error.

Without the .select("posts.*") line, Stable's introspection can't detect the materialized columns and hasNextPage breaks.

Navigation Menu

Feature request: Ability to refer to joined tables columns in sorting keys #16

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Feature request: Ability to refer to joined tables columns in sorting keys #16

Description

Activity

DmitryTsepelev commented on Jun 6, 2024

Bertg commented on Jun 6, 2024

mjacobus commented on Feb 12, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions