Juan Dela Cruz Juan Dela Cruz - 2 months ago 9
Ruby Question

How I can count the total votes in worker table in Rails

I'am confused when I try to count the total votes in my votes table in rails..

serviceproviders has_many votes
votes belongs_to serviceproviders


I tried like this :

sp = Serviceprovider.joins(:votes).group_by(&:id).count


but it doesn't get the right output.

example output I want is:

If in the table Jhon Doe has 5 row of votes in the table, I can get the total 5 votes when I query. Can any give me the idea how can execute the query. Thank you!

Update:

Thank you for those answers.

I tried this in my rails c.

vote = Vote.joins(:serviceprovider).group(:serviceprovider_id).count


and I got the results:
{108=>2, 109=>1}


My question how can I get the top 10 highest votes?

Answer

Here is the table:

app_development=# select * from votes;

 id |  city   |         created_at         |         updated_at         | service_provider_id
----+---------+----------------------------+----------------------------+---------------------
  1 | B\'lore  | 2015-02-19 17:35:58.061324 | 2015-02-19 17:35:58.083479 |                   3
  2 | Kol     | 2015-02-19 17:35:58.103013 | 2015-02-19 17:35:58.123405 |                   2
  3 | Mum     | 2015-02-19 17:35:58.11242  | 2015-02-19 17:35:58.125345 |                   2
  4 | Kochin  | 2015-02-19 17:35:58.136139 | 2015-02-19 17:35:58.167971 |                   1
  5 | Mum     | 2015-02-19 17:35:58.145833 | 2015-02-19 17:35:58.170319 |                   1
  6 | Chennai | 2015-02-19 17:35:58.156755 | 2015-02-19 17:35:58.171996 |                   1
(6 rows)

app_development=# select * from service_providers;

 id | name |         created_at         |         updated_at
----+------+----------------------------+----------------------------
  1 | MTS  | 2015-02-19 17:35:57.837508 | 2015-02-19 17:35:57.837508
  2 | HCL  | 2015-02-19 17:35:57.923479 | 2015-02-19 17:35:57.923479
  3 | ACL  | 2015-02-19 17:35:57.934414 | 2015-02-19 17:35:57.934414

You need the following query to obtain the desired result :

Vote.joins(:service_provider)
    .group(:service_provider_id)
    .order("count_all desc")
    .limit(10)
    .count

Tested in Rails console :

[arup@app]$ rails c
Loading development environment (Rails 4.1.1)
[1] pry(main)> Vote.joins(:service_provider).group(:service_provider_id).order("count_all desc").limit(2).count
   (2.0ms)  SELECT  COUNT(*) AS count_all, service_provider_id AS service_provider_id FROM "votes" INNER JOIN "service_providers" ON "service_providers"."id" = "votes"."service_provider_id" GROUP BY service_provider_id  ORDER BY count_all desc LIMIT 2
=> {1=>3, 2=>2}
[2] pry(main)>
Comments