Ecto preload nested association - Getting select values

Questions / Help
,
1

I have this Query

    query   = from u in UHF,
                where: (u.admin_id == ^admin_id and  u.box_id == ^box_id),
                left_join: b in assoc(u, :barcodes),
                preload: [{:barcodes, :product}]

    Repo.all(query)

Which fetches the below result.


[
  %Logistics.Products.UHF{
    __meta__: #Ecto.Schema.Metadata<:loaded, "product_uhf">,
    admin_id: "cf719686-b7e4-4c60-bf5e-835628e723a3",
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    barcodes: %Logistics.Products.Barcode{
      __meta__: #Ecto.Schema.Metadata<:loaded, "barcodes">,
      admin_id: "cf719686-b7e4-4c60-bf5e-835628e723a3",
      barcode: "051111407592",
      id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
      **name: "NEW BARCODE1",**
      product: %Logistics.Products.Product{
        __meta__: #Ecto.Schema.Metadata<:loaded, "products">,
        admin_id: "cf719686-b7e4-4c60-bf5e-835628e723a3",
        barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
        barcodes: #Ecto.Association.NotLoaded<association :barcodes is not loaded>,
        id: "4ed730be-bc81-42af-b19f-0d0da062f347",
        **image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",**
        name: "NEW TV 1"
      },
      product_uhf: #Ecto.Association.NotLoaded<association :product_uhf is not loaded>
    },
    box_id: "970f66b3-ea2a-4d9c-a20b-560c6e64df9a",
    box_uhf: #Ecto.Association.NotLoaded<association :box_uhf is not loaded>,
    id: "7bff4215-f5b0-4bd3-ae5f-905a5c925aa2",
    **uhf: "E28011700000020C220D9CF1"**
  },
  %Logistics.Products.UHF{
    __meta__: #Ecto.Schema.Metadata<:loaded, "product_uhf">,
    admin_id: "cf719686-b7e4-4c60-bf5e-835628e723a3",
    barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
    barcodes: %Logistics.Products.Barcode{
      __meta__: #Ecto.Schema.Metadata<:loaded, "barcodes">,
      admin_id: "cf719686-b7e4-4c60-bf5e-835628e723a3",
      barcode: "051111407592",
      id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
      **name: "NEW BARCODE1",**
      product: %Logistics.Products.Product{
        __meta__: #Ecto.Schema.Metadata<:loaded, "products">,
        admin_id: "cf719686-b7e4-4c60-bf5e-835628e723a3",
        barcode_id: "14a1e98d-a67f-464c-a92e-de75fa01abc5",
        barcodes: #Ecto.Association.NotLoaded<association :barcodes is not loaded>,
        id: "4ed730be-bc81-42af-b19f-0d0da062f347",
        **image: "https://s3.ap-south-1.amazonaws.com/hoovi234/products/8f49984066e04c30b091b326b33e8e72.png",**
        name: "NEW TV 1"
      },
      product_uhf: #Ecto.Association.NotLoaded<association :product_uhf is not loaded>
    },
    box_id: "970f66b3-ea2a-4d9c-a20b-560c6e64df9a",
    box_uhf: #Ecto.Association.NotLoaded<association :box_uhf is not loaded>,
    id: "d6ef2c38-a88c-4bbc-9274-1d7b46a96b87",
    **uhf: "E28011700000020C220DA5E2"**
  }
]

I only want these 3 fields from this result

  1. uhf
  2. barcodes.name
  3. barcodes.product.image

First to get uhf and barcodes.name i tried below one

query   = from u in UHF,
                where: (u.admin_id == ^admin_id and  u.box_id == ^box_id),
                left_join: b in assoc(u, :barcodes),
                preload: [{:barcodes, :product}],
                select: %{uhf: u.uhf, name: b.name}

    Repo.all(query)

I am getting these error

** (Ecto.QueryError) the binding used infrommust be selected inselectwhen usingpreload in query:

Plus I want image field from the from product preload

Ex: barcodes.product.image

Can anyone give me insight on how to achieve this. Your help is greatly appreciated.

1 Like
2

I think removing the preload and using another join will do the trick. Otherwise you must put the whole u in the select to use preload which is not what you want if you need only certain fields.

query   = from u in UHF,
                where: (u.admin_id == ^admin_id and  u.box_id == ^box_id),
                left_join: b in assoc(u, :barcodes),
                left_join: p in assoc(b, :product),
                select: %{uhf: u.uhf, name: b.name, image: p.image}
3 Likes
3
  1. I have this query
    query   = from t in Tracks,
                where: t.admin_id == ^admin_id and t.destination == ^location_id,
                preload: [:source_locations, :delivery_locations],
                join: d in Driver, on: t.driver_id == d.id,
                join: c in Company, on: d.company_id == c.id

In this i am getting the preload results but not the ā€˜dā€™ and ā€˜cā€™

So i changed the query to


    query   = from t in Tracks,
                where: t.admin_id == ^admin_id and t.destination == ^location_id,
                left_join: l in assoc(t, :source_locations),
                join: d in Driver, on: t.driver_id == d.id,
                join: c in Company, on: d.company_id == c.id

In this source_locations and ā€˜dā€™ and ā€˜cā€™ is not loaded.

  1. I have two association which belongs to the same table

For example

left_join: l in assoc(t, [:source_locations, :delivery_location]) i know this expression is wrong but to exaplain i wrote this.
I want both the [:source_locations, :delivery_location] to be loaded.

Can you give me insight on how to achieve this? Your help is greatly appreciated.

4

Guys any help on this?