CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Ethan
Ethan

Reputation: 60089

Is this a good way to get arbitrary data from the DB

What is you want to craft a special query by hand not based to any particular model. A column here, a column there, maybe a count, sum, or some other caluculation. Here's a way I came up with...

>> results = ActiveRecord::Base.connection.execute(
    'SELECT location, count(*) AS count FROM contracts 
    GROUP BY location 
    ORDER BY location')
=> #<Mysql::Result:0x103197e50>
>> arr = []
=> []
>> results.each { |row| arr << [row.first, row.last] }
=> #<Mysql::Result:0x103197e50>
>> arr
=> [[nil, "189"], ["", "4"], ["Canceled", "12"], ["Cancelled", "4"], ["Closed", "1"], ["Contract - Asst", "4"], ["Contracts - Admin", "5"], ["Exec - Operations", "2"], ["Exec - Policy", "1"], ["Executive", "1"], ["Finance", "25"], ["Fully Executed", "3631"], ["General Counsel", "11"], ["On-Hold", "27"], ["Pending Distribution", "2"], ["Pending Signature", "40"], ["Per Stephen W.", "1"], ["Project Manager", "26"], ["Upcoming", "1"]]

But I'm like the worst programmer ever. How would a good programmer would to it?

In particular, is there a cleaner, simpler way to access the data in the Mysql::Result object? Why am I getting a Mysql::Result instead of some generalized, non-DB-specific data structure?

(If it matters, in this particular case, I'll use the 2d Array to populate an select menu, but it could be anything.)

Upvotes: 1

Views: 134

Answers (1)

Jits
Jits

Reputation: 9728

To run custom SQL queries via ActiveRecord, there are two general approaches:

1) ActiveRecord::Base#find_by_sql (docs)

Example (as taken from the docs):

Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]
> [#<Post:0x36bff9c @attributes={"first_name"=>"The Cheap Man Buys Twice"}>, ...]

2) ActiveRecord::Base.connection.select and ActiveRecord::Base.connection.select* (docs)

The benefit of using this is you get back regular Hash objects that are easy to parse.

IMPORTANT NOTE: make sure you sanitise your queries before using these methods otherwise you will be open to SQL injection attacks!! (Yes, that deserves TWO exclamation marks!!)

Edit: do take a look at ActiveRecord's querying support (powered by Arel), if you haven't already done so. There is a lot you can do with this.

Upvotes: 1

Related Questions