Group_By in Rails by 2 or More Attributes

group_by in rails by 2 or more attributes

Why not do:

@bunch.group_by{|e| [e.commentable_id, e.commentable_type]}

group_by multiple times in ruby

I think following will work for you,

events = [
  { name: 'Event 1', date: '2019-02-21 08:00:00', area: 'South', micro_area: 'A' }
]

events.group_by { |x| x[:date] }.transform_values do |v1|
  v1.group_by { |y| y[:area] }.transform_values do |v2|
    v2.group_by { |z| z[:micro_area] }
  end
end
# {
#   "2019-02-21 08:00:00"=>{
#     "South"=>{
#       "A"=>[
#         {:name=>"Event 1", :date=>"2019-02-21 08:00:00", :area=>"South", :micro_area=>"A"}
#       ]
#     }
#   }
# }   

Is there a Ruby/Rails clause for grouping by an attribute and finding groups with two different attributes?

You can achieve this in SQL using an inner join:

SELECT DISTINCT sandwiches.user_id
FROM sandwiches
INNER JOIN sandwiches sandwiches_alias
  ON sandwiches.user_id = sandwiches_alias.user_id
WHERE sandwiches.topping = "jelly"
  AND sandwiches_alias.topping = "peanut butter";

This will return just the relevant user_ids.

With this data, you can make your grouping query much simpler:

user_ids = Sandwich.connection.execute(the_above_sql).flatten
Sandwich.where(user_id: user_ids).group(:user_id)

Rails Query - Group By with 2 groups

You could write your query :

User.group("lower(localidade)")
    .select("CASE WHEN lower(localidade) = 'new york' THEN COUNT(id) END AS NewYork, 
             CASE WHEN lower(localidade) != 'new york' THEN COUNT(id) END AS Non-NewYork")

Since 9.4, you can use FILTER with aggregate expression :

User.group("lower(localidade)")
    .select("COUNT(id) FILTER (WHERE lower(localidade) != 'new york') AS NonNewyork, 
             COUNT(id) FILTER (WHERE lower(localidade) = 'new york') AS Newyork")

I created a Table to explain and test the above sql, and they worked as expected :

[shreyas@rails_app_test (master)]$ rails db
psql (9.4.1)
Type "help" for help.

app_development=# select id, location, name from people;
 id | location | name
----+----------+------
  2 | X        | foo
  3 | X        | foo
  4 | Y        | foo
(3 rows)

app_development=# SELECT COUNT(id) FILTER(WHERE lower(location) != 'x') AS Non_X_loc, COUNT(id) FILTER (WHERE lower(location) = 'x') AS X_loc FROM "people";
 non_x_loc | x_loc
-----------+-------
         1 |     2
(1 row)

Let me now, jump to the rails console, and test the equivalent Rails code :

[2] pry(main)> p = Person.select("COUNT(id) FILTER(WHERE lower(location) != 'x') AS Non_X_loc, COUNT(id) FILTER (WHERE lower(location) = 'x') AS X_loc ")
  Person Load (0.5ms)  SELECT COUNT(id) FILTER(WHERE lower(location) != 'x') AS Non_X_loc, COUNT(id) FILTER (WHERE lower(location) = 'x') AS X_loc  FROM "people"
=> [#<Person:0x007fd85ed71980 id: nil>]
[3] pry(main)> p.first.attributes
=> {"id"=>nil, "non_x_loc"=>1, "x_loc"=>2}
[6] pry(main)> Person.group("lower(location)").select("CASE WHEN lower(location) = 'x' THEN COUNT(id) END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) END AS Non_X_loc")
  Person Load (0.6ms)  SELECT CASE WHEN lower(location) = 'x' THEN COUNT(id) END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) END AS Non_X_loc FROM "people" GROUP BY lower(location)
=> [#<Person:0x007fd8608281e8 id: nil>, #<Person:0x007fd860828008 id: nil>]
[7] pry(main)> p = _
=> [#<Person:0x007fd8608281e8 id: nil>, #<Person:0x007fd860828008 id: nil>]
[8] pry(main)> p.map { |rec| rec.attributes }
=> [{"id"=>nil, "x_loc"=>nil, "non_x_loc"=>1}, {"id"=>nil, "x_loc"=>2, "non_x_loc"=>nil}]
[9] pry(main)> p.map { |rec| rec.attributes.except('id') }
=> [{"x_loc"=>nil, "non_x_loc"=>1}, {"x_loc"=>2, "non_x_loc"=>nil}]

Update

You can remove those nil from DB level only :

Rails code :

[shreyas@rails_app_test (master)]$ rails c
Loading development environment (Rails 4.2.0)
[1] pry(main)> Person.group("lower(location)").select("CASE WHEN lower(location) = 'x' THEN COUNT(id) ELSE 0 END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) ELSE 0 END AS Non_X_loc")
  Person Load (0.9ms)  SELECT CASE WHEN lower(location) = 'x' THEN COUNT(id) ELSE 0 END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) ELSE 0 END AS Non_X_loc FROM "people" GROUP BY lower(location)
=> [#<Person:0x007fd858c100b0 id: nil>, #<Person:0x007fd860853e88 id: nil>]
[2] pry(main)> p = _
=> [#<Person:0x007fd858c100b0 id: nil>, #<Person:0x007fd860853e88 id: nil>]
[3] pry(main)> p.map { |rec| rec.attributes }
=> [{"id"=>nil, "x_loc"=>0, "non_x_loc"=>1}, {"id"=>nil, "x_loc"=>2, "non_x_loc"=>0}]
[4] pry(main)> p.map { |rec| rec.attributes.except('id') }
=> [{"x_loc"=>0, "non_x_loc"=>1}, {"x_loc"=>2, "non_x_loc"=>0}]
[5] pry(main)> p = Person.select("count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, count(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc").group("lower(location)")
  Person Load (0.9ms)  SELECT count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, count(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc FROM "people" GROUP BY lower(location)
=> [#<Person:0x007fd85b150f78 id: nil>, #<Person:0x007fd85b150230 id: nil>]
[6] pry(main)> p.map { |rec| rec.attributes.except('id') }
=> [{"x_loc"=>0, "non_x_loc"=>1}, {"x_loc"=>2, "non_x_loc"=>0}]

SQL

app_development=# select CASE WHEN lower(location) = 'x' THEN COUNT(id) ELSE 0 END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) ELSE 0 END AS Non_X_loc from people group by lower(location);
 x_loc | non_x_loc
-------+-----------
     0 |         1
     2 |         0
(2 rows)
app_development=# select count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, count(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc from people group by lower(location);
 x_loc | non_x_loc
-------+-----------
     0 |         1
     2 |         0
(2 rows)

Update- II

The classical approach to get the output same as FILTER :

app_development=# select count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, sum(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc from people;
 x_loc | non_x_loc
-------+-----------
     2 |         1
(1 row)

app_development=# select sum(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, sum(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc from people;
 x_loc | non_x_loc
-------+-----------
     2 |         1
(1 row)

app_development=# select id, location, name from people;
 id | location | name
----+----------+------
  2 | X        | foo
  3 | X        | foo
  4 | Y        | foo
(3 rows)

app_development=#

And In Rails way :-

Loading development environment (Rails 4.2.0)
[1] pry(main)> p = Person.select("sum(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, sum(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc")
  Person Load (0.6ms)  SELECT sum(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, sum(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc FROM "people"
=> [#<Person:0x007fd85b6e6a78 id: nil>]
[2] pry(main)> p.first.attributes.except("id")
=> {"x_loc"=>2, "non_x_loc"=>1}
[3] pry(main)> p = Person.select("count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, count(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc")
  Person Load (0.5ms)  SELECT count(CASE WHEN lower(location) = 'x' THEN 1 END) AS X_loc, count(CASE WHEN lower(location) != 'x' THEN 1 END) AS Non_X_loc FROM "people"
=> [#<Person:0x007fd85b77f098 id: nil>]
[4] pry(main)> p.first.attributes.except("id")
=> {"x_loc"=>2, "non_x_loc"=>1}
[5] pry(main)>

---

Related Topics