Ruby-on-rails Postgres查询

Question

我有一个用于酒店的ruby on rails应用程序，该应用程序有一个房间页面。酒店房间可以为它们打开不同种类的门票，在我的房间视图中，将显示房间号，并且我在我的helper中使用此查询来获取单个房间的门票数量

helper/room.rb
SELECT COUNT(*) FROM "tickets" WHERE "tickets"."room_id" = ? AND (status = ? OR status = ? )

room.slim

- @rooms.each do |floor_number, rooms|

  - if rooms.present?
    h1.separator
      - if (@current_view.eql? "room_type") || (params[:action].eql? "room_type")
        span= floor_number
      - else
        span= "Floor-#{floor_number}"

   .cards-container
      - rooms.each do |room|

= link_to get_show_page_url(room) do
            .col-md-3.room-card_room.new-floor-other{ style="background-color:#{card_background_color(room, @maintenance_type, @inspection_type)}" }
              .new-floor-inner
                  - if room.occupy_or_dnd? && (!@current_view.eql? "maintenance") && (!@current_view.eql? "inspection")
                    span
                      - if room.dnd?
                        = image_tag('dnd', class: "guest-occupy-img")
                      - else
                        = image_tag('man_to_white', class: "guest-occupy-img")

                  h4= room.location
                  - if (@current_view.eql? "maintenance") || (@current_view.eql? "inspection")
                    h6.room_status
                      | (
                      = last_maintained(room, @maintenance_type, @inspection_type)
                      | )

              span.room_hilton_honors= room.hilton_honors
              - count = room.open_tickets_count || 0
              - if count > 0 && (!@current_view.eql? "maintenance") && (!@current_view.eql? "inspection")
                span.circle = count

room_controller
before_action :get_room, only: [:show, :update]
def get_room
      @room = Room.find_by_id(params[:id])
      @featured_tickets = ImportTicket.where(request_type: "PREVENTIVE MAINTENANCE", featured:true)
    end

room.open_tickets_count是多次触发的查询，open_tickets在帮助器中定义，计数显示该房间的票证数量

这工作得很好，但就性能而言，它创建了更多的处理时间，因为这个单个查询在单个请求中被多次触发。假设有100个房间，该查询将被触发100次，以检查每个房间的票证总数

有没有一种方法可以让我在性能良好的情况下做到这一点？

Answer 1

我假设您有一个包含所有房间ids的变量。然后，您可以对此阵列使用where，对group使用count

Ticket.where(room_id: room_ids).group(:room_id).count

返回结果将如下所示：

{ 1 => 5, 2 => 3 }

也就是说，1号房有5张票，2号房有3张票。