Question 20212 in Mysql Question posted in
The official MySQL documentation can be found here.

Rails MySQL Joins on null

I have three tables.

hospital_bed, patient and condition.

each hospital bed can have one patient
each patient can have many conditions, but conditions can also be null in case it is not known yet

I basically want to display all beds and patients and then in a collapsable underneath all the conditions of the patient.

For that, I need to join these three tables like this:

HospitalBed.joins(:patient).joins('JOIN condition ON patient.id = condition.patient_id')

This way I receive all beds and patients and their conditions except the patients that do not have a condition listed yet.

How do I also get them? I know this would work

HospitalBed.joins(:patient).joins('JOIN conditions')

But this generates way too many rows than necessary.

Edit:
I want to get all beds and all patients no matter if they have a condition or not. All I can return are patients that have conditions but I also want the ones that do not have one (yet).

This but via the HospitalBed

Patient.left_joins(:conditions).merge(Condition.where.not(id: nil).or(Condition.where(id: nil)))
Tags:

2

Answers


  1. 0

    Ruby on Rails will do an INNER JOIN per default when using joins with a symbol. That means only relations will be returned that have matching records on both sides.

    But when you want a JOIN to return also relations where one of the records does not exist, then you need to use an OUTER JOIN. Ruby on Rails has the left_outer_joins for those cases. But unfortunately you cannot use that method, because you will need the OUTER JOIN on a nested association.

    Therefore, in your case the best option seems to be the writing the JOIN statement yourself, like this:

    HospitalBed
  .joins(:patient)
  .joins('LEFT OUTER JOIN conditions ON patients.id = conditions.patient_id')

    Please note that when writing the join statement manually, then you need to use the real tables names in plural like they are named in the database.

    Login or Signup to reply.
  2. 0

    You don’t need a SQL string here. You just need to setup your associations properly:

    class HostipitalBed
  belongs_to :patient
  has_many :conditions, through: :patient
end

    And then you need to figure out what you actually mean by "How do I also get them?":

    HostipitalBed.joins(:conditions)        # Left inner join
HostipitalBed.left_joins(:conditions)   # Left outer join 
HostipitalBed.includes(:conditions)     # Loads the conditions in a separate query to avoid n+1
HostipitalBed.eager_load(:conditions)   # Loads the conditions in a single query to avoid n+1

    Both joins and left_joins don’t actually select anything from the joined table. If you want to display the conditions you want to use include/eager load to avoid a n+1 query issue.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search