How do I get the nth record for each distinct name in a table in rails?
Essentially, I want to get the nth last entry for each distinct name.
So, if I have a table that looks something like
name field1
a 1
b 3
a 4
a 2
c 1
c 0
b 3
b 2
c 1
And I wanted to query for the second record of each distinct name (n = 2) I would like to get records
name field1
a 4
c 0
b 3
Is there a way to do this in one request. Or do I need to query once for each distinct name?
I'm trying to use a group function, but couldn't get it to return anything but the last entry for each group.
source to share
You can use order
to order your posts by their attribute name
:
# => [#<model:0x007f8293e61820 id: 1, name: "a", field1: "1"...,
...
#<model:0x007f8293e60150 id: 9, name: "c", field1: "1"...]
Then use pluck
to get only the attributes name
and field1
:
# => [["a", "1"], ["a", "4"], ["a", "2"], ["b", "3"], ["b", "3"], ["b", "2"], ["c", "1"], ["c", "0"], ["c", "1"]]
With this, you can work on your result as a hash and use group_by
to group them by the first element:
# => {"a"=>[["a", "1"], ["a", "4"], ["a", "2"]], "b"=>[["b", "3"], ["b", "3"], ["b", "2"]], "c"=>[["c", "1"], ["c", "0"], ["c", "1"]]}
And then use a map to get the second array value for each key value in the main hash:
# => [["a", "4"], ["b", "3"], ["c", "0"]]
So, you can try:
Model
.order(:name)
.pluck(:name, :field1)
.group_by(&:first)
.map{|_,v| v[1]}
.to_h
# => {"a"=>"4", "b"=>"3", "c"=>"0"}
source to share