ruby-on-rails – Group_by – Ruby / Rails Postgres
我对ROR和Postgre很新,我很难实现这个目标.
我有一个Working_hour模型和一个商家模型,其中商家has_many working_hours和working_hour属于Merchant.商家可以在同一天工作两小时或多小时. 我的看法: <% @merchant.working_hours.order(:day).group_by(&:day).each do |dia,whs| %> <%= t(:"date.abbr_day_names")[dia.to_i] %> : <% whs.each do |wh| %> <li> <%= wh.oppening_hour.to_formatted_s(:time) %> - <%= wh.close_hour.to_formatted_s(:time) %> </li> <% end %> <% end %> 当我在按日排序的视图中显示检索到的数据时(请注意,开放时间是无序的): Mon: 17:00-20:00 10:00-13:00 Tue: 18:00-21:00 10:00-13:00 我希望按星期几分组,先按星期几按顺序排序,然后按开放时间排序: Mon: 10:00-13:00 17:00-20:00 Tue: 10:00-13:00 18:00-21:00 但正如您所看到的,目前,我正在使用ruby层来实现性能问题.如何使用数据库层实现这一目标? 解决方法
如果您愿意将数据存储在数据库表中(在随机创建的数据集上),请使用Quick Postgres示例:
-- The query: SELECT to_char( mytime,'day' ) as weekday,-- example to get weekday name extract( dow from mytime ) as weekday_num,-- example to get weekday number format( -- format the way example output was given '%s - %s',date_trunc( 'hour',opening_time )::time(0),-- get opening hour (without milliseconds) date_trunc( 'hour',closing_time )::time(0) -- get closing hour (without milliseconds) ) as working_hours FROM mytable GROUP BY mytime,-- to secure accurate ordering by timestamp weekday,working_hours ORDER BY mytime,working_hours; -- Result: weekday | weekday_num | working_hours -----------+-------------+--------------------- monday | 1 | 08:00:00 - 17:00:00 tuesday | 2 | 08:00:00 - 16:00:00 tuesday | 2 | 08:00:00 - 17:00:00 wednesday | 3 | 08:00:00 - 12:00:00 thursday | 4 | 08:00:00 - 12:00:00 thursday | 4 | 08:00:00 - 16:00:00 friday | 5 | 08:00:00 - 15:00:00 friday | 5 | 08:00:00 - 18:00:00 Postgres文档链接可能会派上用场: https://www.postgresql.org/docs/current/static/functions-datetime.html 附:希望能提供一些如何在数据库中解决它的想法. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |