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

tanweerdev

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

12 comments

#ecto #databases #postgresql #many_to_many #query

0 2348 12

2019-04-16 07:25:11 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

mythicalprogrammer

Does it happen to do with that line?

Post #1

tanweerdev

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

Post #2

tanweerdev

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

Post #3

tanweerdev

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

Post #4

axelson

Scenic Core Team

How are you preloading the revisions?

Post #5

tanweerdev

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]

Post #6

tanweerdev

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

Post #7

al2o3cr

(formatting for discussion)

tanweerdev:

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])
  >
]

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?

Post #8