daily pastebin goal
86%
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
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
 
Top