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
and
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.
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
query
|> join(:inner, [product: p, parent: pp, children: c], sp in StorefrontProduct,
on: sp.parent_id == pp.id or sp.id in [p.id, pp.id, c.id]
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 == p.id
as: :attributes)
end
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 Repo.one/1 crashes because we get two or more records back. I’ve looked at the records (by switching Repo.one/1 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?
