使用Rails / AR产生这种复杂查询 [英] Using Rails/AR to generate this complex query

问题描述

在试图解决一个分组和排序的问题（此处原题：<一href="http://stackoverflow.com/questions/5541055/complex-grouping-and-indexing-in-rails">"Complex"在？），我得到了一个SQL查询，将获取正确的轨道编组和索引记录的正确途径。

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

在SQL查询如下：

  SELECT
u.id为owner_id，u.name为OWNER_NAME，t.id，t.due_date
来自用户的ü
INNER JOIN任务M于u.id = m.owner_id
INNER JOIN任务的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
 

解决方案

我认为你需要玩弄你的数据库和这些属性得到它刚刚好，但你可以尝试这样的事情。

 类用户的LT;的ActiveRecord :: Base的
   HAS_ONE：next_task，：将class_name =＆GT; ：任务：为了=＆GT; DUE_DATE说明：限制=＆GT; 1
   的has_many：任务：为了=＆GT; DUE_DATE DESC

结束

类任务：LT;的ActiveRecord :: Base的
    belongs_to的：用户

结束

User.include（[：next_task，：任务]）的顺序（task.due_date降序）tasks.each {|任务|。
      把task.user.owner_id
      把task.user.owner_name
      把task.due_date
}
 

我不知道有关订单条款对用户进行排序......你必须检查产生的，以确定到底是什么需要存在的SQL。

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

解决方案

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.

这篇关于使用Rails / AR产生这种复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！