Similar queries but different results

To me these two queries are similar:

Query 1:

       from i0 in HaiData.Infection,
       right_join: i1 in HaiData.InfectionCriteria,
       on: i1.infection_id == i0.id and i0.infection_category_id in ^[8816],
       right_join: u2 in HaiData.UserFacilityPermission,
       on: u2.facility_id == i0.facility_id and u2.reporting == true and u2.user_id == ^13446 and u2.facility_id in 
      ^[11411]

Query 2

       from i0 in HaiData.Infection,
       right_join: i1 in "infection_criteria", on: i0.id == i1.infection_id,
       right_join: u2 in HaiData.UserFacilityPermission,
       on: u2.facility_id == i0.facility_id and u2.reporting == true and u2.user_id == ^13447 and 
       u2.facility_id in ^[11413],
       where: i0.infection_category_id in ^[8817] and ^true

They should produce the same result but i got 1 result returned from Query 2 and 0 results from Query 1.

I want Query 2 to be equivalent of Query 1.

Any help will be much appreciated

Thanks

  1. You can use https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#to_sql/3 to generate the actual SQL and see what differences exist
  2. The ids are different, so couldn’t that be the reason?
2 Likes

They aren’t the same query.

The first one will generate SQL like (skipping the second right_join clause because it’s the same shape in both):

SELECT ... FROM infections
RIGHT JOIN infection_criteria ON infection_criteria.infection_id = infections.id AND infections.infection_category_id IN [1234]

this will give a row of results for every record in infection_criteria. If the ON clause doesn’t match, that row will have NULLs for infections columns.

The second one will generate similar-but-different SQL:

SELECT ... FROM infections
RIGHT JOIN infection_criteria ON infection_criteria.infection_id = infections.id
WHERE infections.infection_category_id IN [1234]

This will only have a row in the results when a corresponding record exists in infections.

1 Like