[SOLVED] Preserving order of the columns

[AlekseyFedorchenko]

Hi Rodrigo!

...and thank you for the great Redmine plugin that solved my demand for truly custom reporting.

There was the only issue that frustrated me: columns were selected in a weird order that was totally different to one defined in SQL. While I'm a total novice to Redmine and Ruby -- it is a 3rd day on board -- I tried to debug your code and found a problem: ActiveRecord::Base.connection.select_all returns with a hashed columns instead of plain columns array that might be used in this case. I tweaked your code with ActiveRecord::Base.connection.execute and it looks like follows:

  Redmine::WikiFormatting::Macros.register do
    desc "Run SQL query"
    macro :sql do |obj, args|

        _sentence = args.join(",")
        _sentence = _sentence.gsub("\\(", "(")
        _sentence = _sentence.gsub("\\)", ")")

        result = ActiveRecord::Base.connection.execute(_sentence)
        unless result.nil?
          unless result.num_rows() == 0
            column_names = []
            for columns in result.fetch_fields.each do
              column_names += columns.name.to_a
            end

            _thead = '<tr>'
            column_names.each do |column_name|
              _thead << '<th>' + column_name.to_s + '</th>'
            end
            _thead << '</tr>'

            _tbody = ''
            result.each_hash do |record|
              unless record.nil?
                _tbody << '<tr>'
                column_names.each do |column_name|
                  _tbody << '<td>' + record[column_name].to_s + '</td>'
                end
                _tbody << '</tr>'
              end 
            end

            _table = '<table>' << _thead << _tbody << '</table>' 

            result.free
            return _table
          else
            result.free
            return '0 records returned'
          end
        else
          result.free
          return 'No result returned'
        end
    end 
  end

It works for me and hopefully you will accept this patch to your plugin :)

[rorx]

Plugin updated!