如何使用Rails索引和查询联接表? [英] How should I use Rails to index and query a join table?

问题描述

我有一个ruby on Rails 4应用程序，使用devise并具有User模型和Deal模型.

I have a ruby on Rails 4 app, using devise and with a User model and a Deal model.

我正在为用户和交易之间的has_many/has_many关系创建 user_deals 表.

I am creating a user_deals table for has_many/has_many relationship between User and Deal.

这里是迁移

class CreateUserDeals < ActiveRecord::Migration

  def change
    create_table :user_deals do |t|
        t.belongs_to :user
      t.belongs_to :deal
      t.integer         :nb_views

      t.timestamps
    end
  end
end

当用户加载交易(例如Deal id = 4)时，我使用一种称为 show

When a user load a Deal (for example Deal id= 4), I use a method called show

controllers/deal.rb
#for the view of the Deal page
def show
end

在交易ID = 4"页面的视图中，我需要在用户当前所在的交易"页面内显示Devise的 current_user 的视图的nb.

In the view of this Deal id=4 page, I need to display the nb of views of the Devise's current_user inside the Deal page the user is currently on.

deal/show.html

here is the nb of views of user: <% current_user.#{deal_id}.nb_views%>

让我们说我有1000万条 user_deals 行，我想知道是否应该使用索引

Lets' say I have 10M+ user_deals lines, I wanted to know if I should use an index

add_index :user_deals, :user_id
add_index :user_deals, :deal_id

也许

add_index(:deals, [:user_id, deal_id])

实际上在其他情况下我会说是"，但是在这里我不知道Rails在幕后如何工作.感觉好像Rails知道在不需要我加快处理速度的情况下该怎么做，...好像当Rails加载此视图时就没有SQL查询(例如'在视图的nb中找到 user_id= x 和 deal_id = Y ')....因为我仅用于登录的 current_user (通过devise的current_user )，对于 deal_id ，Rails知道这一点，就像我们在交易的整个页面(显示页面)上一样，所以我只是将其作为参数传递.

Indeed in other situations I would have said Yes, but here I don't know how Rails works behind the scenes. It feels as if Rails is aware of what to do without me needing to speed up the process,...as if when Rails loads this view that there is no SQL query (such as 'find the nb of views WHERe user_id= x and deal_id= Y')....because I'm using just for the current_user who is logged-in (via devise's current_user) and for deal_id Rails knows it as we are on the very page of this deal (show page) so I just pass it as a parameter.

那我是否需要一个索引来加速它?

So do I need an index to speed it up or not?

推荐答案

您关于索引的问题是一个很好的问题.Rails 确实会生成SQL *以发挥其魔力，因此适用了优化数据库的常规规则.

Your question on indexes is a good one. Rails does generate SQL* to do its magic so the normal rules for optimising databases apply.

设计的魔力仅扩展到current_user.它通过SQL查询来获取其详细信息，这是有效的，因为默认情况下，由devise创建的用户表具有有用的索引.但这不是您需要的索引.

The magic of devise only extends to the current_user. It fetches their details with a SQL query which is efficient because the user table created by devise has helpful indexes on it by default. But these aren't the indexes you'll need.

首先，有一种更整洁的惯用方式来做您想要做的事

Firstly, there's a neater more idiomatic way to do what you're after

class CreateUserDeals < ActiveRecord::Migration
  def change
    create_join_table :users, :deals do |t|
      t.integer :nb_views
      t.index [:user_id, :deal_id]
      t.index [:deal_id, :user_id]
      t.timestamps
    end
  end
end

您会注意到迁移包括两个索引.如果您从不希望为给定交易创建所有用户的视图，则不需要这些索引中的第二个.但是，正如@chiptuned所说，索引每个外键几乎总是正确的调用.整数上的索引花费很少的写入资源，但是在读取上却节省了很多钱.这是一个非常低成本的默认防御位置.

You'll notice that migration included two indexes. If you never expect to create a view of all users for a given deal then you won't need the second of those indexes. However, as @chiptuned says indexing each foreign key is nearly always the right call. An index on an integer costs few write resources but pays out big savings on read. It's a very low cost default defensive position to take.

您将拥有更好的时间，并且如果将数据获取逻辑放入控制器中，事情也会变得更加清晰.另外，您正在显示交易，因此将其设置为获取数据的中心而不是 current_user 会很合适.

You'll have a better time and things will feel clearer if you put your data fetching logic in the controller. Also, you're showing a deal so it will feel right to make that rather than current_user the centre of your data fetch.

实际上，您可以在不使用 through 关联的情况下执行此查询，因为您可以在不触摸用户表的情况下进行查询.(不过，在其他情况下，您可能希望通过 through 关联.)只需 has_many:user_deals 即可完成这项工作.

You can actually do this query without using the through association because you can do it without touching the users table. (You'll likely want that through association for other circumstances though.) Just has_many :user_deals will do the job for this.

为充分利用数据库引擎并在一个查询中执行此操作，您的控制器应如下所示:

To best take advantage of the database engine and do this in one query your controller can look like this:

def show
  @deal = Deal.includes(:user_deals)
              .joins(:user_deals)
              .where("user_deals.user_id = ?", current_user.id)
              .find(params["deal_id"])
end

然后在您看来...

I can get info about the deal: <%= @deal.description %>

由于有了 includes ，我可以在没有单独的SQL查询的情况下获得用户nb_views:<％= @ deal.user_deals.nb_views％>

And thanks to the includes I can get user nb_views without a separate SQL query: <%= @deal.user_deals.nb_views %>

*如果要查看神奇地生成了什么SQL rail，只需将 .to_sql 放在最后.例如 sql_string = current_user.deals.to_sql 或 @ deal.to_sql

* If you want to see what SQL rails is magically generating just put .to_sql on the end. e.g. sql_string = current_user.deals.to_sql or @deal.to_sql

这篇关于如何使用Rails索引和查询联接表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！