Rails 3.1 将对象添加到 find_by_sql 返回的数组中/无法在 erb 文件中显示

Question

我正在将 php 应用程序移植到 Rails，因此我有一组 sql 语句，我将它们转换为 find_by_sql 的。我看到它返回了我调用它的类型的对象集合。然后我想做的是迭代这个集合（大概是一个数组）并添加特定对象的实例，如下所示：

#class is GlobalList
#sql is simplified - really joining across 3 tables
def self.common_items user_ids
    items=find_by_sql(["select gl.global_id, count(gl.global_id) as global_count from main_table gl group by global_id"])

    #each of these items is a GlobalList and want to add a location to the Array            
    items.each_with_index do |value,index|
        tmp_item=Location.find_by_global_id(value['global_id'])
        #not sure if this is possible
        items[index]['location']=tmp_item
    end
    return items
end

控制器

#controller  
@common_items=GlobalList.common_items user_ids

视图

#view code - the third line doesn't work
<% @common_items.each_with_index do |value,key| %>
        <%=debug(value.location) %> <!-- works -->
        global_id:<%=value.location.global_id %> <!-- ### doesn't work but this is an attribute of this object-->
<% end %>

所以我有 3 个问题：
1. items 是数组吗？它说这是通过调用 .class 但不确定
2. 我能够添加这些 GlobalList 项目的位置。但是在视图代码中，我无法访问位置的属性。我如何访问它？
3.我知道这很难看——有没有更好的模式来实现这个？

Answer 1

我将从 sql 查询中的位置获取您需要的任何数据，这样您就可以避免 n +1问题

   @items=find_by_sql(["select locations.foo       as location_foo, 
                               gl.global_id        as global_id, 
                               count(gl.global_id) as global_count 
                        from main_table gl 
                        inner join locations on locations.global_id = gl.global_id 
                        group by global_id"])

然后在视图中：

<% @items.each do |gl| %>
        <%= gl.location_foo %> <-- select any fields you want in controller -->
        <%= gl.global_count %>
        <%= gl.global_id %>
<% end %>

如果你想保持它接近原样，我会：

def self.common_items user_ids
    items=find_by_sql(["select gl.global_id, count(gl.global_id) as global_count 
                        from main_table gl group by global_id"])

    items.map do |value|
        [value, Location.find_by_global_id(value.global_id)]
    end
end

然后在视图中：

<% @common_items.each do |value,location| %>
        <%=debug(location) %> 
        global_id:<%=value.global_id %> 
<% end %>