ROM SQL CTE tables

Usage Stories
1

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 :

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;
2

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.

3

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

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.

4

I think this is what you would need:

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
1 Like
5

Perfect, thanks for your help!

6

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…

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
1 Like
7

There is a closure_tree gem for active record which implements the closure table pattern. Is there anything similar for Sequel?

1 Like