“复杂"Rails 中的分组和索引?

问题描述

我遇到了一个似乎找不到简单解决方案的问题.

I have a problem to which I can't seem to find a simple solution.

我想实现以下目标:
* 我有一个任务列表，每个任务都有一个所有者和一个截止日期
* 我想显示按所有者分组的所有任务的列表
* 我想根据截止日期对所有者进行排序:例如先是到期日最低的所有者，然后是次低的所有者，依此类推
* 我希望能够对结果进行分页，最好使用 will_paginate

I want to achieve the following:
* i have a list of tasks, each with an owner and a due date
* i want to display a list of all tasks grouped by owner
* i want to sort the owners based on the due dates: e.g. The owner with the lowest due date first, followed by the owner with the second lowest, etc
* I want to be able to paginate the results, preferable with will_paginate

为了说明，这将是我正在寻找的结果:

To ilustrate, this would be a result i am looking for:

哈利
- 任务 1，截止日期 1
- 任务 3，截止日期 4
本
- 任务 2，截止日期 2
卡罗尔
- 任务 4，截止日期 3

Harry
- task 1, due date 1
- task 3, due date 4
Ben
- task 2, due date 2
Carol
- task 4, due date 3

到目前为止，我可以查询所有有任务的所有者，根据虚拟属性对其最早截止日期"进行排序，然后在视图中显示和他们的任务.

So far, I can query for all owners with tasks, sort them on a virtual attribute with their "earliest due date" and then display them and their tasks in a view.

这种方法存在多个问题，imo:
* 我从视图(owner.tasks.each 等)运行多个查询.我总是了解到从视图运行查询是不好的
* 我正在通过加载内存中的所有记录(虚拟属性)来进行排序，这可能会导致大记录集出现问题.* 我不知道如何在此上进行分页，这对显示的任务数量很敏感

There are multiple problems with this approach, imo:
* i run multiple queries from the view (owner.tasks.each etc). I always learned that running queries from the view is bad
* i'm doing the sorting by loading all records in memory (virtual attribute), which could get problematic with large records set.* I have no idea how i can get pagination in on this, that will be sensitive to the amount of tasks displayed

我似乎无法破解这个难题，因此将不胜感激任何帮助或提示！
埃尔文

I can't seem to crack this nut, so any help or hints would be greatly appreciated!
Erwin

推荐答案

试试这个查询，你还没有提供示例数据(最好使用 SQL)以便我们自己玩:

Try this query, you have not provided sample data (ideally using SQL) so that we could play ourselves:

SELECT
u.id as owner_id, u.name as owner_name, t.id, t.due_date
FROM users u
INNER JOIN tasks m ON u.id = m.owner_id
INNER JOIN tasks t ON u.id = t.owner_id
GROUP BY u.id, u.name, t.id, t.due_date
ORDER BY MIN(m.due_date), t.due_date

您应该以正确的顺序获取所需的所有数据，您只需对其应用 LIMIT(或将其转换为 AR 并提交给 will_paginate)即可进行分页.

You should get all the data you need in the proper order, and you can paginate simply by applying LIMIT to it (or converting it to AR and submitting it to will_paginate).

这篇关于“复杂"Rails 中的分组和索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！