SQL / ActiveRecord Query for Multiple Rows on Multiple Columns

Question

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.

Have you made any attempts to sort this issue?

hd1
– hd1

2015-09-22 22:23:43 +00:00
Commented Sep 22, 2015 at 22:23 — hd1
– hd1, Commented Sep 22, 2015 at 22:23

ahmacleod · Accepted Answer · 2015-09-22 22:53:59Z

2

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)

answered Sep 22, 2015 at 22:53

ahmacleod

4,31022 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kendall · Accepted Answer · 2015-09-22 23:52:47Z

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

Collectives™ on Stack Overflow

SQL / ActiveRecord Query for Multiple Rows on Multiple Columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related