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!