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

Question

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?

If you want to count how many votes for "John Doe" then somewhere in your command for `sp = Serviceprovider...` you're going to need to mention `'John Doe'` but you don't. — lurker, Feb 19 '15 at 16:02
Can you provide some more info about your schema, and exactly what you want? What is "Jhon Doe" here - is he a ServiceProvider? If so then you will probably want to say something like `@service_provider = ServiceProvider.find_by_id(params[:id]); @vote_count = @service_provider.votes.count` — Max Williams, Feb 19 '15 at 16:04
Thank you sir. What if I want to count each votes in serviceprovider and get the top 10 highest votes? — Juan Dela Cruz, Feb 19 '15 at 16:11

score 4 · Accepted Answer · edited Sep 29 '16 at 22:36

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

Thank you for your answer sir but I got an error SyntaxError: (irb):47: syntax error, unexpected tCONSTANT, expecting ')' ...("count(serviceprovider_id)" AS vote_count).order("vote_coun... — Juan Dela Cruz, Feb 19 '15 at 16:26
Sorry my friend .. :) I got an error again ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "AS" LINE 1: SELECT COUNT(count(serviceprovider_id) AS vote_count) AS cou... It seems like "AS" has a problem. I'm using rails 4.1 is it valid? — Juan Dela Cruz, Feb 19 '15 at 16:36
@JuanDelaCruz there might be silly mistakes.. I'm working on it.. Just give some moments.. — Arup Rakshit, Feb 19 '15 at 16:37
Thank you very much. I got it. I also created my own solution using SELECT but yours is much better. :) — Juan Dela Cruz, Feb 19 '15 at 17:07

score 0 · Answer 2 · answered Feb 19 '15 at 16:07

0

Try this

sp = ServiceProvider.find_by_name("Jhon Doe");  
@votes = sp.votes.count

answered Feb 19 '15 at 16:07

Sonalkumar sute

2,565
2
17
27

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

2 Answers2

Linked