ActiveRecord查询获取MySQL中的第一个不同记录



我想改进这个查询,以返回主键列loco_no的不同记录

LocoUpdate.where(is_live: true).order(updated_at: :desc)

目前它返回满足上述查询的loco_no的所有记录,但我只想返回Expected

中看到的第一个记录
#<ActiveRecord::Relation [
#<LocoUpdate id: 36, loco_no: 125, event: "create", mfd_sl_no: "600483", mfd_date: nil, loco_name: "", status: "Withdrawn", comments: "Vacuum brakes only", object_changes: {"id"=>[nil, 94], "livery"=>[nil, ""], "status"=>[nil, "Withdrawn"], "loco_no"=>[nil, 125], "shed_id"=>[nil, 109], "user_id"=>[nil, 1], "comments"=>[nil, "Vacuum brakes only"], "loco_name"=>[nil, ""], "mfd_sl_no"=>[nil, "600483"], "sub_class"=>[nil, ""], "created_at"=>[nil, "2021-02-27T11:19:28.561Z"], "updated_at"=>[nil, "2021-02-27T11:19:28.561Z"], "loco_class_id"=>[nil, 109], "manufacturer_id"=>[nil, 107]}, is_live: true, loco_id: 94, user_id: 2, manufacturer_id: 107, shed_id: 109, loco_class_id: 109, created_at: "2021-02-27 11:19:28", updated_at: "2021-02-27 11:46:14">, 
#<LocoUpdate id: 43, loco_no: 12321, event: "update", mfd_sl_no: "600485", mfd_date: "1901-02-27 00:00:00", loco_name: "some-loco", status: "Withdrawn", comments: "made an edit right", object_changes: {"comments"=>["made an edit nowwwwww", "made an edit right nowwwwww"], "updated_at"=>["2021-02-27T11:42:15.058Z", "2021-02-27T11:43:57.570Z"]}, is_live: true, loco_id: 93, user_id: 2, manufacturer_id: 107, shed_id: 107, loco_class_id: 106, created_at: "2021-02-27 11:43:57", updated_at: "2021-02-27 11:45:25">, 
#<LocoUpdate id: 42, loco_no: 12321, event: "update", mfd_sl_no: "600485", mfd_date: "1901-02-27 00:00:00", loco_name: "some-loco", status: "Withdrawn", comments: "made an edit nowwwwww", object_changes: {"user_id"=>[1, 2], "comments"=>["made an edit here for visibility more", "made an edit nowwwwww"], "updated_at"=>["2021-02-27T11:19:28.542Z", "2021-02-27T11:42:15.058Z"]}, is_live: true, loco_id: 93, user_id: 2, manufacturer_id: 107, shed_id: 107, loco_class_id: 106, created_at: "2021-02-27 11:42:15", updated_at: "2021-02-27 11:44:36">, 
#<LocoUpdate id: 35, loco_no: 12321, event: "create", mfd_sl_no: "600485", mfd_date: "1901-02-27 00:00:00", loco_name: "some-loco", status: "Withdrawn", comments: "made an edit here for visibility more", object_changes: {"id"=>[nil, 93], "livery"=>[nil, ""], "status"=>[nil, "Withdrawn"], "loco_no"=>[nil, 12321], "shed_id"=>[nil, 107], "user_id"=>[nil, 1], "comments"=>[nil, "Vacuum brakes only"], "mfd_date"=>[nil, "1901-02-27T11:19:28.000Z"], "loco_name"=>[nil, "some-loco"], "mfd_sl_no"=>[nil, "600485"], "sub_class"=>[nil, ""], "created_at"=>[nil, "2021-02-27T11:19:28.542Z"], "updated_at"=>[nil, "2021-02-27T11:19:28.542Z"], "loco_class_id"=>[nil, 106], "manufacturer_id"=>[nil, 107]}, is_live: true, loco_id: 93, user_id: 2, manufacturer_id: 107, shed_id: 107, loco_class_id: 106, created_at: "2021-02-27 11:19:28", updated_at: "2021-02-27 11:41:28">
]>

预期:

#<ActiveRecord::Relation [
#<LocoUpdate id: 36, loco_no: 125, event: "create", mfd_sl_no: "600483", mfd_date: nil, loco_name: "", status: "Withdrawn", comments: "Vacuum brakes only", object_changes: {"id"=>[nil, 94], "livery"=>[nil, ""], "status"=>[nil, "Withdrawn"], "loco_no"=>[nil, 125], "shed_id"=>[nil, 109], "user_id"=>[nil, 1], "comments"=>[nil, "Vacuum brakes only"], "loco_name"=>[nil, ""], "mfd_sl_no"=>[nil, "600483"], "sub_class"=>[nil, ""], "created_at"=>[nil, "2021-02-27T11:19:28.561Z"], "updated_at"=>[nil, "2021-02-27T11:19:28.561Z"], "loco_class_id"=>[nil, 109], "manufacturer_id"=>[nil, 107]}, is_live: true, loco_id: 94, user_id: 2, manufacturer_id: 107, shed_id: 109, loco_class_id: 109, created_at: "2021-02-27 11:19:28", updated_at: "2021-02-27 11:46:14">, 
#<LocoUpdate id: 43, loco_no: 12321, event: "update", mfd_sl_no: "600485", mfd_date: "1901-02-27 00:00:00", loco_name: "some-loco", status: "Withdrawn", comments: "made an edit right nowwwwww",  object_changes: {"comments"=>["made an edit nowwwwww", "made an edit right nowwwwww"], "updated_at"=>["2021-02-27T11:42:15.058Z", "2021-02-27T11:43:57.570Z"]}, is_live: true, loco_id: 93, user_id: 2, manufacturer_id: 107, shed_id: 107, loco_class_id: 106, created_at: "2021-02-27 11:43:57", updated_at: "2021-02-27 11:45:25">
]>

您可以使用DISTINCT ONselect原始SQL:

LocoUpdate.where(is_live: true)
.order(updated_at: :desc)
.select('DISTINCT ON (loco_no) *')

为MySQL:

LocoUpdate.where(is_live: true)
.order(updated_at: :desc)
.group(:loco_no)

相关内容

  • 没有找到相关文章

最新更新