# ROM SQL CTE tables

**URL:** <https://discourse.rom-rb.org/t/rom-sql-cte-tables/433>\
**Category:** Usage Stories\
**Created:** [February 18, 2021, 3:16pm UTC](https://discourse.rom-rb.org/t/rom-sql-cte-tables/433 "2021-02-18T15:16:12Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![alex-lairan](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/alex-lairan/32/206_2.png) [@alex-lairan](https://discourse.rom-rb.org/u/alex-lairan)\
**Post date:** [February 18, 2021, 3:16pm UTC](https://discourse.rom-rb.org/t/rom-sql-cte-tables/433/1 "2021-02-18T15:16:12Z")

</div>

Hi.

It is possible to do CTE table query with ROM SQL or we have to handle it at the Sequel level?

I didn’t find anything on the source code of the gem so I am asking.

And if no, what would be the steps to implement it?  
Is on the fly relations possible?

Example of what I want to build :

```auto
WITH RECURSIVE hierarchical_cte (level_id, parent_id, level) AS 
(
SELECT hierarchical.level_id, hierarchical.parent_id, hierarchical.level 
FROM hierarchical 
WHERE hierarchical.level_id = '1_1_2'

UNION ALL

SELECT hierarchical.level_id, hierarchical.parent_id, hierarchical.level 
FROM hierarchical_cte 
JOIN hierarchical ON hierarchical_cte.parent_id = hierarchical.level_id
) SELECT * FROM hierarchical_cte;

```

---

<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:** [February 22, 2021, 6:34am UTC](https://discourse.rom-rb.org/t/rom-sql-cte-tables/433/2 "2021-02-22T06:34:33Z")

</div>

There’s no support for `with recursive` syntax yet, so I reckon dropping down to Sequel is your option or using a raw string query.

---

<div class="post-metadata">

**Author:** ![radar](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/radar/32/227_2.png) [@radar](https://discourse.rom-rb.org/u/radar)\
**Post date:** [March 16, 2021, 4:11am UTC](https://discourse.rom-rb.org/t/rom-sql-cte-tables/433/3 "2021-03-16T04:11:25Z")

</div>

I had a need to do the same thing today. Here’s the one I came up with:

```auto
class TicketsRelation < ROM::Relation[:sql]
  gateway :default

  schema(:tickets, infer: true)

  def contributors(project_ids)
    contribution_groups = select { [integer::count(id).as(:count), user_id, project_id] }
      .order(nil)
      .where(project_id: project_ids)
      .group_and_count(:project_id, :user_id)
      .dataset

    rankings = dataset.db[:contribution_groups].select do |cg|
      [
        cg.project_id,
        cg.user_id,
        cg.count,
        cg.rank.function.over(partition: cg.project_id, order: Sequel.desc(cg.count))
      ]
    end

    rankings = dataset.db[:rankings]
      .with(:contribution_groups, contribution_groups)
      .with(:rankings, rankings)
      .where { rank <= 3 }
      .to_a

    rankings.group_by { |ranking| ranking[:project_id] }
  end
end

```

The method spends most of the time building up the different parts of the query then finally executes it.

It’s relevant to me to get this information grouped by project\_id (so I can then show the top contributors to each project), so I then group the result at the end.

---

<div class="post-metadata">

**Author:** ![radar](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/radar/32/227_2.png) [@radar](https://discourse.rom-rb.org/u/radar)\
**Post date:** [March 16, 2021, 4:34am UTC](https://discourse.rom-rb.org/t/rom-sql-cte-tables/433/4 "2021-03-16T04:34:22Z")

</div>

I think this is what you would need:

```auto
union_1 = dataset.db[:hierarchical].select(:level_id, :parent_id, :level).where(level_id: '1_1_2')
union_2 = dataset.db[:hierarchical_cte].join(:hierarchical, level_id: :parent_id)
union = union_1.union(union_2, all: true, from_self: false)
dataset.db[:hierarchical_cte].with(:hierarchical_cte, union, recursive: true, args: [:level_id, :parent_id, :level]).sql

```

---

<div class="post-metadata">

**Author:** ![alex-lairan](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/alex-lairan/32/206_2.png) [@alex-lairan](https://discourse.rom-rb.org/u/alex-lairan)\
**Post date:** [April 14, 2021, 1:35pm UTC](https://discourse.rom-rb.org/t/rom-sql-cte-tables/433/5 "2021-04-14T13:35:15Z")

</div>

Perfect, thanks for your help!

---

<div class="post-metadata">

**Author:** ![codeslinger](https://avatars.discourse-cdn.com/v4/letter/c/e9a140/32.png) [@codeslinger](https://discourse.rom-rb.org/u/codeslinger)\
**Post date:** [October 1, 2022, 12:56pm UTC](https://discourse.rom-rb.org/t/rom-sql-cte-tables/433/6 "2022-10-01T12:56:20Z")

</div>

I know this is an old post but this may help future readers. Avoid using self referencing tables (parent\_id) for hierarchies. I attempted this design using ROM and it turns into a hairy mess! Sometimes this could be considered an anti-pattern. Try something called a _ **Closure Table** _. This was easy to implement with ROM and solves a lot of other issues like having N number of nested joins for each level.

Once setup correctly queries look like this…

```auto
descendant_comments = comments_rel.join(:descendants).where(ancestor_id: 4).to_a # 4, 5, 6, 7
ancestor_comments = comments_rel.join(:ancestors).where(descendant_id: 6).to_a # 1, 4, 6

```

---

<div class="post-metadata">

**Author:** ![pboling](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/pboling/32/299_2.png) [@pboling](https://discourse.rom-rb.org/u/pboling)\
**Post date:** [June 22, 2023, 9:32am UTC](https://discourse.rom-rb.org/t/rom-sql-cte-tables/433/7 "2023-06-22T09:32:35Z")

</div>

There is a [closure\_tree](https://github.com/ClosureTree/closure_tree) gem for active record which implements the closure table pattern. Is there anything similar for Sequel?