Why I am getting same ids/resources returned when try to preload has_many relation

Questions / Help
, , , ,
1

I have this query

#Ecto.Query<from i0 in HaiData.Infection,
 left_join: e1 in assoc(i0, :encounter), left_join: f2 in assoc(i0, :facility),
 left_join: r3 in assoc(i0, :revisions), order_by: [asc: i0.id], limit: ^10,
 offset: ^0, select: map(i0, [:id, :encounter_id, :facility_id]),
 preload: [
  encounter: #Ecto.Query<from e0 in HaiData.Encounter, left_join: p1 in assoc(e0, :patient), limit: ^200, offset: ^0, select: map(e0, [:id, :patient_id]), 
    preload: [patient: #Ecto.Query<from p0 in HaiData.Patient, limit: ^200, offset: ^0, select: map(p0, [:id])>]>, 
  facility: #Ecto.Query<from f0 in HaiData.Facility, limit: ^200, offset: ^0, select: map(f0, [:id, :name])>, 
  revisions: #Ecto.Query<from i0 in HaiData.InfectionRevision, limit: ^200, offset: ^0, select: map(i0, [:revision_id])>
  ]
>

But in return I am getting same resource repeating ie same ids being returned, dont know the reason

[
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1},
     %{encounter_id: 15, facility_id: 2, id: 1}
   ]

Really weird. Dont know if its a bug in ecto or my query. Please help me out debugging issue. Thanks

2

Does it happen to do with that line?

3

No. it happens even if I select more/less fields

4

It only happens when I preload has_many revisions relation.
If I remove revisions from preloading, it just works fine

5

I just created a repository to help identify the issue has_many preload issue.

Please

  • Clone
  • mix deps.get
  • mix ecto.setup
  • mix test

and there is one file has_many_test. Please have a look, it describes the issue

1 Like
6

How are you preloading the revisions?

7

Test cases file with both normal results and duplicated

Query from test case

patient_query = from p0 in Data.Patient, limit: ^200, offset: ^0, select: map(p0, [:id])

 encounter_query =
      from e0 in Data.Encounter,
        left_join: p1 in assoc(e0, :patient),
        limit: ^200,
        offset: ^0,
        select: map(e0, [:id, :patient_id]),
        preload: [patient: ^patient_query]

 revisions_query =
      from i0 in Data.InfectionRevision, limit: ^200, offset: ^0, select: map(i0, [:revision_id])

 query =
      from i0 in Data.Infection,
        left_join: e1 in assoc(i0, :encounter),
        left_join: r3 in assoc(i0, :revisions),
        order_by: [asc: i0.id],
        limit: ^10,
        offset: ^0,
        select: map(i0, [:id, :encounter_id]),
        preload: [encounter: ^encounter_query, revisions: ^revisions_query]
8

You can run test cases on your machine, just clone and setup and you will be able to debug/fine the issue on your machine if you want

9

(formatting for discussion)

preload tells Ecto how to load associated data into the result set. I don’t understand the intended result of using it with a select that doesn’t include any associated data.

What behavior are you expecting from this query? What SQL should be generated?

10

oh It definitely needs to have associated data/fields. I just to keep query simple selected minimum fields

11

I am expecting to get all infections(if there are 3 infections, there will be total array of 3 records) with relevant/associated revisions preloaded.

12

I believe your original question…

same resource repeating ie same ids being returned

…is because you’re both preloading and left_joining the same tables in the same query. It looks like you could delete your joins (leaving your specifically-queried preloads) and stop getting duplicate records.

As for what gets returned, if it were me, I’d just drop the select statement. Preloading will return the associations, and I’d move forward. Perhaps there are business-reasons you strongly prefer to bang your head against nested select syntax to return only 3 fields alongside associations. I wouldn’t choose to bang my head against select syntax when I could just filter out fields in Elixir, but that’s me. :slight_smile:

2 Likes
13

You are correct. Atleast removing (left) join from has_many association it worked perfectly. Thanks