Group_by - Ruby / Rails + Postgres

Question

I am new to ROR and Postgre and I have problems with this.

I have a Work_hour and Merchant Model where the seller has_many working_hours and working_hour are owned by Merchant. A merchant can have two or more business days in one day.

My opinion:

<% @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 %> 

When I show the data received in order of time (note that the opening hour is unordered):

 Mon: 17:00-20:00 10:00-13:00 Tue: 18:00-21:00 10:00-13:00 

I want to group the day of the week and first order the day of the week , and the second - the opening hour :

 Mon: 10:00-13:00 17:00-20:00 Tue: 10:00-13:00 18:00-21:00 

But, as you can see, I am currently using the ruby ​​layer for this, which leads to performance problems. How to achieve this with a database tier?

Answer 1

Quick Postgres example if you want to store data in a DB table (in a randomly generated dataset):

 -- 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 documentation documentation links that may come in handy:

https://www.postgresql.org/docs/current/static/functions-datetime.html https://www.postgresql.org/docs/current/static/functions-formatting.html https: //www.postgresql. org / docs / current / static / functions-string.html # FUNCTIONS-STRING-FORMAT

PS I hope you get some ideas on how to solve this problem in the database.

Answer 2

Work hours must be ordered by opening_hour, because you specify the opening hours in the user interface in ascending order. After the working hour is ordered, the result can be grouped by day.

<% @merchant.working_hours.order(:opening_hour).group_by(&:day).each do |dia, whs| %> <%= t(:"date.abbr_day_names")[dia.to_i] %> : <% whs.each do |wh| %> <li> <%= wh.opening_hour.to_formatted_s(:time) %> - <%= wh.close_hour.to_formatted_s(:time) %> </li> <% end %> <% end %>

Answer 3

 <% day = nil %> <% @merchant.working_hours.order(:day, :oppening_hour).each do |wh| %> <% if wh.day != day %> <% day = wh.day %> <%= t(:"date.abbr_day_names")[day.to_i] %> : <% end %> <li> <%= wh.oppening_hour.to_formatted_s(:time) %> - <%= wh.close_hour.to_formatted_s(:time) %> </li> <% end %> 

Answer 4

Why can't you just order two fields: by "day" and "opening_hour"?

 <% @merchant.working_hours.order(:day).order(:oppening_hour).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 %>