Fetch records using where clause on joined table

Question

I have a model Voting that has a has_many relation to another model GroupVoting. The tables look like this:

Voting      GroupVoting
- id        - id 
            - voting_id
            - party_id
            - yes     [int]
            - no      [int]
            - abstain [int]
            - absent  [int]

My question is this: What query should I write to fetch all votings on which a party A has N votings "yes" and another party has M votes "no", third party... and so on? (We are given the party ids, the vote type to count and the minimum vote count)

So far my code looks like this:

gv = GroupVoting.arel_table
Voting.joins(:group_votings)
  .where(
       (gv[:party_id].eq(9).and(gv[:yes].gt(30)))
    .or(gv[:party_id].eq(115).and(gv[:yes].gt(80)))
    .or(gv[:party_id].eq(16).and(gv[:no].gt(60))))
 .group("voting_id").having("count(*)>2").count

and the corresponding sql query looks like this:

SELECT COUNT(*) AS count_all, voting_id AS voting_id FROM "votings"
INNER JOIN "group_votings" ON "group_votings"."voting_id" = "votings"."id"
WHERE ((("group_votings"."party_id" = 9 AND "group_votings"."yes" > 25 OR "group_votings"."party_id" = 115 AND "group_votings"."yes" > 60) OR "group_votings"."party_id" = 16 AND "group_votings"."no" > 30))
GROUP BY voting_id HAVING count(*)>2

This code seems to work but the result is in the form of:

{11666=>3, 11667=>3, 1835=>3}

And I want it in the form of:

ActiveRecord[Voting..., Voting..., ...]

Thank you in advance. :)

Does either table votings or group_votings relate voting_Id to the text "Voting"? if not then you'll need a case statement on voting_ID and manually assign the text... — xQbert, Jul 09 '14 at 19:09
Hi xQbert. I am not sure I understand your question. GroupVoting has voting_id that references the voting model. One voting has many group_votings. — anonymous, Jul 09 '14 at 19:12
I'm trying to figure out how the 3 gets translated to "Voting" 3 I assume is the voting_Id, but I don't know how the database is to know that 3 means Voting and 4 means something else... etc. — xQbert, Jul 09 '14 at 19:21

score 0 · Answer 1 · answered Jul 09 '14 at 19:22

0

You have a count at the end of your clause which won't return the objects but a count for each id. Remove that and you should get the objects back.

answered Jul 09 '14 at 19:22

CWitty

4,488
3
23
40

Hi CWitty. If I remove the `count` I get the following error: `PG::GroupingError: ERROR: column "votings.id" must appear in the GROUP BY clause or be used in an aggregate function`. I guess it is because of the inner workings of rails. – anonymous Jul 09 '14 at 19:25
I think you can drop the group and having unless Im not understanding the full need. – CWitty Jul 09 '14 at 19:34
You can also try changing `group_by(:voting_id)` to `group_by(:id)` – CWitty Jul 09 '14 at 19:42

Fetch records using where clause on joined table

1 Answers1