How to get rows as Arrays (not Hashes) in Sequel ORM?

Question

In the Sequel ORM for Ruby, the Dataset class has an all method which produces an Array of row hashes: each row is a Hash with column names as keys.

For example, given a table T:

a  b   c
--------------
0  22  "Abe"
1  35  "Betty"
2  58  "Chris"

then:

ds = DB['select a, b, c from T']
ah = ds.all # Array of row Hashes

should produce:

[{"a":0,"b":22,"c":"Abe"},{"a":1,"b":35,"c":"Betty"},{"a":2,"b":58,"c":"Chris"}]

Is there a way built in to Sequel to instead produce an Array of row Arrays, where each row is an array of only the values in each row in the order specified in the query? Sort of how select_rows works in ActiveRecord? Something like this:

aa = ds.rows # Array of row Arrays

which would produce:

[[0,22,"Abe"],[1,35,"Betty"],[2,58,"Chris"]]

Note: the expression:

aa = ds.map { |h| h.values }

produces an array of arrays, but the order of values in the rows is NOT guaranteed to match the order requested in the original query. In this example, aa might look like:

[["Abe",0,22],["Betty",1,35],["Chris",2,58]]

Pyatt

Answer 1

If you want just an array of array of values...

DB['select * from T'].map { |h| h.values }

seems to work

UPDATE given the updated requirement of the column order matching the query order...

cols= [:a, :c, :b]
DB[:T].select{cols}.collect{ |h| cols.collect {|c| h[c]}}

not very pretty but guaranteed order is the same as the select order. There does not appear to be a builtin to do this. You could make a request for the feature.

Cherubini

This creates an array but unlike ActiveRecord select_rows, the order of the columns does not necessarily match the order requested in the underlying query. I will edit my question to indicate this requirement. – Pyatt

I think if you want to guarantee the order then you need to specify the columns you want to fetch in order using dataset#select{[columns,..]}, I'll update my answer with an example. – Cherubini

FYI, this will of course fail if you have duplicate column names in your result set: DB['SELECT 1 AS test, 2 AS test'] – Stone

Answer 2

Old versions of Sequel (pre 2.0) had the ability in some adapters to return arrays instead of hashes. But it caused numerous issues, nobody used it, and I didn't want to maintain it, so it was removed. If you really want arrays, you need to drop down to the connection level and use a connection specific method:

DB.synchronize do |conn|
  rows = conn.exec('SQL Here') # Hypothetical example code
end

The actual code you need will depend on the adapter you are using.

Lenalenard

@jwfeam: I second that. I can't wipe the smile off of my face ever since I've discovered it. Finally something that lets me write exactly the query I need without concatenate bits and pieces of SQL strings. – Prodrome

Answer 3

DB[:table].where().select_map(:id)

Ivett

Should be accepted answer. my_keys = [:a, :b, :c]; DB[:T].select_map(my_keys) – Vociferation

Answer 4

I haven't yet found a built-in method to return an array of row arrays where the values in the row arrays are ordered by the column order in the original query. The following function does* although I suspect an internal method could be more effecient:

def rows( ds )
  ret = []
  column_keys = ds.columns  # guaranteed to match query order?
  ds.all { |row_hash|
    row_array = []
    column_keys.map { |column_key| row_array << row_hash[column_key] }
    ret << row_array
  }
  ret
end

*This function depends on the order of the array returned by Dataset.columns. If this order is undefined, then this rows function isn't very useful.

Pyatt

Answer 5

have you tried this?

ds = DB['select a, b, c from T'].to_a

not sure it it works but give it a shot.

Gettysburg