Ruby-on-rails Rails - 按连接表数据排序

Rails - Sort by join table data

提问by Steve Davis

I've got a RoR project in the works. Here are the applicable sections of my models.

我有一个 RoR 项目正在进行中。以下是我的模型的适用部分。

Home

家

has_many :communities, :through => :availabilities
has_many :availabilities, :order => "price ASC"

Community

社区

has_many :homes, :through => :availabilities
has_many :availabilities

Availability

可用性

belongs_to :home
belongs_to :community

The "availabilities" table in the database has the additional data column "price"

数据库中的“可用性”表具有附加数据列“价格”

So now I can call

所以现在我可以打电话

@home.availabilities.each do |a|
  a.community.name
  a.price

and get back the availabilities data ordered by price as I want. My question is this:

并根据需要取回按价格排序的可用性数据。我的问题是这样的：

Is there a way to automatically order Homes by avaliabilities.first.price(first = lowest)? Maybe something with default_scope :order?

有没有办法按avaliabilities.first.price（第一 = 最低）自动订购房屋？也许有什么default_scope :order？

回答by ecoologic

I would suggest to avoid using default_scope, especially on something like price on another table. Every time you'll use that table, join and ordering will take place, possibly giving strange results in complex queries and anyway making your query slower.

我建议避免使用default_scope，尤其是在另一张桌子上的价格之类的东西上。每次您使用该表时，都会进行连接和排序，这可能会在复杂查询中产生奇怪的结果，并且无论如何都会使您的查询变慢。

There's nothing wrong with a scope of its own, it's simpler and it's even clearer, you can make it as simple as:

一个 scope 本身并没有错，它更简单，更清晰，你可以把它变得简单：

scope :ordered, -> { includes(:availabilities).order('availabilities.price') }

PS: Remember to add an index on price; Also see other great answers in here to decide between join/include.

PS：记得在上加索引price；另请参阅此处的其他精彩答案以在join/include之间做出决定。

回答by Steve Davis

Figured it out with help from this related post.

在此相关帖子的帮助下解决了这个问题。

I moved the ordering out of the Home model and into the Availability model:

我将排序从 Home 模型移到了 Availability 模型中：

Availability

可用性

default_scope :order => "price ASC"

Then I eager loaded availabilities into the Home model and sorted by price:

然后我急切地将可用性加载到 Home 模型中并按价格排序：

Home

家

default_scope :include => :availabilities, :order => "availabilities.price ASC"

回答by TeWu

@ecoologic answer:

@生态答案：

scope :ordered, -> { includes(:availabilities).order('availabilities.price') }

is great, but it should be mentioned that includescould, and in some cases should be replaced by joins. They both have their optimal use cases.

很棒，但应该提到的是includes可以，并且在某些情况下应该替换为joins. 他们都有自己的最佳用例。

From practical standpoint there are two main differences:

从实际的角度来看，有两个主要区别：

1. includesloads associated record(s); in this case Availabilityrecords. joinsdon't load any associated record(s). So you should use includeswhen you want to use data from join model e.g. display pricesomewhere. On the other hand, joinsshould be used if you intend to use join model's data only in query e.g. in ORDER BYor WHEREclauses.

2. includesloads all records, while joinsloads only those records that have associated join model. So in OP's case, Home.includes(:availabilities)would load all homes, while Home.joins(:availabilities)would load only those homes that have associated at least one availability.

1. includes加载相关记录；在这种情况下Availability记录。joins不要加载任何关联的记录。所以includes当你想使用来自连接模型的数据时你应该使用，例如在price某处显示。另一方面，joins如果您打算仅在查询中使用连接模型的数据，例如 in ORDER BYorWHERE子句，则应使用。

2. includes加载所有记录，而joins只加载那些具有关联连接模型的记录。因此，在 OP 的情况下，Home.includes(:availabilities)将加载所有房屋，而Home.joins(:availabilities)将仅加载与至少一个可用性相关联的那些房屋。

Also see this question.

另请参阅此问题。

回答by Lucas Caton

In Rails 5.2+, you might get a deprecation warning when passing a string param to order method:

在 Rails 5.2+ 中，将字符串参数传递给 order 方法时，您可能会收到弃用警告：

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "table.column". Non-attribute arguments will be disallowed in Rails 6.0. This method should not be called with user-provided values, such as request parameters or model attributes.

弃用警告：使用非属性参数调用的危险查询方法（其参数用作原始 SQL 的方法）：“table.column”。Rails 6.0 中将不允许使用非属性参数。不应使用用户提供的值调用此方法，例如请求参数或模型属性。

To solve this, you can use Arel.sql():

要解决此问题，您可以使用Arel.sql()：

scope :ordered, -> {
  includes(:availabilities).order(Arel.sql('availabilities.price'))
}

回答by nothing-special-here

Another way to achieve this:

实现这一目标的另一种方法：

scope :ordered, -> { includes(:availabilities).order(Availability.arel_table[:price]) }

You can also specify ASCdirection with

您还可以指定 ASC方向

scope :ordered, -> { includes(:availabilities).order(Availability.arel_table[:price].asc) }

DESC:

DESC：

scope :ordered, -> { includes(:availabilities).order(Availability.arel_table[:price].desc) }

Using arel_tableon ActiveRecordmodel makes you save against scenario when table name changed (but it happens very rarely).

arel_table在ActiveRecord模型上使用可以让您避免表名更改时的情况（但这种情况很少发生）。

Note that it is nice to add main_table#idfor determinate sorting.

请注意，main_table#id为确定排序添加是很好的。

So final version would be:

所以最终版本将是：

scope :ordered, -> {
  includes(:availabilities).
    order(Availability.arel_table[:price].asc, order(Home.arel_table[:id].asc)
}

回答by mld.oscar

You can also sort linked tables like this (e.g.):

您还可以像这样对链接表进行排序（例如）：

class User
  has_many :posts
end

class Post
  belongs_to :user

  scope :sorted_by_user_and_title, -> { 
    joins(:user).merge(
     User.order(first_name: :asc, last_name: :asc)
    )
    .order(title: :desc)
    # SELECT * FROM `posts`
    # INNER JOIN `users` ON `posts`.`user_id` = `users`.`id`
    # ORDER BY
    # `users`.`first_name` ASC, `users`.`last_name` ASC, `posts`.`title` DESC;
  }
  scope :sorted_by_title_and_user, -> { 
    order(title: :desc)
    .joins(:user).merge(
     User.order(first_name: :asc, last_name: :asc)
    )
    # SELECT * FROM `posts`
    # INNER JOIN `users` ON `posts`.`user_id` = `users`.`id`
    # ORDER BY
    # `posts`.`title` DESC, `users`.`first_name` ASC, `users`.`last_name` ASC;
  }
end

Regards

问候