Rails 4：多态关联模型的索引优化与关联查询问题咨询

Hey there! Let's tackle your two questions based on the polymorphic associations you've set up. First, let's recap the key relationships to align our context:

Core Association Setup #

• User ↔ Purchase: Polymorphic has_many (User has many purchases as purchasable)
• Company ↔ Purchase: Polymorphic has_many (Company has many purchases as purchasable)
• User & Company: No direct association
• Purchase ↔ Item: has_many (Purchase owns many items, Item belongs to a purchase)
• User ↔ Item: Indirect has_many :through => :purchases

Here's the properly formatted model code:

class User < ApplicationRecord
  has_many :purchases, as: :purchasable, dependent: :destroy
  has_many :items, through: :purchases
end

class Purchase < ApplicationRecord
  belongs_to :purchasable, polymorphic: true
  has_many :items, dependent: :destroy
end

class Item < ApplicationRecord
  belongs_to :purchase
end

Question 1: Indexes for User.first.items #

When you run User.first.items, the query chain goes:

1. Fetch the user
2. Find all purchases where purchasable_type = 'User' and purchasable_id = [user_id]
3. Join to items where purchase_id matches the found purchases

To optimize this, add these indexes:

• For purchases table: A composite index on the polymorphic fields, since we need to filter by both type and id:

  add_index :purchases, [:purchasable_type, :purchasable_id]
  

  This lets the database directly locate all purchases belonging to a specific user without scanning the entire table.
• For items table: An index on purchase_id to speed up the join from purchases to items:

  add_index :items, :purchase_id
  

If you frequently filter/sort items by fields like created_at, you can extend the items index to a composite one (e.g., [:purchase_id, :created_at]) to cover common query patterns entirely.

Question 2: Efficiently Get User's Most Recent Items by Category #

Your current code is inefficient because it loads full ActiveRecord objects into memory with collect(&:id) and performs in-memory intersections—this gets slow fast with large datasets. Let's fix this by pushing all logic to the database.

First: Fix the Typo #

Note your original code has a typo: :puchase_id should be :purchase_id.

Efficient Implementation Options #

Option 1: ActiveRecord (Clean, Rails-native)

This uses a subquery to find the latest item per category, then fetches those items directly:

class User
  def most_recent_items
    # Subquery to get the latest created_at per category for this user's items
    latest_per_category = Item.joins(:purchase)
                              .where(purchases: { purchasable: self })
                              .group(:category)
                              .select("category, MAX(created_at) AS latest_created_at")

    # Join back to items to get the full records matching the latest per category
    Item.joins(:purchase)
        .where(purchases: { purchasable: self })
        .joins("JOIN (#{latest_per_category.to_sql}) latest ON items.category = latest.category AND items.created_at = latest.latest_created_at")
        .order(created_at: :desc)
  end
end

If you trust that higher id = more recent (safe with standard auto-incrementing ids), you can simplify to:

class User
  def most_recent_items
    latest_item_ids = Item.joins(:purchase)
                          .where(purchases: { purchasable: self })
                          .group(:category)
                          .pluck("MAX(items.id)")

    Item.where(id: latest_item_ids).order(created_at: :desc)
  end
end

pluck is way faster than collect(&:id) because it returns only the ids directly from the database, no ActiveRecord objects loaded into memory.

Option 2: Raw SQL (For Maximum Control)

SELECT items.*
FROM items
JOIN purchases ON items.purchase_id = purchases.id
JOIN (
  SELECT category, MAX(created_at) AS latest_created_at
  FROM items
  JOIN purchases ON items.purchase_id = purchases.id
  WHERE purchases.purchasable_type = 'User' 
    AND purchases.purchasable_id = #{self.id}
  GROUP BY category
) latest_items ON items.category = latest_items.category 
               AND items.created_at = latest_items.latest_created_at
WHERE purchases.purchasable_type = 'User' 
  AND purchases.purchasable_id = #{self.id}
ORDER BY items.created_at DESC;

Index Optimization for This Query #

Add a composite index to items to speed up the category grouping and latest date lookup:

add_index :items, [:category, :created_at]

This lets the database quickly group items by category and find the most recent entry without scanning all rows.

内容的提问来源于stack exchange，提问作者rantingsonrails