Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- class LimitPerGroupQueryBuilder
- class << self
- def query_builder(query:, group_by:, order_by:, limit:)
- t_outer = Arel::Table.new('t_outer')
- window_function = window_function(table: query.arel_table, partition: group_by, order_by: order_by)
- row_number_function = row_number_function(over: window_function)
- inner_query = query
- .arel.dup
- .project(row_number_function)
- .as(t_outer.name)
- select_manager = Arel::SelectManager.new
- select_manager.project(Arel.star).from(inner_query).where(t_outer[:row_number].lteq(limit))
- end
- private
- def window_function(table:, partition:, order_by:)
- window = Arel::Nodes::Window.new
- window.partition(table[partition])
- window.order(query_order(table: table, order_by: order_by))
- window
- end
- def row_number_function(over:)
- Arel::Nodes::NamedFunction.new('row_number', []).over(over).as('row_number')
- end
- def query_order(table:, order_by:)
- if order_by.is_a?(Hash)
- column, order = order_by.first
- order == 'asc' ? Arel::Nodes::Ascending.new(table[column]) : Arel::Nodes::Descending.new(table[column])
- else
- table[order_by]
- end
- end
- end
- end
- # Showing the 3 most popular posts in those categories
- post_query = Post.where(category_id: [1,2,3])
- query = LimitPerGroupQueryBuilder.query_builder(query: post_query, group_by: 'category_id', order_by: {rating: 'desc'}, limit: 3)
- Post.find_by_sql(query)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement