开发者

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 = Activ开发者_StackOverfloweRecord::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.)


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜