ruby-on-railspostgresqlruby-on-rails-4rails-postgresql
how to efficently use group_by using rails
I have tables records like below:
I am grouping them based on by am, pm and none.
What I really need to break down the group_by for none to 7 7 columns as my total records of none are 14.
Query I am working with:
SlotFormula.select([:id,:doorman,:day]).where(:zone_id => 1).order('created_at
asc').group_by { |result| [result.doorman] }
Current Results from query are:
=> {["am"]=>
[
#<SlotFormula id: 1659, day: "Monday", doorman: "am">,
#<SlotFormula id: 1660, day: "Tuesday", doorman: "am">,
#<SlotFormula id: 1661, day: "Wednesday", doorman: "am">,
#<SlotFormula id: 1662, day: "Thursday", doorman: "am">,
#<SlotFormula id: 1663, day: "Friday", doorman: "am">,
#<SlotFormula id: 1664, day: "Saturday", doorman: "am">,
#<SlotFormula id: 1665, day: "Sunday", doorman: "am">],
["pm"]=>
[
#<SlotFormula id: 1666, day: "Monday", doorman: "pm">,
#<SlotFormula id: 1667, day: "Tuesday", doorman: "pm">,
#<SlotFormula id: 1668, day: "Wednesday", doorman: "pm">,
#<SlotFormula id: 1669, day: "Thursday", doorman: "pm">,
#<SlotFormula id: 1670, day: "Friday", doorman: "pm">,
#<SlotFormula id: 1671, day: "Saturday", doorman: "pm">,
#<SlotFormula id: 1672, day: "Sunday", doorman: "pm">],
["none"]=>[
#<SlotFormula id: 1673, day: "Monday", doorman: "none">,
#<SlotFormula id: 1674, day: "Tuesday", doorman: "none">,
#<SlotFormula id: 1675, day: "Wednesday", doorman: "none">,
#<SlotFormula id: 1676, day: "Thursday", doorman: "none">,
#<SlotFormula id: 1677, day: "Friday", doorman: "none">,
#<SlotFormula id: 1678, day: "Saturday", doorman: "none">,
#<SlotFormula id: 1679, day: "Sunday", doorman: "none">,
#<SlotFormula id: 1680, day: "Monday", doorman: "none">,
#<SlotFormula id: 1681, day: "Tuesday", doorman: "none">,
#<SlotFormula id: 1682, day: "Wednesday", doorman: "none">,
#<SlotFormula id: 1683, day: "Thursday", doorman: "none">,
#<SlotFormula id: 1684, day: "Friday", doorman: "none">,
#<SlotFormula id: 1685, day: "Saturday", doorman: "none">,
#<SlotFormula id: 1686, day: "Sunday", doorman: "none">
]}
Expected Results Needed:
=> {["am"]=>
[
#<SlotFormula id: 1659, day: "Monday", doorman: "am">,
#<SlotFormula id: 1660, day: "Tuesday", doorman: "am">,
#<SlotFormula id: 1661, day: "Wednesday", doorman: "am">,
#<SlotFormula id: 1662, day: "Thursday", doorman: "am">,
#<SlotFormula id: 1663, day: "Friday", doorman: "am">,
#<SlotFormula id: 1664, day: "Saturday", doorman: "am">,
#<SlotFormula id: 1665, day: "Sunday", doorman: "am">],
],
["pm"]=>
[
#<SlotFormula id: 1666, day: "Monday", doorman: "pm">,
#<SlotFormula id: 1667, day: "Tuesday", doorman: "pm">,
#<SlotFormula id: 1668, day: "Wednesday", doorman: "pm">,
#<SlotFormula id: 1669, day: "Thursday", doorman: "pm">,
#<SlotFormula id: 1670, day: "Friday", doorman: "pm">,
#<SlotFormula id: 1671, day: "Saturday", doorman: "pm">,
#<SlotFormula id: 1672, day: "Sunday", doorman: "pm">],
],
["none"]=>[
#<SlotFormula id: 1673, day: "Monday", doorman: "none">,
#<SlotFormula id: 1674, day: "Tuesday", doorman: "none">,
#<SlotFormula id: 1675, day: "Wednesday", doorman: "none">,
#<SlotFormula id: 1676, day: "Thursday", doorman: "none">,
#<SlotFormula id: 1677, day: "Friday", doorman: "none">,
#<SlotFormula id: 1678, day: "Saturday", doorman: "none">,
#<SlotFormula id: 1679, day: "Sunday", doorman: "none">,
],
["none"]=>[
#<SlotFormula id: 1680, day: "Monday", doorman: "none">,
#<SlotFormula id: 1681, day: "Tuesday", doorman: "none">,
#<SlotFormula id: 1682, day: "Wednesday", doorman: "none">,
#<SlotFormula id: 1683, day: "Thursday", doorman: "none">,
#<SlotFormula id: 1684, day: "Friday", doorman: "none">,
#<SlotFormula id: 1685, day: "Saturday", doorman: "none">,
#<SlotFormula id: 1686, day: "Sunday", doorman: "none">
]
}
EDIT
My other question is: How I can show them as columns not rows?
So,
r denotes result in below table
n denotes none in below table
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
am am(mo r) am(tu r) am(we r) am(th r) am(fr r) am(sa r) am(su r)
pm pm(mo r) pm(tu r) pm(we r) pm(th r) pm(fr r) pm(sa r) pm(su r)
n n(mo r) n(tu r) n(we r) n(th r) n(fr r) n(sa r) n(su r)
n n(mo r) n(tu r) n(we r) n(th r) n(fr r) n(sa r) n(su r)
n n(mo r) n(tu r) n(we r) n(th r) n(fr r) n(sa r) n(su r)
am am(mo r) am(tu r) am(we r) am(th r) am(fr r) am(sa r) am(su r)
pm pm(mo r) pm(tu r) pm(we r) pm(th r) pm(fr r) pm(sa r) pm(su r)
n n(mo r) n(tu r) n(we r) n(th r) n(fr r) n(sa r) n(su r)
n n(mo r) n(tu r) n(we r) n(th r) n(fr r) n(sa r) n(su r)
n n(mo r) n(tu r) n(we r) n(th r) n(fr r) n(sa r) n(su r)
If someone has more better way to deal with it. Please share as well.
Solution
What I really need to break down the group_by for none to
77columns as my total records ofnoneare14.
You can use the same query you already have, just add one more iteration, using Array#map and Enumerable#each_slice, to break each group (that is ["am"], ["pm"], ["none"]) into groups of seven objects each:
SlotFormula.select(:id, :doorman, :day)
.where(zone_id: 1)
.order(created_at: :asc)
.group_by(&:doorman)
.map { |k, v| { k => v.each_slice(7).to_a } }
First you'll find that group_by changed to group_by(&:doorman) which will get the exact same result as using group_by { |elem| elem.doorman } (notice that the [ and ] where removed, so instead of ["pm"], you'll get "pm".
Second, we added .map which applies each_slice(7) to every element of each group, which splits any group larger than 7 in multiple groups of seven each.
Output:
[
{
"am"=> [
[
#<SlotFormula:0x007f8128968ec0 id: 1, day: "Monday", doorman: "am">,
#<SlotFormula:0x007f8128968d80 id: 2, day: "Tuesday", doorman: "am">,
#<SlotFormula:0x007f8128968c40 id: 3, day: "Wednesday", doorman: "am">,
#<SlotFormula:0x007f8128968b00 id: 4, day: "Thursday", doorman: "am">,
#<SlotFormula:0x007f81289689c0 id: 5, day: "Friday", doorman: "am">,
#<SlotFormula:0x007f8128968880 id: 6, day: "Saturday", doorman: "am">,
#<SlotFormula:0x007f8128968740 id: 7, day: "Sunday", doorman: "am">
]
]
},
{
"pm"=> [
[
#<SlotFormula:0x007f8128968600 id: 8, day: "Monday", doorman: "pm">,
#<SlotFormula:0x007f81289684c0 id: 9, day: "Tuesday", doorman: "pm">,
#<SlotFormula:0x007f8128968380 id: 10, day: "Wednesday", doorman: "pm">,
#<SlotFormula:0x007f8128968240 id: 11, day: "Thursday", doorman: "pm">,
#<SlotFormula:0x007f8128968100 id: 12, day: "Friday", doorman: "pm">,
#<SlotFormula:0x007f8128973f78 id: 13, day: "Saturday", doorman: "pm">,
#<SlotFormula:0x007f8128973e38 id: 14, day: "Sunday", doorman: "pm">
]
]
},
{
"none"=> [
[
#<SlotFormula:0x007f8128973cf8 id: 15, day: "Monday", doorman: "none">,
#<SlotFormula:0x007f8128973bb8 id: 16, day: "Tuesday", doorman: "none">,
#<SlotFormula:0x007f8128973a78 id: 17, day: "Wednesday", doorman: "none">,
#<SlotFormula:0x007f8128973938 id: 18, day: "Thursday", doorman: "none">,
#<SlotFormula:0x007f81289737f8 id: 19, day: "Friday", doorman: "none">,
#<SlotFormula:0x007f81289736b8 id: 20, day: "Saturday", doorman: "none">,
#<SlotFormula:0x007f8128973578 id: 21, day: "Sunday", doorman: "none">
], [
#<SlotFormula:0x007f8128973438 id: 22, day: "Monday", doorman: "none">,
#<SlotFormula:0x007f81289732f8 id: 23, day: "Tuesday", doorman: "none">,
#<SlotFormula:0x007f81289731b8 id: 24, day: "Wednesday", doorman: "none">,
#<SlotFormula:0x007f8128973078 id: 25, day: "Thursday", doorman: "none">,
#<SlotFormula:0x007f8128972f38 id: 26, day: "Friday", doorman: "none">,
#<SlotFormula:0x007f8128972df8 id: 27, day: "Saturday", doorman: "none">,
#<SlotFormula:0x007f8128972cb8 id: 28, day: "Sunday", doorman: "none">
]
]
}
]
This changes your original format a little, for instance is an Array of Hash objects (original was a Hash for Array objects), but the main change is the addition of one more level of arrays, where everything is grouped in blocks of 7 objects.
This is not so clear with am and pm groups since both have exactly 7 items, so you see an array with one array (of 7 elements inside). But with none you can see an array with two arrays inside, each of 7 items (i.e objects).
I recommend this approach since it will be easier to iterate and fetch the data with keys such as :pm, for example:
@slot_formulas[:pm].each do |group|
group.each do |slot_formula|
# <SlotFormula:0x007f81289fae38 id: 8, day: "Monday", doorman: "pm"> ...
end
end
But, if you need the format you already have, it is also possible to get, but instead of map we will need Enumerable#each_with_object:
SlotFormula.select(:id, :doorman, :day])
.where(zone_id: 1)
.order(created_at: :asc)
.group_by(&:doorman)
.each_with_object({}) { |(doorman, group), result| result[[doorman]] = group.each_slice(7).to_a }
It's a little more complicated, but you'll get the same output you had with the extra level of arrays (to break in groups of 7).
Output:
{
["am"] => [
[
#<SlotFormula:0x007f81289fb6f8 id: 1, day: "Monday", doorman: "am">,
#<SlotFormula:0x007f81289fb5b8 id: 2, day: "Tuesday", doorman: "am">,
#<SlotFormula:0x007f81289fb478 id: 3, day: "Wednesday", doorman: "am">,
#<SlotFormula:0x007f81289fb338 id: 4, day: "Thursday", doorman: "am">,
#<SlotFormula:0x007f81289fb1f8 id: 5, day: "Friday", doorman: "am">,
#<SlotFormula:0x007f81289fb0b8 id: 6, day: "Saturday", doorman: "am">,
#<SlotFormula:0x007f81289faf78 id: 7, day: "Sunday", doorman: "am">
]
],
["pm"] => [
[
#<SlotFormula:0x007f81289fae38 id: 8, day: "Monday", doorman: "pm">,
#<SlotFormula:0x007f81289facf8 id: 9, day: "Tuesday", doorman: "pm">,
#<SlotFormula:0x007f81289fabb8 id: 10, day: "Wednesday", doorman: "pm">,
#<SlotFormula:0x007f81289faa78 id: 11, day: "Thursday", doorman: "pm">,
#<SlotFormula:0x007f81289fa938 id: 12, day: "Friday", doorman: "pm">,
#<SlotFormula:0x007f81289fa7f8 id: 13, day: "Saturday", doorman: "pm">,
#<SlotFormula:0x007f81289fa6b8 id: 14, day: "Sunday", doorman: "pm">
]
],
["none"] => [
[
#<SlotFormula:0x007f81289fa578 id: 15, day: "Monday", doorman: "none">,
#<SlotFormula:0x007f81289fa438 id: 16, day: "Tuesday", doorman: "none">,
#<SlotFormula:0x007f81289fa2f8 id: 17, day: "Wednesday", doorman: "none">,
#<SlotFormula:0x007f81289fa1b8 id: 18, day: "Thursday", doorman: "none">,
#<SlotFormula:0x007f81289fa078 id: 19, day: "Friday", doorman: "none">,
#<SlotFormula:0x007f81289f9f38 id: 20, day: "Saturday", doorman: "none">,
#<SlotFormula:0x007f81289f9df8 id: 21, day: "Sunday", doorman: "none">
],
[
#<SlotFormula:0x007f81289f9cb8 id: 22, day: "Monday", doorman: "none">,
#<SlotFormula:0x007f81289f9b78 id: 23, day: "Tuesday", doorman: "none">,
#<SlotFormula:0x007f81289f9a38 id: 24, day: "Wednesday", doorman: "none">,
#<SlotFormula:0x007f81289f98f8 id: 25, day: "Thursday", doorman: "none">,
#<SlotFormula:0x007f81289f97b8 id: 26, day: "Friday", doorman: "none">,
#<SlotFormula:0x007f81289f9678 id: 27, day: "Saturday", doorman: "none">,
#<SlotFormula:0x007f81289f9538 id: 28, day: "Sunday", doorman: "none">
]
]
}
Either way, you get the grouping of 7 you needed, just use the one you fill more confortable to present your table.
How I can show them as columns not rows?
You can loop trough the result array like this:
<table>
<% @slots.each do |doorman_groups| %>
<% doorman_groups.each do |doorman, groups| %>
<% groups.each do |subgroups| %>
<tr>
<td><%= doorman %></td>
<% subgroups.each do |subgroup| %>
<td><%= subgroup.inspect %></td>
<% end %>
</tr>
<% end %>
<% end %>
<% end %>
</table>
- Create a Grouping Column/Variable from other Columns in R
- Specify the height of the bars in the gglikert function in R
- modelsummary modelplot: change linewidth
- How to create a conditional panel using a reactive object that is passed from another module?
- renderHighchart output not displaying in Shiny App
- How can I specify GeoTiff configuration of PixelIsPoint when exporting a raster using R package terra?
- Create a list from a dataframe in R
- Fast way of converting large list to dataframe
- rintrojs only shows first dialog in Safari
- Automatically read a column of lowercases True and False as logical
- How do I add counts to a stacked bar graph?
- Counting the number of rows between each pair of months?
- Plot multiple normalized stock charts from different dates into a single plot
- Select columns based on string match - dplyr::select
- Looking for a more efficient way to replace matrix elements
- custom R function with group argument does not work while using the filter
- ggsurvplot function, risk table alignment problem
- plot running average in ggplot2
- Calling variable in df within function
- How to find position of running minimum (runMin) in a vector in R?
- Using httr2::last_response() in conjunction with purrr::possibly()
- Cropping a raster using terra does not return the expected extent
- Can janitor::clean_names be used on only certain columns in a data frame?
- Efficient way of row binding time series in a data.table, with correctly sorted timestamps
- Defining optional arguments in R when more complex function
- Can I change the cursor in plotly only when hovering over points?
- Issue Loading RStoolbox: "Cannot find proj.db" Error
- Filter CSV files for specific value before importing
- Matching the same lines from 2 different files and 2 columns
- Conditional coloring and outer borders in pdf KableExtra table in R