如何将自表子查询的SQL到Rails的named_scope？ [英] How to turn a self-table subquery SQL to a Rails named_scope?

问题描述

我使用的是轨道2.3.10和新 named_scope 。我处理一个SQL以检索的特定事件的最后邀请列表。我想出了用子查询SQL，它看起来像它可以做我想做的。我在想是不是可以使用 named_scope 做同样的事情，这样我可以利用它与找到（）。

我有以下问题：

1. 是否有可能实现的SQL与 named_scope ？
2. 能不能在一个优雅的方式，使子选择不包含在：？？条件多个named_scope需要
3. 如何做 named_scope （S）是什么样子？
4. 如何在找到（）的样子时，它包括name_scope（S）？

SQL：

  SELECT *
  从邀请INV1
  加入 （
         SELECT事项标识，USER_ID，MAX（invite_time）AS last_invite_time
           从邀请函
          GROUP BY事项标识，USER_ID
       ）AS last_invite ON
       inv1.event_id = last_invite.event_id和
       inv1.user_id = last_invite.user_id和
       inv1.invite_time = last_invite.last_invite_time
 

邀请数据：

  EVENT_ID USER_ID invite_time invite_reply_ code
1 78 2011-02-01 15:21 1
2 78 2011-02-02 11:45 1
2 79 2011-02-02 11:50 1
2 79 2011-02-02 11:55 1
2 80 2011-02-02 11:50 1
2 80 2011-02-02 11:51 1
 

预期的结果：

  EVENT_ID USER_ID invite_time invite_reply_ code
2 78 2011-02-02 11:45 1
2 79 2011-02-02 11:55 1
2 80 2011-02-02 11:51 1
 

解决方案

在：在加入选项找到即可一个字符串，它会只是坚持到SQL。要使你的 EVENT_ID 在那里，你将需要使用lambda，所以这样的事情是什么，你是后：

  named_scope：FOO，拉姆达{| EVENT_ID |
  {：选择=＆GT; OINV。*，
    ：从=＆GT; 邀请AS OINV
    ：加入=＆GT; ＆LT;＆LT; -SQL
      加入 （
             SELECT事项标识，USER_ID，MAX（invite_time）AS last_invite_time
               从邀请AS jinv
              GROUP BY事项标识，USER_ID
           ）AS last_invite ON
           oinv.event_id = last_invite.event_id和
           oinv.user_id = last_invite.user_id和
           oinv.invite_time = last_invite.last_invite_time
      SQL
    ，：条件=＆GT; [oinv.event_id =？，事项标识]
  }
}
 

这是完全未经测试，但希望你能看到我在做什么，这台你在正确的道路上。

I'm using rails 2.3.10 and new to named_scope. I'm dealing with a SQL which retrieves a list of last invitations of a particular event. I came up with a SQL with subquery and it looks like it can do what I want. I'm thinking of is it possible to use named_scope to do the same thing so that I can make use of it with find().

I have the following questions:

1. Is it possible to implement the SQL with named_scope?
2. Can it be in a elegant way so that the sub-select is not included in the :condition?more than one named_scope needed?
3. How do the named_scope(s) look like?
4. How does the find() look like when it includes the name_scope(s)?

SQL:

SELECT *
  FROM invitation inv1
  JOIN (
         SELECT event_id, user_id, MAX(invite_time) AS last_invite_time
           FROM invitation
          GROUP BY event_id, user_id
       ) AS last_invite ON
       inv1.event_id = last_invite.event_id AND
       inv1.user_id = last_invite.user_id AND
       inv1.invite_time = last_invite.last_invite_time

Invitation data:

event_id     user_id     invite_time       invite_reply_code
1            78          2011-02-01 15:21  1
2            78          2011-02-02 11:45  1
2            79          2011-02-02 11:50  1
2            79          2011-02-02 11:55  1
2            80          2011-02-02 11:50  1
2            80          2011-02-02 11:51  1

Expected result:

event_id     user_id     invite_time       invite_reply_code
2            78          2011-02-02 11:45  1
2            79          2011-02-02 11:55  1
2            80          2011-02-02 11:51  1

解决方案

The :joins option in find can take a string, which it will just stick into the SQL. To get your event_id in there you will need to use a lambda, so something like this is what you're after:

named_scope :foo, lambda { |event_id|
  { :select => "oinv.*",
    :from => "invitation AS oinv"
    :joins => <<-SQL
      JOIN (
             SELECT event_id, user_id, MAX(invite_time) AS last_invite_time
               FROM invitation AS jinv
              GROUP BY event_id, user_id
           ) AS last_invite ON
           oinv.event_id       = last_invite.event_id AND
           oinv.user_id        = last_invite.user_id AND
           oinv.invite_time    = last_invite.last_invite_time
      SQL
    , :conditions => [ "oinv.event_id = ?", event_id ]
  }
}

This is totally untested, but hopefully you can see what I'm doing and this sets you on the right path.

这篇关于如何将自表子查询的SQL到Rails的named_scope？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！