From: Xavier Noria <fxn@...>
Date: 2008-08-28T00:24:43+09:00
Subject: Re: ActiveRecord Question -- making arrays or hashs out of database tables

On Wed, Aug 20, 2008 at 8:21 PM, Glenn <glenn_ritz@yahoo.com> wrote:

> I have a question about using ActiveRecord.  The way I understand ActiveRecord is that you can use it as a kind of bridge between Ruby and a relational database.  I believe that you can use it to create objects from tables and that the objects are equivalent to the rows of data in the tables.
> I want to be able to use it a bit differently than that.  I want to be able to create abjects that are arrays of the values in specific fields in a given table.  For example, given the following table:
> X    Y    Z
> 1   'a'   1.1
> 2   'b'   6.2
> 3   'c'   0.001
> I want to be able to create an array from the X field that is equal to [1, 2, 3], and an array from the Y field that is equal to ['a', 'b', 'c'], and so forth.
>
> Better yet, I'd like to be able to create an array of hashes, where each element of the array is equal to a hash of the fields in the table, like this:
> [{:x => 1, :y => 'a', :z => 1.1}, {:x => 2, :y => 'b', :z => 6.2}, {:x => 3, :y => 'c', :z => 0.001}]
> Can anyone tell me if this is doable in ActiveRecord, or in some other Ruby package, or with some stand-alone Ruby code?
> Thanks for your help,

I believe this is overkill for an ORM, you can just use the DBI
interface for example:

    require 'rubygems'
    require 'dbi'

    DBI.connect('dbi:SQLite3:data.db') do |dbh|
      x = dbh.select_all("select x from data")
      y = dbh.select_all("select y from data")
      z = dbh.select_all("select z from data")

      table = dbh.select_all("select * from data").map {|row| row.to_h}
    end

The example uses SQLite, but the same code may be used for many others
with a DBD driver, see:

   http://ruby-dbi.rubyforge.org/rdoc/index.html

Just changing the connection string 'dbi:SQLite3:data.db'.