0

I have 3 tables/models Participants, Demographics, ParticipantDemographics.

I would like to find Participants who have a combination of Demographics.

Here is a simple example:

Lets presume in some UI I am trying to filter a list of participants. I select the following demographic choices. Gender: Male (id=1), Age: 21-30 (id=7), Age: 31-40 (id=8)

This indicates I want Males within the age group 21-30 or 31-40

I would like find participants who have a participant_demographic record where the demographic_id is either 7 or 8, and they also have a participant_demographic record where the demographic_id is 1.

What does this SQL statement look like and is there anything in ActiveRecord that can make this select look nicer or be chained more easily.

Thank You

Edit: There seems to be some confusion as to why I would use a HABTM. In this system the end user can create any demographics they would like to track. Not just ones I've predefined and put on a table as columns. For example a user might want to start tracking the "spoken language" of the participants. So they would add the "spoken language" category and demographics for each "english", "spanish", "german", "etc". Because people can speak many languages I could link through the join table a participant to both english and spanish. This is why the join table is necessary.

user204777
  • 93
  • 1
  • 1
  • 5
  • Your conceptual model appear to need more work first. Are you for example encoding a survey where a participant has submitted one survey response? If so creating a Rails query over all participants is straight forward. However I'm confused as to why the join table is needed. – Matt Stevens Apr 16 '15 at 03:50
  • Without the join table I couldn't add demographics without migrating the participants table to contain additional columns. For example let say we want to start tracking smokers/non-smokers. I could add a "smoker" column on participants. But that doesn't work if I want the end user to be able to added there own demographics to track. I would instead need to add those two values as demographic records and add a join record to each participant who is a smoker or non-smoker. – user204777 Apr 16 '15 at 14:01

3 Answers3

0

Making the assumption that your use case is a participant has one demographic (set of details contained in one model)

class Participant < ActiveRecord::Base
    has_one :demographic
end

class Demographic < ActiveRecord::Base
    belongs_to :participant
end

The create_table for demographics, needs to contain t.belongs_to :participant to create a foreign key which Rails uses to identify the relationship.

Now to get your answer:

my_array = Parcipant.demographic.where("gender = '%s' AND age = '%s' AND age = '%s'", gender_id, age_id_A, age_id_B )

reference here for instructions on the .where clause.

Matt Stevens
  • 1,104
  • 1
  • 12
  • 24
  • This doesn't work if I want the end user to be able to add their own demographics, hence the HABTM. In your scenario a new demographic could only be added by migrating additional columns on to the demographic table/model. – user204777 Apr 16 '15 at 14:06
  • :-) I should have waited for your answer to my question seeking clarification. I will add a new solution. – Matt Stevens Apr 16 '15 at 21:01
0

Ok, with clarification each Demographic represents a Participant trait. I have reservations whether this is ideal, for example your question then implies you have a Demographic model for Male and a second Demographic model for Female. However that is detail and doesn't change the substance of how to set the relationship between Participants and Demographics, whatever demographics represents.

A Participant has and belongs to many Demographics and a Demographic has and belongs to many Participants.

Two models are needed.

class Participant < ActiveRecord::Base
    has_and_belongs_to_many :demographics
end

class Demographic < ActiveRecord::Base
    has_and_belongs_to_many :participants
end

Three tables are needed, here in one migration but you may prefer seperat migrations.

class SampleMigration < ActiveRecord::Migration

  def change
    create_table :participants do |t|
        t.string :name
    end

    create_table :demographics do |t|
        t.string  :name
    end

    create_table :demographics_participants, id: false do |t|
       t.belongs_to :demographic
       t.belongs_to :participant
    end
  end
end

Take care with plurals, Rails expects them. They appear here when referring to for example has_and_belongs_to_many :participants and also naming tables create_table :demographics. In addition note the join table name is both pluralised and in alphabetical order. If you place the names in reverse order Rails will not find the table.

participant.demographics will now return an array of Demographic models that your participant has. While demographic.participants returns an array of Participant models. For example Demographic.find(1).participants will return an array of Participant models that are all Male.

Refer to this Rails guide for advice on how to create more complex queries.

Matt Stevens
  • 1,104
  • 1
  • 12
  • 24
0

Thanks for the effort Matt. I probably should not have asked this with emphasis on acitve record. I think that has lead you to believe I'm a ruby/rails novice. Thats not the case. The difficulty in this problem lies in the sql. I need to find participants that match a combination of demographics. The solution you have provided only allows for finding those that match one. Below is the current AR query I'm using. This query will work so long as a participant can't have two or more demographics from one category, like in the spoken language example above. This is because the count value in the having clause is no longer accurate if a participant matches multiple demographics from a category. 

Participants.joins(:participant_demographics)
.where("participant_demographics.demographic_id = 3 OR participants_demographics.demographic_id = 1 OR participant_demographics.demographic_id =4")
.having('COUNT(participants.id) > 2')
.group('participants.id')
user204777
  • 93
  • 1
  • 1
  • 5