Rails - Searching with multiple parameters

Question

I am developing a system where the user inputs several symptoms and receives a list of all illnesses that match those symptoms. I haven't found a way to do so, however.

For example, if the user has "Fever" and "Sneezing" as symptoms, I want the system to find all illnesses that have both fever and sneezing as symptoms simultaneously and list them in no particular order.

I have three tables: illnesses, symptoms and illness_symptoms. Illnesses and Symptoms both have a has_many relationship through illness_symptoms.

Both Illnesses and Symptoms are similarly mapped: Text, description and id fields. illness_symptoms only has id_symptom and id_illness. Symptoms are "saved" to an illness through a form where one illness and one symptom are specified, though this is rudimentary and will be changed later.

Using @illness = symptom.illnesses I can find and list all illnesses that have one certain symptom but I'm not sure how to narrow it down for a second (and any subsequent) symptoms.

Can you show your Illness and Symptom models? and the schema (to see what attributes are there), specially to see how you save multiple symptoms for a particular illness. — K M Rakibul Islam
– K M Rakibul Islam, Commented Nov 12, 2015 at 5:16
Not sure how useful a direct copy-and-paste would be considering some of it is not in English but edited the original post to explain more of the system. — Tsugihagi
– Tsugihagi, Commented Nov 12, 2015 at 5:20
"Fever" and "Sneezing" are different types of symptoms, right? How is that saved in the database table? as name or description column in the symptoms table? Just show couple of database records from the illness and symptoms tables — K M Rakibul Islam
– K M Rakibul Islam, Commented Nov 12, 2015 at 5:24
Yes, they are two different entries in the Symptoms table. Let's say: id = 1; name = "Fever"; description = "..." and id = 2; name = "Sneezing"; description = "..." — Tsugihagi
– Tsugihagi, Commented Nov 12, 2015 at 5:28

Plamena Gancheva · Accepted Answer · 2015-11-13 09:01:51Z

2

You can use an aggregate function

symptoms = Symptom.find(1,2)

illnesses = Illness.joins(:illness_symptoms)
                    .where("illness_symptoms.symptom_id in (?)", symptoms)
                    .group("illnesses.id")
                    .having("count(illnesses.id) >= ?", symptoms.length)

To answer your comment - querying the database is best done in the model. I would make a scope in the Illness model:

  scope :with_symptoms, -> (symptoms) { joins(:illness_symptoms)
                                                .where("illness_symptoms.symptom_id in (?)", symptoms)
                                                .group("illnesses.id")
                                                .having("count(illnesses.id) >= ?", symptoms.length) }

And use it wherever needed. E.g. Illness.with_symptoms(Symptom.find(1,2))

edited Nov 13, 2015 at 9:01

answered Nov 12, 2015 at 8:34

Plamena Gancheva

7003 silver badges6 bronze badges

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

4 Comments

Tsugihagi Over a year ago

Probably a very simple question (I'm somewhat new to Rails): does this section of code go in the def that I want to use to execute the search?

Tsugihagi Over a year ago

Thank you for your help. Adding the scope to the Illness model gives a few "unexpected PAREN_ARG" errors in the IDE

Tsugihagi Over a year ago

This was fixed by removing a space between the arrow and the parenthesis

Mark Swardstrom Over a year ago

Nice solution! One thing - you don't need to join through to symptom. Just change the where clause - where("illness_symptoms.symptom_id in (?)", symptoms)

Mark Swardstrom · Accepted Answer · 2015-11-12 06:13:07Z

0

You can do an intersect - get the ids of one and limit the results of the other to get the final intersection. Be sure to index illness_symptoms on symptom_id and illness_id.

You can write something to extend this to take an array and hide the details, but here is an example.

# for example
fever = Symptom.find(1)
sneezing = Symptom.find(2)
@illnesses = Illness.with_symptom(fever).where(:id => Illness.with_symptom(sneezing).pluck(:id))

Add a scope to the Illness class

class Illness < ActiveRecord::Base

  has_many :illness_symptoms
  has_many :symptoms, :through => :illness_symptoms

  scope :with_symptom, -> (symptom) { joins(:illness_symptoms).where(:symptom_id => symptom) }

end

answered Nov 12, 2015 at 6:13

Mark Swardstrom

18.3k6 gold badges74 silver badges75 bronze badges

5 Comments

Tsugihagi Over a year ago

This seems to give an "Unexpected tlPAREN_ARG" error that I assume is related to the spacing from what I've looked up but I can't seem to fix it.

Mark Swardstrom Over a year ago

Your using Ruby 1.9? If so, try to change the spacing here: scope :with_symptom, ->(symptom) (remove space between arrow and paren)

Tsugihagi Over a year ago

ruby -vsays version 2.2.1p85 but that did solve it, thank you

Mark Swardstrom Over a year ago

The main takeaway here is to get the illness ids from one symptom and use that to limit the next one. There are a few ways to do that, you don't necessarily need a scope.

Tsugihagi Over a year ago

This seems to have worked properly and looks like it can be expanded to fit more symptoms as needed. Now I must learn how to pass the results of a user form to the method (though this is probably for another, unrelated question)

akbarbin · Accepted Answer · 2015-11-12 10:34:36Z

0

To find illnesses by symptoms, you can use IN in sql by array of text that user inputs several symptoms. You can do this:

Illness.joins(:symptoms).where("symptoms.name IN (?)", ["Fever", "Sneezing"])

I hope this help you.

answered Nov 12, 2015 at 10:34

akbarbin

5,1151 gold badge31 silver badges31 bronze badges

1 Comment

Tsugihagi Over a year ago

I'm sorry for the possibly simple question but I'm not sure where I'd add that code. I assume it's inside whatever method I'll use for searching, correct?

Collectives™ on Stack Overflow

Rails - Searching with multiple parameters

3 Answers 3

4 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related