daily pastebin goal
57%
SHARE
TWEET

Untitled

a guest Apr 16th, 2018 50 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ##What is this?
  2.  
  3. #This is a join automator. It makes .join methods family take another query as a first argument. It aliases all columns with tablename prefix and puts prefix to all WHERE's and ORDER BY's.
  4. #You can omit .select statement to make it select all columns.
  5.  
  6. #Known limitations:
  7.   #Cant join already joined query on the right
  8.   #You MUST not use column aliasing, otherwise it'll break stuff. This can be checked, of course, but i dont think it's a very good idea.
  9.  
  10.  
  11. ##Examples
  12. >> User.select(:id, :login).filter{:login != "Inviz"}.join(Post.reverse_order(:id), :user_id).sql
  13. => "SELECT users.`id` AS `users_id`, users.`login` AS `users_login`, users.`topic_id` AS `users_topic_id`, users.`forum_id` AS `users_forum_id`, users.`user_id` AS `users_user_id`, users.`body` AS `users_body`, users.`body_html` AS `users_body_html`, users.`created_at` AS `users_created_at`, users.`updated_at` AS `users_updated_at` FROM users INNER JOIN posts ON (posts.`user_id` = users.`id`) WHERE (NOT (`login` = 'Inviz')) ORDER BY posts.`id` DESC"
  14.  
  15.  
  16. >> User.select(:id).filter{:id > 1}.join(Post.select(:id).filter{:id > 1}, :user_id).or{:id < 1}.sql
  17. => "SELECT users.`id` AS `users_id`, posts.`id` AS `posts_id` FROM users INNER JOIN posts ON (posts.`user_id` = users.`id`) WHERE ((users.`id` > 1) AND (posts.`id` > 1)) OR (`id` < 1)"
  18.  
  19.  
  20.  
  21.  
  22.  
  23.  
  24.  
  25. ## Code
  26. #This goes into dataset/sql.rb instead of old join_table method
  27. #Dont forget the regexp
  28.  
  29.  
  30.  
  31.       PREFIXED_WHERE_REGEXP = /\(`/.freeze
  32.  
  33.       # Returns a joined dataset with the specified join type and condition.
  34.       def join_table(type, table, expr)
  35.         unless expr.is_a?(Hash)
  36.           expr = {expr => :id}
  37.         end
  38.         clone = {}
  39.         if table.is_a?(Sequel::Dataset)
  40.           ds = table.opts
  41.           raise "Joining already joined tables is not supported" if ds[:join]
  42.          
  43.           unless @opts[:prefixed]
  44.             select = @opts[:select] || @opts[:models][nil].columns
  45.             clone[:select] = select.map {|c| :"#{@opts[:from]}__#{c}___#{@opts[:from]}_#{c}"}
  46.           end
  47.          
  48.           select = (ds[:select] || ds[:models][nil].columns)
  49.           clone[:select] << (select.map{|c| :"#{ds[:from]}__#{c}___#{ds[:from]}_#{c}"})
  50.        
  51.                  
  52.           if ds[:where]
  53.             if !@opts[:prefixed] && (clone[:where] = @opts[:where])
  54.                clone[:where].gsub!(PREFIXED_WHERE_REGEXP, "(#{@opts[:from]}.`")
  55.             end
  56.             if where = ds[:where]
  57.               where.gsub!(PREFIXED_WHERE_REGEXP, "(#{ds[:from]}.`")
  58.               clone[:where] = clone[:where] ? "#{clone[:where]} AND #{where}" : where
  59.             end
  60.           end
  61.          
  62.           order = []
  63.           if !@opts[:prefixed] && @opts[:order]
  64.             order << (@opts[:order].map do |o|
  65.               if o.is_a?Symbol
  66.                 :"#{@opts[:from]}__#{o}"
  67.               else
  68.                   :"#{@opts[:from]}__#{o.l}".send(o.op)
  69.               end
  70.             end)
  71.           end
  72.           if ds[:order]
  73.             order << (ds[:order].map do |o|
  74.               if o.is_a?Symbol
  75.                 :"#{ds[:from]}__#{o}"
  76.               else
  77.                   :"#{ds[:from]}__#{o.l}".send(o.op)
  78.               end
  79.             end)
  80.           end
  81.           clone[:order] = order unless order.blank?
  82.          
  83.           @opts[:prefixed] = true
  84.                    
  85.           for opt in [:union, :union_all, :sql]
  86.             if ds[opt] || @opts[opt]
  87.               clone[opt] = nil
  88.             end
  89.           end
  90.          
  91.           table = ds[:from]
  92.         end
  93.        
  94.         clause = join_expr(type, table, expr)
  95.         clone[:join] = @opts[:join] ? @opts[:join] + clause : clause
  96.         clone[:last_joined_table] = table
  97.        
  98.         clone_merge(clone)
  99.       end
RAW Paste Data
Top