CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
user1925223
user1925223

Reputation: 35

SQL / ActiveRecord Query for Multiple Rows on Multiple Columns

Let's say that I have two ActiveRecord models (or two tables in an SQL database, if you'd like). I have a User class and a Poll class. A User has_many Polls. When a user takes a Poll, they are asked whether or not they like apples, bananas, and cherries. The User's response to each of these things is recorded in a boolean column.

I'm interested in finding out which users have taken Polls and have responded that they like each of apples, bananas, and cherries at ANY time (not necessarily at the same time). Put differently, they do not need to have said that they like all three fruits during one Poll, they just need to have said that they like each of these things at some point, i.e. they need to have said that they like apples in at least one Poll, bananas in at least one Poll, and cherries in at least one Poll.

What's the right way to go about doing this in a general manner? Either an ActiveRecord or SQL query would be helpful.

Upvotes: 1

Views: 308

Answers (2)

Kendall
Kendall

Reputation: 381

Or perhaps your table looks like this http://sqlfiddle.com/#!15/c0967/10:

create table polls (userid char(10), pollid char(10), questionid char(10), response boolean);

insert into polls (userid, pollid, questionid, response)
values ('1','1','APPLES',true),
       ('1','1','BANANAS',false),
       ('1','1','CHERRIES',true),
       ('1','2','APPLES',false),
       ('1','2','BANANAS',false),
       ('1','2','CHERRIES',true),
       ('1','3','APPLES',false),
       ('1','3','BANANAS',true),
       ('1','3','CHERRIES',true),
       ('2','1','APPLES',false),
       ('2','1','BANANAS',false),
       ('2','1','CHERRIES',true),
       ('2','2','APPLES',false),
       ('2','2','BANANAS',true),
       ('2','2','CHERRIES',true),
       ('2','3','APPLES',false),
       ('2','3','BANANAS',false),
       ('2','3','CHERRIES',true),
       ('3','1','APPLES',true),
       ('3','1','BANANAS',false),
       ('3','1','CHERRIES',false),
       ('3','2','APPLES',false),
       ('3','2','BANANAS',true),
       ('3','2','CHERRIES',false),
       ('3','3','APPLES',false),
       ('3','3','BANANAS',false),
       ('3','3','CHERRIES',true),
       ('4','1','APPLES',false),
       ('4','1','BANANAS',false),
       ('4','1','CHERRIES',false),
       ('4','2','APPLES',true),
       ('4','2','BANANAS',true),
       ('4','2','CHERRIES',true),
       ('4','3','APPLES',false),
       ('4','3','BANANAS',false),
       ('4','3','CHERRIES',false)
;

in which case you can create a temporary view grouping the user questions with true responses, and then count the records in that view like this:

with counts as (
  select userid, questionid
  from polls
  where response = true
  and questionid in ('APPLES','BANANAS','CHERRIES')
  group by userid, questionid
  )
select userid
from counts
group by userid
having count(*) = 3

Upvotes: 1

ahmacleod
ahmacleod

Reputation: 4310

Lets suppose your polls table has boolean columns apples, bananas, and cherries. Then for each user, you want to look at the corresponding polls and compute an aggregate OR function on each fruit column, keeping only the rows that return true for each fruit.

User.joins(:polls)
    .group('users.id')
    .having('BOOL_OR(polls.apples) = ? AND BOOL_OR(polls.bananas) = ? AND BOOL_OR(polls.cherries) = ?', true, true, true)

Upvotes: 2

Related Questions