ROM SQL CTE tables


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'


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;

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.

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] }
      .where(project_id: project_ids)
      .group_and_count(:project_id, :user_id)

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

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

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

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.

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

Perfect, thanks for your help!