SHARE
TWEET

Untitled

a guest Jun 24th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. class LimitPerGroupQueryBuilder
  2.   class << self
  3.     def query_builder(query:, group_by:, order_by:, limit:)
  4.       t_outer = Arel::Table.new('t_outer')
  5.       window_function = window_function(table: query.arel_table, partition: group_by, order_by: order_by)
  6.       row_number_function = row_number_function(over: window_function)
  7.  
  8.       inner_query = query
  9.         .arel.dup
  10.         .project(row_number_function)
  11.         .as(t_outer.name)
  12.  
  13.       select_manager = Arel::SelectManager.new
  14.       select_manager.project(Arel.star).from(inner_query).where(t_outer[:row_number].lteq(limit))
  15.     end
  16.  
  17.     private
  18.  
  19.     def window_function(table:, partition:, order_by:)
  20.       window = Arel::Nodes::Window.new
  21.       window.partition(table[partition])
  22.       window.order(query_order(table: table, order_by: order_by))
  23.       window
  24.     end
  25.  
  26.     def row_number_function(over:)
  27.       Arel::Nodes::NamedFunction.new('row_number', []).over(over).as('row_number')
  28.     end
  29.  
  30.     def query_order(table:, order_by:)
  31.       if order_by.is_a?(Hash)
  32.         column, order = order_by.first
  33.         order == 'asc' ? Arel::Nodes::Ascending.new(table[column]) : Arel::Nodes::Descending.new(table[column])
  34.       else
  35.         table[order_by]
  36.       end
  37.     end
  38.   end
  39. end
  40.  
  41. # Showing the 3 most popular posts in those categories
  42. post_query = Post.where(category_id: [1,2,3])
  43. query = LimitPerGroupQueryBuilder.query_builder(query: post_query, group_by: 'category_id', order_by: {rating: 'desc'}, limit: 3)
  44. Post.find_by_sql(query)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top