如何选择具有max(:created_at(Rails 3，PostgreSQL)的行

Question

想要得到（这个）｛示例⬇}数据库中的数据。

有这样的数据：

=> #<Item id: 2330, asin: “7701C6RHIY", domain: "com”, title: “badfsd”, created_at: "2014-04-04 01:42:28">
(this) => #<Item id: 2330, asin: “7701C6RHIY", domain: "com”, title: “badfsd”, created_at: "2014-04-04 03:52:28">
=> #<Item id: 2330, asin: “7701C6RHIY", domain: "com”, title: “badfsd”, created_at: "2014-04-04 03:42:18">
=> #<Item id: 2330, asin: “7701C6RHIY", domain: "com”, title: “badfsd”, created_at: "2014-04-04 03:42:22”>

+

(this) => #<Item id: 4331, asin: “R901C6RHIY", domain: “ca”, title: “FSDs”, created_at: "2014-07-04 03:42:28">
=> #<Item id: 4331, asin: “R901C6RHIY", domain: “ca”, title: “FSDs”, created_at: "2014-04-04 03:42:28">
=> #<Item id: 4331, asin: “R901C6RHIY", domain: “ca”, title: “FSDs”, created_at: "2014-05-04 03:42:28">

+

(this) => #<Item id: 571, asin: "BSD1C6RHIY", domain: "co.jp", title: "BRIDGサイズ SCN20.A シルバー”, created_at: "2014-04-04 03:42:28">
=> #<Item id: 570, asin: "BSD1C6RHIY", domain: "co.jp", title: "BRIDGサイズ SCN20.A シルバー”, created_at: "2014-04-02 03:42:28">
=> #<Item id: 560, asin: "BSD1C6RHIY", domain: "co.jp", title: "BRIDGサイズ SCN20.A シルバー”, created_at: "2014-04-01 03:42:28">

结果类似于（这个）数据，id=>2330、4331、571

+++更新+++

现在这对我有效：

 @item_registrations = ItemRegistration.find_all_by_user_id(current_user.id)
 @items = []
 @item_registrations.try(:each) do |i|
   @items += Item.where(:asin => i.asin, :domain => i.domain, :user_id => i    .user_id).order('created_at desc').limit(1)
 end

但是，这些东西很慢，所以，也许，还有其他方法。

+++更新2+++

我想做这样的事情：

@items = Item.where(:user_id => current_user.id).where(:created_at => ("SELECT m    ax(created_at) from Items"))

获取消息->

PG::InvalidDatetimeFormat: ERROR:  invalid input syntax for type timestamp: "SELECT max(created_at) from Items"
LINE 1: ... "items"."user_id" = 1 AND "items"."created_at" = 'SELECT ma...
                                                             ^
: SELECT  "items".* FROM "items"  WHERE "items"."user_id" = 1 AND "items"."created_at" = 'SELECT max(created_at) from Items' LIMIT 1

Answer 1

如果您想检索最新创建的uniq记录，您可以执行此

Item.select(:id).map(&:id).order("created_at DESC").uniq
                      OR
Item.uniq.pluck(:id).order("created_at DESC")
which gives you the latest created records which are unique
     #<Item id: 571, asin: "BSD1C6RHIY", domain: "co.jp", title: "BRIDGサイズ SCN20.A シルバー”, created_at: "2014-04-04 03:42:28">
     #<Item id: 2330, asin: “7701C6RHIY", domain: "com”, title: “badfsd”, created_at: "2014-04-04 03:52:28">
     #<Item id: 4331, asin: “R901C6RHIY", domain: “ca”, title: “FSDs”, created_at: "2014-07-04 03:42:28">

注意：

我不确定这是否100%正确。请尝试一下。

Answer 2

sql = "SELECT i.* from items i where i.created_at in (select max(i2.created_at) from items i2 group by i2.asin, i2.domain )
@items = ActiveRecord::Base.connection.execute(sql)