ActiveRecord finding record by relations requiring all attributes

Question

Ok, so I am new to ruby and rails and it will probably show with my question.

I am writing a for fun app and have a particularly strange question in regards to finding records in a HABTM relationship. The underlying database is Postgresql

My models look like the following

class Family < ActiveRecord::Base
  has_and_belongs_to_many :persons
end

class Person < ActiveRecord::Base
  has_and_belongs_to_many :families
end

Lets say I have 4 people 'Joe', 'Jane', 'Mary', 'Ben'. They can all belong to multiple families.

Family 1 << 'Joe', 'Jane', 'Ben'

Family 2 << 'Jane', 'Ben'

Family 3 << 'Joe', 'Jane', 'Ben', 'Mary'

I would like to be able to find families by searching for their names

This is what my query currently looks like

Family.joins(:persons).where(persons: {name:['Joe','Jane','Ben']})

This works great at finding all records that either have Joe or Jane or Ben (all the families) but not at returning records that only have Joe, Jane, and Ben.

In my example, I am looking to find only Family 1, not the other 2.

How can I make sure that I am only finding records that have all of the names, no more, no less.

Is there a better query to write or should I rethink my database structure?

Answer 1

I would set up models this way:

class Family < ActiveRecord::Base
  has_many :persons
end

class Person < ActiveRecord::Base
  belongs_to :family
end

Then add family_id to families table.

Now you can call family.first.persons

Or

Family.where(id: 1).first.persons

Also if you don't find this method suitable, then I'd recommend using has_many :through instead of has_many_and_belongs_to

Here's more info: http://guides.rubyonrails.org/association_basics.html#the-has-many-through-association

Answer 2

So after some digging and some help on the #rubyonrails irc, I came up with this solution.

class Family < ActiveRecord::Base

  def self.find_by_person_names(persons = [])
    family = nil
    families = Family.joins(:persons).where(persons: {name:persons})
    families.each do |f|
      if f.persons.names.sort == persons.sort
        family = f
      end
    end
    return family
  end
end

Answer 3

I also think a has_many through relationship is more suited: has_and_belongs_to_many vs has_many through

Here's an option that doesn't require filtering the results in ruby:

1. Select all FamilyPerson objects that have each of the desired people in it
2. Select all FamilyPerson objects that have people other than the desired people
3. Select all Family objects with ids in the first query results and not the second - in other words, those Families that have all the necessary people and no more

Implementation

app/models/family.rb

class Family < ActiveRecord::Base
  has_many :family_people
  has_many :people, through: :family_people

  def self.all_with_members members
    member_ids = members.collect {|m| m.id }

    # FamilyPersons that have each of the members in it
    enough = FamilyPerson.where(person_id: member_ids).group("family_id").having("count(person_id) >= ?", member_ids.count).collect { |fp| fp.family_id }

    # FamilyPersons that have no additional members
    too_many = FamilyPerson.where("person_id NOT IN(?)", member_ids).group("family_id").having("count(person_id) > 0").collect { |fp| fp.family_id }

    Family.find enough - too_many
  end
end

app/models/person.rb

class Person < ActiveRecord::Base
  has_many :family_people
  has_many :families, through: :family_people
end  

app/models/family_person.rb

class FamilyPerson < ActiveRecord::Base
  belongs_to :family
  belongs_to :person
end

Excerpt from db/schema.rb

create_table "families", force: true do |t|
  t.string "name"
end

create_table "family_people", force: true do |t|
  t.integer "person_id"
  t.integer "family_id"
end

create_table "people", force: true do |t|
  t.string "name"
end

Testing

spec/models/family_spec.rb

to test with RSpec

require 'rails_helper'

describe Family do
  before :each do
    Rails.application.load_seed
  end

  describe "self.all_with_members" do
    it "returns all families that have each person in it" do
      people = [Person.find_by_name("Joe"), Person.find_by_name("Jane"), Person.find_by_name("Ben")]
      expect(Family.all_with_members(people).to_a).to eq Family.where(name: "Family 1").to_a
    end
  end
end

db/seeds.rb

Seed data

Family.destroy_all
Person.destroy_all
FamilyPerson.destroy_all

joe = Person.create name: "Joe"
jane = Person.create name: "Jane"
ben = Person.create name: "Ben"
mary = Person.create name: "Mary"

family_one = Family.create name: "Family 1"
family_two = Family.create name: "Family 2"
family_three = Family.create name: "Family 3"

family_one.people << joe
family_one.people << jane
family_one.people << ben
family_one.save!

family_two.people << jane
family_two.people << ben
family_two.people << mary
family_two.save!

family_three.people << joe
family_three.people << jane
family_three.people << ben
family_three.people << mary
family_three.save!