sql - 带有父子记录的 Rails group_by 查询
问题描述
我正在尝试获取所有可能Buy
Sell
或Create
通过计算时间的用户,如下所示
我想喜欢下面的例子
2021 (0)
2020 (0)
2019 (4)
January (2)
-> Buy (1) // Specifically with records date if possible
-> Sell (1)
August (1)
-> Create (1)
September (1)
-> Buy (1)
2018 (3)
January (2)
-> Buy (1) // Specifically with records date if possible
-> Sell (1)
August (1)
-> Create (1)
And so on........
我正在尝试类似下面的东西
// controller
@user = User.find(params[:user_id])
@records = @user.records.joins(:task)
.where(tasks: {name: ["Buy", "Sell", "Create"]})
.group_by {|t| t.created_at.beginning_of_year}
// view.html.erb
<% @records.each do |y, h| %>
<%= y.strftime("%Y") %>
<% h.each do |f| %>
<%= f.task.name %>
<% end %>
<% end %>
Output like below
2018
Buy
Sell
下面是我的模型和表格,我尝试了几种方法,但仍然没有得到。
//table users
has_many :records, dependent: :destroy
--------------------
id | name | etc... |
--------------------
1 | john | etc |
--------------------
2 | alex | etc |
--------------------
//table tasks
has_many :records, dependent: :destroy
-------------
id | name |
-------------
1 | Buy |
-------------
2 | Sell |
-------------
3 | Create |
-------------
//table records
belongs_to :task, required: true
belongs_to :user, required: true
-------------------------------------
id | user_id | task_id | created_at |
-------------------------------------
1 | 2 | 2 | timestamps |
-------------------------------------
2 | 1 | 1 | timestamps |
-------------------------------------
3 | 1 | 2 | timestamps |
-------------------------------------
提前致谢
解决方案
而不是查询用户直接使用user_id
任务名称查询记录表然后执行3 group_by
(s)首先group_by
是任务创建的年份第二个group_by是月份,第三个是任务名称
查找任务的代码
records = Record.joins(:user, :task).where(users: {id: @user.id}, tasks: {name: ["Buy", "Sell", "Create"]})
records_grouped_by_year = records.group_by { |e| e.created_at.year }.sort.to_h
# now you will get a list of tasks in a hash with keys as years { 2018 => [record1, record2, record3, record4]}
records_grouped_by_year_month = records_grouped_by_year.transform_values { |records| records.group_by { |r| r.created_at.strftime("%B") }) }
# now you will get a hash inside a hash first key is year second key is month the record was created in { 2018 => {"January"=>[record1, record2, record4], "August" => [record3]}
records_grouped_by_year_month_name = records_grouped_by_year_month.transform_values { |records| records.transform_values { |record| tasks.group_by { |r| r.taks.name } } }
# now you will get 3 nested hashes first key is year second key is month and third key is the record name { 2018 => {"January"=>{"Sell=>[record1, record2], "Buy=>[record4]}, "August" => { "Buy" => [record3]}}}
(records.key.first..Time.current.year).each { |year| records_grouped_by_year_month_name[year] ||= {} }
# now it will add an empty hash for each year that didn't exists in the records
在您看来:
<% task_count_grouped_by_year_grouped_by_name.each do |year, data_by_month| %>
<h1> <%= year %> </h1>
<% if data_by_month.blank? %>
<%= there was not sale on this year %>
<% else %>
<% data_by_month.each do |month, data_by_name| %>
<h2> <%= month %> </h2>
<% data_by_name.each do |task_name, records| %>
<br /> <bold><%= task_name %></bold> -> (<%= records.count %>) <% #
you have the records here as an array so you can do whatever you want with them %>
<% end %>
<% end %>
<% end %>
<% end %>