Custom query: i.e. WITH RECURSIVE

Issues
1

Hello, rom-rb community.

I’ve been having so much fun with ROM lately. So I just wanna say thank you all for all the work done.

I’ve came across this little issue.

I wanna use WITH RECURSIVE in a rom relation and I want it return ROM::Relation instead of Sequel::Postgres::Dataset so the IdentityRepo maps the result to ROM::Struct

So basically I want IdentityRepo#ancestors_by_pk to return the same type of data as IdentityRepo#listing

Is it even possible?

Consider the code below for reference. It’s very basic.

Thank you in advance.

module Persistence
  module Relations
    class Identities < ROM::Relation[:sql]
      schema :identities, infer: true

      def ancestors_by_pk(pk)
        db[:ancestors].with_recursive(:ancestors,
          db[:identities].select { [id, parent_id, Sequel[0].as(:relative_depth)] }.where(id: pk),
          db[:identities, :ancestors].select { [identities[:id], identities[:parent_id], ancestors[:relative_depth] - 1] }.where(Sequel.lit('identities.id = ancestors.parent_id'))
        ).select(Sequel.lit('*'), :relative_depth)
      end

      private

      def db
        dataset.db
      end
    end
  end
end

module SomeModule
  module Repositories
    class IdentityRepo < SomeModule::Repository[:identities]
      commands :create, update: :by_pk, delete: :by_pk

      # struct_namespace Entities

      def [](pk)
        identities.by_pk(pk).one!
      end

      def listing(limit: 10)
        identities.limit(limit)
      end

      def ancestors_by_pk(pk)
        identities.ancestors_by_pk(pk)
      end
    end
  end
end
2

:wave: I’m very happy to know that :slight_smile:

You can use Relation#new(dataset) to achieve this, the only caveat is that you are responsible for ensuring that the resulting schema is compatible with the current one (as in, the one which is defined in the relation object that you use).

You could do this:

module Persistence
  module Relations
    class Identities < ROM::Relation[:sql]
      schema :identities, infer: true

      def ancestors_by_pk(pk)
        ds = db[:ancestors].with_recursive(:ancestors,
          db[:identities].select { [id, parent_id, Sequel[0].as(:relative_depth)] }.where(id: pk),
          db[:identities, :ancestors].select { [identities[:id], identities[:parent_id], ancestors[:relative_depth] - 1] }.where(Sequel.lit('identities.id = ancestors.parent_id'))
        ).select(Sequel.lit('*'), :relative_depth)

        new(ds)
      end

      private

      def db
        dataset.db
      end
    end
  end
end

I hope this helps.