Ecto query to ensure unique ids

I’m working with a virtual schema that helps populate products on a storefront, it is built from persisted data friom another model. (

We have Products which are associated to ProductAttribute. A product is stand alone by default, but a user can add a variant product which becomes a child record on the product. In such cases a default? product must be chosen so we know which one should be displayed on the storefront. The problem I’m having is that sometimes there are multiple instances of the same product showing on the storefront (in other words, the correct product is showing, multiplied by the number of variants it has.

Two (simplified) schema modules, the first is the Product persisted to the database, the second is the StorefrontProduct we display on the storefront which is built through the Ecto.Query:

defmodule App.Products.Product do 
   @moduledoc this is the product record that is stored in the database
   schema "products" do 
   has_one :product_attribute, ProductAttribute 
   belongs_to :parent, __MODULE__
   has_many :children, __MODULE__, foreign_key: :parent_id
   field :default?, :boolean 


defmodule  App.Products.StorefrontProduct do    
   @moduledoc this is a simpler schema that we query to display products, there are far fewer fields and most are virtual
   schema "products" do 
   belongs_to :parent, __MODULE__
   has_many :children, Product, foreign_key: :parent_id

When loading products on the storefront we query from StorefrontProduct.

   |> from(as: :product)
   |> join(:left, [product: p], c in assoc(p, :children), on: is_nil(c.deleted_at), as: :children)
   |> join(:left, [product: p], pp in assoc(p, :parent), on is_nil(pp.deleted_at), as: parent)
   |> join_product_attributes()

defp join_product_attributes(query) do 
   |> join(:inner, [product: p, parent: pp, children: c], sp in StorefrontProduct, 
      on: sp.parent_id == or in [,,]
      on: sp.default?,
      on: is_nil(p.deleted_at),
      as: :default_product)
   |> join(:inner, [product: p, default_product: d] a in Attributes, 
      on: a.product_id == 
      as: :attributes)

The idea is that we query all Product records, then we join parent records and child records. In join_product_attributes/1 we join the Attributes on whatever product is the default?.

This works most of the time but occasionally crashes because we get two or more records back. I’ve looked at the records (by switching to all/2 and they are just identical copies):

[%StorefrontProduct{id: "same_id"}, %StorefrontProduct{id: "same_id"}]

My thought is that somewhere in the query we are not specific enough that the product needs to have the default?: true flag and so it is allowing all instances of the product through, although I can’t easily identify where this is happening (the above code is a much smaller psudo query). Is there any way I can scope the returned StorefrontProduct records so that duplicate ids are rejected?

1 Like

My coworker, who is apparently a lurker here, solved it, distinct/3 at the end of the query:

|> distinct([storefront_product],
1 Like