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

[Bertg]

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.

[DmitryTsepelev]

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

[Bertg]

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