How can I intersect two ActiveRecord::Relations on an arbitrary column?

Question

If I have a people table with the following structure and records:

drop table if exists people;
create table people (id int, name varchar(255));

insert into people values (1, "Amy");
insert into people values (2, "Bob");
insert into people values (3, "Chris");

insert into people values (4, "Amy");
insert into people values (5, "Bob");
insert into people values (6, "Chris");

I'd like to find the intersection of people with ids (1, 2, 3) and (4, 5, 6) based on the name column.

In SQL, I'd do something like this:

select
  group_concat(id),
  group_concat(name)
from people
group by name;

Which returns this result set:

id  | name
----|----------
1,4 | Amy,Amy
2,5 | Bob,Bob
3,6 | Chris,Chris

In Rails, I'm not sure how to solve this.

My closest so far is:

a = Model.where(id: [1, 2, 3])
b = Model.where(id: [4, 5, 6])

a_results = a.where(name: b.pluck(:name)).order(:name)
b_results = b.where(name: a.pluck(:name)).order(:name)

a_results.zip(b_results)

This seems to work, but I have the following reservations:

Performance - is this going to perform well in the database?
Lazy enumeration - does calling #zip break lazy enumeration of records?
Duplicates - what will happen if either set contains more than one record for a given name? What will happen if a set contains more than one of the same id?

Any thoughts or suggestions?

Thanks

Check out http://stackoverflow.com/questions/28146848/how-can-i-use-group-concat-in-rails — Prakash Murthy, May 24 '15 at 14:24

score 0 · Answer 1 · answered May 24 '15 at 14:31

You can use your normal sql method to get this arbitrary column in ruby like so:

@people = People.select("group_concat(id) as somecolumn1, group_concat(name) as somecolumn2").group("group_concat(id), group_concat(name)")

For each record in @people you will now have somecolumn1/2 attributes.

How can I intersect two ActiveRecord::Relations on an arbitrary column?

1 Answers1