Ecto Preload in array field

Lets take this example

    create table(:barcodes, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :name, :string
      add :barcode, :string
      add :product_id, references(:products, on_delete: :nothing, type: :binary_id)

      timestamps()
    end

Schema

  schema "barcodes" do
    field :barcode, :string
    field :name, :string

    field :admin_id, :binary_id
    field :user_id, :binary_id

    has_one(:product, Logistics.Products.Product)

    timestamps()
  end

Query

    query = from b in Barcode,
              distinct: b.id,
              where: b.admin_id == ^admin_id,
              left_join: p in assoc(b, :product),
              preload: :product

    Repo.all(query)

The above one perfectly works for me.

But what if I want to add a set of product ids in a single field like array instead of single product id.

Ex:

    create table(:barcodes, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :name, :string
      add :barcode, :string
      add :product_id, {:array, :string}

      timestamps()
    end

Schema:

field :product_id, {:array, :string }, default: []

It will be stored as

product_id: [“888fe8a8-4de9-4f14-a596-f333f8b02e6d”, “988fe8a8-3de9-4f14-a596-f333f8b02e6d”, … … …]

Now when I want to query the barcodes along with the preloaded product ids.

Any insight on how to achieve this will be greatly appreciated. Thanks

Hi @david234

I would design a schema and relationship a bit differently instead of saying a barcode has one or putting arrays of product into barcode schema. I would map a relationship like a barcode has many products and a product belongs_to a barcode. My schema and migration would look something like this ->

# Barcode Migration
create table(:barcodes, primary_key: false) do
  add :id, :binary_id, primary_key: true
  add :name, :string
  add :barcode, :string
  # If you see you will see no reference to products is been removed.

  timestamps()
end

# Product Migration
create table(:products, primary_key: false) do
  add :id, :binary_id, primary_key: true
  add :barcode_id, references :barcode, type: :binary_id #skipping on_delete and on_update here
  
  timestamps()
end

And your schema could look something like this ->

#Barcode Schema
#Primary key definition omitted here
schema "barcodes" do
  field :barcode, :string
  field :name, :string

  field :admin_id, :binary_id
  field :user_id, :binary_id

  has_many :products, ProductModule # Foreign Key and other options could be added here

  timestamps()
 end

#Product Schema
schema "products" do
  # Other fields are omitted here.

  belongs_to :barcode, BarcodeModule # Foreign Key and other options could be added here

  timestamps()
 end

And then you want to query barcode with it products you could do something as ->

barcode = 
  Repo.get(BarcodeModule, "someId")
  |> Repo.preload([:products])

I hope this answer your question.

Thanks, @blisscs for your response.

Actually I am working on a separate requirement called Tracking. This barcode I showed for an example.

So I need a way to store some ids as an array in a particular field and later I could able to link to their parent table and retrieve the information of each id.

If you could share some insight on achieving this, will be a great help for me. Thank you very much for your response once again.

Can you elaborate more on your requirements.

I would generally refrain from doing that kind of relationships. Generally what we do in relational database is that we put the id (generally called foreign key) of another relationship in the side that has zero or one relationship to another side. but in here you are doing it differently by putting arrays of ids on this side of relationship.

By doing I think there is one draw back. you have to do additional one query to fetch the related relationship. you can’t do join and preload things.

could you elaborate on your usecase. if it’s necessary we could do some hack, but i would refrain from doing that

ok, in my case each item is unique.

Item will be trnsported from factory to warehouse or from warehouse to shop or directly from warehouse to shop other than these they will be transported to other location too.

So for each transportation i need to track the souce and destination and the item IDS which are transported.

For example

these are the item ids [1,2,3,4,5,6,7,8,9]

Day 1
[2,3,4] are transported from Location A to B
[1,6,7,8,9 are transported from Location A to C

Day 2
[3,4] are transported from Location B to D
[1] are transported from Location c to X
[7,8] are transported from Location C to Y

Day 3



So like this i need to keep track of what are the item IDs that are transported on each trip.I need to keep history of that too.

So i created a Trip Table to record each trip along with the Item IDs.

  schema "tracks" do
    field :delivered_date, :naive_datetime
    field :started_date, :naive_datetime
    field :status, :integer
    field :source, :binary_id
    field :destination, :binary_id
    field :driver_id, :binary_id
    field :item_ids, {:array, :string }, default: []
    field :admin_id, :binary_id

    timestamps()
  end

I hope now you have got what I am trying to achieve. Thank you for your response once again.

Could this blog post help you out? it also has a gitrepo with an example. They use an explicit jsonb column in which they store the array. Also some example code on how to query the column (including indexing).

Hope it helps!

1 Like