Postgres Join Query on Rails

Question

I have researched a lot and I can't find the answer for my question. I have the following setup on my Rails app:

class Group < ActiveRecord::Base
  has_many :people
  # ...
end

class City < ActiveRecord::Base
  has_many :people
  # ...
end

class Person < ActiveRecord::Base
  belongs_to :city
  belongs_to :group
  # ...
end

The people have the column :role that is 0 or 1.

I want to get all the groups that have at least one person with role == 0 and one person with the role == 1.

Any idea? I'm using Postgres by the way.

user1096557 · Accepted Answer · 2012-07-27 22:22:52Z

1

Here's a query I just tested on my SQLite3 database (should work on Postgres too I believe):

 Group.select("groups.*").joins("LEFT JOIN people on groups.id = people.group_id").where("people.role==0 OR people.role==1").group("id")

Here I assume you've already added the foreign key group_id in your people migration. Hope this helps.

answered Jul 27, 2012 at 22:22

user1096557

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

Collectives™ on Stack Overflow

Postgres Join Query on Rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related