Find diseases from users suffering for a specific disease
I trying to get all other DISEASES from users suffering for disease A.
OLD QUESTION (WRONG):
I trying to get users suffering from some disease also suffer for other diseases:
class User has_many :treatments end class Disease has_many :treatments end class Treatment belongs_to :user belongs_to :disease end
I have some working code:
Get all users ids who suffer for specific disease:
user_ids = Treatment.joins(:user).where(disease_id: 250).pluck(:user_id)
Get all diseases based on users ids
Disease.where("id != 250").where(id: Treatment.select("DISTINCT disease_id").where(user_id: user_ids))
Is there simpler way? Maybe one query?
EDITED: If you want to use one query, you could use this one:
select distinct d1.id from diseases as d1 JOIN treatments as t1 on d1.id=t1.disease_id where exists ( select t2.id from treatments as t2 where t2.disease_id = 250 AND t2.user_id=t1.user_id ) AND d1.id <> 250;