##What is this?

#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.
#You can omit .select statement to make it select all columns.

#Known limitations:
  #Cant join already joined query on the right
  #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.


##Examples
>> User.select(:id, :login).filter{:login != "Inviz"}.join(Post.reverse_order(:id), :user_id).sql
=> "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"


>> User.select(:id).filter{:id > 1}.join(Post.select(:id).filter{:id > 1}, :user_id).or{:id < 1}.sql
=> "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)"







## Code
#This goes into dataset/sql.rb instead of old join_table method
#Dont forget the regexp



      PREFIXED_WHERE_REGEXP = /\(`/.freeze

      # Returns a joined dataset with the specified join type and condition.
      def join_table(type, table, expr)
        unless expr.is_a?(Hash)
          expr = {expr => :id}
        end
        clone = {}
        if table.is_a?(Sequel::Dataset)
          ds = table.opts
          raise "Joining already joined tables is not supported" if ds[:join]

          unless @opts[:prefixed]
            select = @opts[:select] || @opts[:models][nil].columns
            clone[:select] = select.map {|c| :"#{@opts[:from]}__#{c}___#{@opts[:from]}_#{c}"}
          end

          select = (ds[:select] || ds[:models][nil].columns)
          clone[:select] << (select.map{|c| :"#{ds[:from]}__#{c}___#{ds[:from]}_#{c}"})


          if ds[:where]
            if !@opts[:prefixed] && (clone[:where] = @opts[:where])
               clone[:where].gsub!(PREFIXED_WHERE_REGEXP, "(#{@opts[:from]}.`")
            end
            if where = ds[:where]
              where.gsub!(PREFIXED_WHERE_REGEXP, "(#{ds[:from]}.`")
              clone[:where] = clone[:where] ? "#{clone[:where]} AND #{where}" : where
            end
          end

          order = []
          if !@opts[:prefixed] && @opts[:order]
            order << (@opts[:order].map do |o|
              if o.is_a?Symbol
                :"#{@opts[:from]}__#{o}"
              else
                  :"#{@opts[:from]}__#{o.l}".send(o.op)
              end
            end)
          end
          if ds[:order]
            order << (ds[:order].map do |o|
              if o.is_a?Symbol
                :"#{ds[:from]}__#{o}"
              else
                  :"#{ds[:from]}__#{o.l}".send(o.op)
              end
            end)
          end
          clone[:order] = order unless order.blank?

          @opts[:prefixed] = true

          for opt in [:union, :union_all, :sql]
            if ds[opt] || @opts[opt]
              clone[opt] = nil
            end
          end

          table = ds[:from]
        end

        clause = join_expr(type, table, expr)
        clone[:join] = @opts[:join] ? @opts[:join] + clause : clause
        clone[:last_joined_table] = table

        clone_merge(clone)
      end