sql - 使用 PostgreSQL 和 Rails 3 查找按关联字段排序的唯一记录？

Question

更新:感谢@Erwin Brandstetter，我现在有了这个:

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

...这似乎正是我想要的。不过，我仍然希望解决两个缺点:

1. order()子句使用直接 SQL 而不是 Arel(无法弄清楚)。

2. 调用 .count上面的查询给了我这个错误:

   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有关，所以我不必在 group 中手动输入所有内容。条款。

如何解决这两个问题？

<小时/>

原始问题:

到目前为止，这是我解决问题第一部分的内容:

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 条唯一的记录，这是正确的。

问题是我需要那些 User记录按 cards[:created_at] 排序(以该特定用户最早的时间为准)。正在追加.order(cards[:created_at])到上述方法末尾的范围绝对不会执行任何操作。

我尝试添加 .joins(:cards) ，但是返回 587 条记录，这是不正确的(重复 User s)。 group_by据我了解，它在这里实际上也没有用，因为 how PostgreSQL handles it .

我需要我的结果是 ActiveRecord::Relation (所以它是可链接的)返回一个唯一用户列表，这些用户拥有属于给定公司的卡，按他们的第一张卡的创建日期排序......使用用 Ruby 编写的查询，并且是与数据库无关。我怎样才能做到这一点？

<小时/>

class Company
  has_many :cards
end

class Card
  belongs_to :user
  belongs_to :company
end

class User
  has_many :cards
end

如果您需要任何其他信息，或者如果我的问题不清楚，请告诉我。

Answer 1

您要查找的查询应如下所示:

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)

如果您在结果中需要该表的列，则可以加入表user，否则您甚至不需要它来进行查询。
如果您不需要 SELECT 列表中的 min_created_at，则可以将其保留。

应该很容易翻译成 Ruby(我不擅长)。

<小时/>

要获取整个用户记录(正如我从您的评论中得出的那样):

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+，您可以简单地编写:

GROUP  BY u.id

(就像在 MySQL 中一样).. 提供的 id 是主键。

我引用release notes :

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

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