How to get table as array of arrays where each sub array is a column (not a row)

Question

This answer shows how to return a rails table as an array of arrays row wise. How can this be achieved column wise, that is, where each of the sub arrays is a column/attribute from the table?

For reference, here's how to return the table as array of arrays row wise

my_array = Mymodel.all.map {|e| e.attributes.values}

Bran

Answer 1

This one works, calls SELECT n times (n = Mymodel.column_names.count)

my_array = Mymodel.column_names.map { |column| Mymodel.all.map(&column.to_sym) }

This one will call SELECT column_name instead of SELECT * n times

my_array = Mymodel.column_names.map { |column| Mymodel.pluck(column.to_sym) }

Better and quicker solution is this answer

Tyner

Answer 2

A natural and reasonably database-efficient approach would be to use Array#transpose on the array-of-arrays from the other answer:

by_column = Mymodel.all.map { |e| e.attributes.values }.transpose
# Just add this ---------------------------------------^^^^^^^^^^

Comparison with other approaches,

bm =  Benchmark.bm do |x| 
  x.report { my_array = User.column_names.map { |column| User.all.map(&column.to_sym) } }
  x.report { my_array = User.column_names.map { |column| User.pluck(column.to_sym) } }
  x.report { by_column = User.all.map { |e| e.attributes.values }.transpose }
end

In earlier two approaches provided by Yuriy Verbitskiy, first approach load model N numbers of times & second one will fire select query N number of times.

And provided third approach will retrieve data in single query which optimise performance in superior way as shown in following result,

[
    [0] #<Benchmark::Tms:0xd6601f4 @label="", @real=0.05709833199989589, @cstime=0.0, @cutime=0.0, @stime=0.0, @utime=0.040000000000000036, @total=0.040000000000000036>,
    [1] #<Benchmark::Tms:0xd58e5f0 @label="", @real=0.02451071499990576, @cstime=0.0, @cutime=0.0, @stime=0.0, @utime=0.019999999999999907, @total=0.019999999999999907>,
    [2] #<Benchmark::Tms:0xd4e5eb4 @label="", @real=0.004674662000070384, @cstime=0.0, @cutime=0.0, @stime=0.0, @utime=0.010000000000000009, @total=0.010000000000000009>
]

Utopianism

I was trying same with zip to work with single database query but found this one which nearly gave same effect! – Unthankful

@Unthankful Thanks for the benchmarks. One database hit and Array#transpose (which is implemented directly in C) should be pretty quick. – Utopianism

This is the quickest and readable way. I’ll edit my answer and upvote yours. Thanks! – Tyner

Answer 3

You could use pluck to get the column values and push it into your array. Something like...

your_array = []

Mymodel.column_names.each do |col|
  sub_arr = Mymodel.pluck(col.to_sym)
  your_array.push(sub_arr)
end

Mashie