寻找独特的记录，在联想订购的领域，在PostgreSQL和Rails 3？ [英] Finding unique records, ordered by field in association, with PostgreSQL and Rails 3?

问题描述

更新：那么感谢@Erwin Brandstetter修改，我现在有这样的：

UPDATE: So thanks to @Erwin Brandstetter, I now have this:

def self.unique_users_by_company(company)
  users = User.arel_table
  cards = Card.arel_table

  users_columns = User.column_names.map { |col| users[col.to_sym] }

  cards_condition = cards[:company_id].eq(company.id).
    and(cards[:user_id].eq(users[:id]))

  User.joins(:cards).where(cards_condition).group(users_columns).
    order('min(cards.created_at)')
end

...这似乎做的正是我想要的。有两个缺点，我还是想已经解决了，但是：

... which seems to do exactly what I want. There are two shortcomings that I would still like to have addressed, however:

1. 的为了（）子句是使用直接的SQL，而不是阿雷尔（不能看着办吧）。

2. 调用 .Count之间上面的查询给了我这个错误：

1. The order() clause is using straight SQL instead of Arel (couldn't figure it out).

2. Calling .count on the query above gives me this error:

NoMethodError：未定义的方法'to_sym为   ＃＆LT;阿雷尔::属性::属性：0x007f870dc42c50＆GT;从   /Users/neezer/.rvm/gems/ruby-1.9.3-p0/gems/activerecord-3.1.1/lib/active_record/relation/calculations.rb:227:in   execute_grouped_calculation

NoMethodError: undefined method 'to_sym' for #<Arel::Attributes::Attribute:0x007f870dc42c50> from /Users/neezer/.rvm/gems/ruby-1.9.3-p0/gems/activerecord-3.1.1/lib/active_record/relation/calculations.rb:227:in 'execute_grouped_calculation'

......我相信这是可能与我是如何映射出 users_columns ，这样我就不必手动输入所有的人都在组条款。

... which I believe is probably related to how I'm mapping out the users_columns, so I don't have to manually type in all of them in the group clause.

我该如何解决这两个问题？

How can I fix those two issues?

原题

下面是我到目前为止，解决了我的问题的第一部分：

Here's what I have so far that solves the first part of my question:

def self.unique_users_by_company(company)
  users = User.arel_table
  cards = Card.arel_table

  cards_condition = cards[:company_id].eq(company.id)
    .and(cards[:user_id].eq(users[:id]))

  User.where(Card.where(cards_condition).exists)
end

这给了我84独特的记录，这是正确的。

This gives me 84 unique records, which is correct.

现在的问题是，我需要那些订购卡用户记录[：created_at] （无论是最早的该特定用户）。追加 .order（卡[：created_at]）。的范围，在方法的结尾上面也绝对没有什么

The problem is that I need those User records ordered by cards[:created_at] (whichever is earliest for that particular user). Appending .order(cards[:created_at]) to the scope at the end of the method above does absolutely nothing.

我尝试添加了 .joins（：卡），但给予退货587的记录，这是不正确（重复用户 S）。 GROUP_BY 据我了解是因为的 PostgreSQL中如何处理它。

I tried adding in a .joins(:cards), but that give returns 587 records, which is incorrect (duplicate Users). group_by as I understand it is practically useless here as well, because of how PostgreSQL handles it.

我需要的结果是一个的ActiveRecord ::关联（所以它的可链接），它返回唯一用户谁拥有卡列表属于某一公司，订购了他们的第一张牌的创建日期......与被写在Ruby中，是数据库无关的查询。我怎样才能做到这一点？

I need my result to be an ActiveRecord::Relation (so it's chainable) that returns a list of unique users who have cards that belong to a given company, ordered by the creation date of their first card... with a query that's written in Ruby and is database-agnostic. How can I do this?

class Company
  has_many :cards
end

class Card
  belongs_to :user
  belongs_to :company
end

class User
  has_many :cards
end

请让我知道如果你需要任何其他信息，或者如果我不清楚我的问题。

Please let me know if you need any other information, or if I wasn't clear in my question.

推荐答案

您正在寻找的应该是像这样的查询：

The query you are looking for should look like this one:

SELECT user_id, min(created_at) AS min_created_at
FROM   cards
WHERE  company_id = 1
GROUP  BY user_id
ORDER  BY min(created_at)

您可以加入在表用户如果您在结果需要一个表中的列，否则你甚至不需要它的查询。
如果你不需要 min_created_at 在 SELECT 列表中，你可以离开它了。

You can join in the table user if you need columns of that table in the result, else you don't even need it for the query.
If you don't need min_created_at in the SELECT list, you can just leave it away.

应该很容易转化为红宝石（这我不擅长的）。

Should be easy to translate to Ruby (which I am no good at).

要获得整个用户记录（我从您的评论中获得）：

To get the whole user record (as I derive from your comment):

SELECT u.*,
FROM   user u
JOIN  (
    SELECT user_id, min(created_at) AS min_created_at
    FROM   cards
    WHERE  company_id = 1
    GROUP  BY user_id
    ) c ON u.id = c.user_id
ORDER  BY min_created_at

或者

SELECT u.*
FROM   user u
JOIN   cards c ON u.id = c.user_id
WHERE  c.company_id = 1
GROUP  BY u.id, u.col1, u.col2, ..   -- You have to spell out all columns!
ORDER  BY min(c.created_at)

在PostgreSQL 9.1+，你可以简单的写：

With PostgreSQL 9.1+ you can simply write:

GROUP  BY u.id

（如在MySQL的）..提供的 ID 是主键。

我引用发行说明：

当主允许非GROUP BY列的查询目标列表中   关键是BY子句指定在GROUP（彼得Eisentraut）

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

SQL标准允许此问题，而且由于主键，   其结果是明确的。

The SQL standard allows this behavior, and because of the primary key, the result is unambiguous.

这篇关于寻找独特的记录，在联想订购的领域，在PostgreSQL和Rails 3？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！