mysql - 在 Ruby 的另一个循环中迭代 MySql 查询结果

Question

是否有任何解决方案可以使用 ruby​​ 在另一个循环中迭代一个 MySql 查询 结果。

使用 Ruby mysql Gem

在我的代码中fetch_row将不接受任何参数。所以我的代码将返回错误。

connection = Mysql.new(@db_host, @db_user, @db_pass, @db_name)
total_make = connection.query("Select make COUNT(cartype) AS cnt FROM vehicles group by car order by car;")
result = connection.query("Select make, cartype, COUNT(cartype) AS cnt FROM vehicles where cartype='suv' group by car order by car;")
suv = 0
data = ""
i = 0
result.each do |row|
    suv += row[1].to_i
    total_make.fetch_row(i)
    data +=  row[0] + "\n"
    data += "Total Cars: " + total_make[1] + "\n"
    data += "Total SUV : " + suv.to_s + "\n"
    data += "-----------------------\n"
    data = ""
    i+=1
end

puts data

MySql 查询:

Select make, cartype, COUNT(cartype) AS cnt FROM vehicles where cartype='suv' group by car order by car;

+-----------+-----------+-----+
|    make   |  cartype  | cnt |
+-----------+-----------+-----+
|    Ford   |    suv    |   2 |
|   Toyota  |    suv    |   3 |
+-----------+-----------+-----+

Select make COUNT(cartype) AS cnt FROM vehicles group by car order by car;

+-----------+-----+
|    make   | cnt |
+-----------+-----+
|    Ford   |   4 |
|   Toyota  |   5 |
+-----------+-----+

预期结果:

尝试按品牌对汽车进行分组，并列出汽车总数、SUV 总数。

Totota
Total Cars: 5
Total SUV : 3
-----------------------
Ford
Total Cars: 4
Total SUV : 2
-----------------------

Answer 1

我不是 100% 清楚你到底想要什么，或者如何充分利用 mysql gem，但这就是我产生你预期结果的方式Rails 方式。

#in the controller
@cars = Cars.all


#in the view
<% @cars.group_by{|a| a[:make]}.each do |key, group| %>
  <%= key %><br>
  Total Cars: <%= group.size %><br>
  Total SUV: <%= group.where(:cartype => "suv").size %><br>
  ------------------
<% end %>