# Custom query: i.e. WITH RECURSIVE

**URL:** <https://discourse.rom-rb.org/t/custom-query-i-e-with-recursive/298>\
**Category:** Issues\
**Created:** [January 7, 2019, 8:13pm UTC](https://discourse.rom-rb.org/t/custom-query-i-e-with-recursive/298 "2019-01-07T20:13:01Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mellowcoder](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@mellowcoder](https://discourse.rom-rb.org/u/mellowcoder)\
**Post date:** [January 7, 2019, 8:13pm UTC](https://discourse.rom-rb.org/t/custom-query-i-e-with-recursive/298/1 "2019-01-07T20:13:01Z")

</div>

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.

```ruby
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

```

```ruby
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

```

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [January 9, 2019, 2:17pm UTC](https://discourse.rom-rb.org/t/custom-query-i-e-with-recursive/298/2 "2019-01-09T14:17:19Z")

</div>

> [@mellowcoder](#):
>
> I’ve been having so much fun with ROM lately. So I just wanna say thank you all for all the work done.

👋 I’m very happy to know that 🙂

> [@mellowcoder](#):
>
> 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`

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:

```ruby
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.