Group_by - Ruby/Rails + Postgres

Question

I am pretty new to ROR and Postgre and i'm in trouble to achieve this.

I have a Working_hour Model and a Merchant Model, where merchant has_many working_hours and working_hour belongs to Merchant. The merchant can have two or mores working_hours for the same day.

My view:

 <% @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 display at the view ordered by day the data retrieved are (note that the opening hour are unordered):

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

I want to group by day of week and ordering first by day of week and second by 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, currently, I'm using the ruby layer to do that what brings performance issues. How can achieve this using the database layer?

try searching - postgres group by week - i had the same issue with mysql and after reading some articles then learned more of sql and i did it, then in rails its just custom select method on class level... — Jakub Kuchar
– Jakub Kuchar, Commented Oct 5, 2016 at 4:22
Are you having any default_scope which is messing with the order? — Pavan
– Pavan, Commented Oct 5, 2016 at 5:17

Kristo Mägi · Accepted Answer · 2016-10-05 06:54:56Z

2

+25

Quick Postgres example if you're willing to store the data in DB table (on randomly created 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 links that might come 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

P.S. Hopefully gives some ideas how to solve it in database.

answered Oct 5, 2016 at 6:54

Kristo Mägi

1,70413 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1301037 Over a year ago

How can I call this on rails? Using Scope? Can you give me an example how to abstract this query to rails?

Kristo Mägi Over a year ago

@user1301037 – I'm not expert on Ruby, but ain't using Activerecord to execute SQL help on this like given example(s) here: stackoverflow.com/questions/14824453/rails-raw-sql-example ?

Minu · Accepted Answer · 2016-10-06 05:19:56Z

2

The working hour should be ordered by opening_hour as you will show the opening hour in UI in ascending order. Once 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 %>

edited Oct 6, 2016 at 5:19

answered Oct 5, 2016 at 11:08

Minu

1015 bronze badges

1 Comment

Smar Over a year ago

You should explain how this answer answers the question.

Oleksandr Avoiants · Accepted Answer · 2016-10-12 10:13:30Z

2

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

edited Oct 12, 2016 at 10:13

answered Oct 8, 2016 at 17:48

Oleksandr Avoiants

1,94917 silver badges25 bronze badges

Comments

Sergey Burtsev · Accepted Answer · 2016-10-12 00:05:57Z

0

Why you just can't order by two fields: by "day" and by "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 %>

answered Oct 12, 2016 at 0:05

Sergey Burtsev

1013 bronze badges

Collectives™ on Stack Overflow

Group_by - Ruby/Rails + Postgres

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related