Where condition with rails

Question

I'm trying to get this query in my controller.

select employer_id, count(*) from employees where termination_date is null group by employer_id order by employer_id;
 employer_id | count 

-------------+-------
           1 |   592
           2 |  2092
           3 |   565
           4 |  3589
           7 |  4233
           8 |   540
          10 |  1035
          11 |  3287
(8 rows)

I have this code in the controller and I get all the result with for.each

@employers = Employer.all.includes(:employees)

I just trying the add the .where(... employees where termination_date is null )

 <tbody>
    <% @employers.each do |employer| %>
        <tr>
            <td><%= employer.name %></td>
            # I want the result here
            <td><%= employer.employees.size() %></td>
            <td></td>
        </tr>
    <% end %>
    </tbody>

Answer 1

You can write as :

Employee.where(termination_date: nil)
        .group(:employer_id)
        .count
# => [{employer_id_1: count_1}, {employer_id_2: count_2}...]

Update :

If you need the employer names and count the employee per employer, you might join the 2 tables :

@employers = Employee.joins(:employer)
                      .where(termination_date: nil)
                      .group("employers.name")
                      .count

To test the query I have created the relevant tables with some sample data. And the tables data looks like :

[arup@app]$ rails db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .header on
sqlite> .mode column
sqlite> select * from employees;
id          name        created_at                  updated_at                  employer_id
----------  ----------  --------------------------  --------------------------  -----------
1           arup        2015-02-13 20:22:41.174700  2015-02-13 20:22:41.510749  2
2           arun        2015-02-13 20:22:41.362186  2015-02-13 20:22:41.513432  2
3           Joy         2015-02-13 20:23:27.533335  2015-02-13 20:23:27.677737  3
sqlite> select * from employers;
id          name        created_at                  updated_at
----------  ----------  --------------------------  --------------------------
2           ABC         2015-02-13 20:22:40.978798  2015-02-13 20:22:40.978798
3           XYZ         2015-02-13 20:23:27.086210  2015-02-13 20:23:27.086210
sqlite>

Now, I open the rails console and got the count of employees per employer as I wanted. See the rails console output :

>> Employee.joins(:employer).where.not(name: nil).group("employers.name").count
   (0.5ms)  SELECT COUNT(*) AS count_all, employers.name AS employers_name FROM "employees" INNER JOIN "employers" ON "employers"."id" = "employees"."employer_id" WHERE ("employees"."name" IS NOT NULL) GROUP BY employers.name
=> {"ABC"=>2, "XYZ"=>1}

Now, You can write your views as :

<tbody>
    <% @employers.each do |employer_name, employees_count| %>
        <tr>
            <td>
                <%= employer_name %>
            </td>
            # I want the result here
            <td>
                <%= employees_count %>
            </td>
            <td></td>
        </tr>
        <% end %>
</tbody>

Answer 2

You can add Arel wheres on to the employees collection.

employer.employees.where(termination_date: nil).size

One note, the above code will result in a query for each row like

select count(*) from employees where employer_id = ? and termination_date is null

So you wind up with an n+1 query situation, which may be a problem if you have lots of rows.