使用 Rails/AR 生成这个复杂的查询

发布于 2024-10-30 19:29:04 字数 520 浏览 0 评论 0原文

试图解决分组和排序问题（原始问题在这里：“复杂”分组和索引在 Rails 中？），我得到了一个 SQL 查询，它将以正确的方式获取正确的记录。

我现在的问题是：如何使用 Rails/AR Syntax 生成此 SQL 查询？

SQL 查询如下：

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

原文

In trying to solve a grouping and ordering problem (original question here: "Complex" grouping and indexing in rails?), I got a SQL query that will fetch the right records the right way.

My question now is: how do I generate this SQL query using Rails/AR synthax?

The SQL-query is as follows:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

世界和平 2024-11-06 19:29:04

我认为您需要使用数据库和这些属性才能使其正确，但您可以尝试这样的方法。

class User < ActiveRecord::Base
   has_one :next_task, :class_name => :task, :order => "due_date desc", :limit => 1
   has_many :tasks, :order => "due_date desc"

end

class Task < ActiveRecord::Base
    belongs_to :user

end

User.include([:next_task,:tasks]).order("task.due_date desc").tasks.each { |task|
      puts task.user.owner_id 
      puts task.user.owner_name
      puts task.due_date
}

我不确定对用户进行排序的 order 子句...您必须检查生成的 SQL 以确定到底需要什么。

I think you'll need to play around with your database and these attributes to get it just right, but you could try something like this.

class User < ActiveRecord::Base
   has_one :next_task, :class_name => :task, :order => "due_date desc", :limit => 1
   has_many :tasks, :order => "due_date desc"

end

class Task < ActiveRecord::Base
    belongs_to :user

end

User.include([:next_task,:tasks]).order("task.due_date desc").tasks.each { |task|
      puts task.user.owner_id 
      puts task.user.owner_name
      puts task.due_date
}

I'm not sure about the order clause to sort the users... you'll have to inspect the SQL generated to determine exactly what needs to be there.

回复收藏 0 原文

~没有更多了~