How to query on keys in an embeds_many

Questions / Help
, ,
1

Given the following schema:

  schema "invoices" do
    embeds_many :items, InvoiceItem

    timestamps()
  end

And the embedded schema

  embedded_schema do
    field :name, :string
    field :amount, :integer
    field :price, :integer
  end

How can I query for items with a given name? I can’t seem to get it working.

I tried fragments, joins, apparently lateral joins aren’t supported directly but can be used in a different form using subqueries, but I’m afraid I don’t see how.

Can anyone shed some light? This can’t be such an obscure usecase

2

Have you tried something like this?

query =
  from invoices in Invoice,
    inner_lateral_join:
      items in fragment(
        "SELECT * FROM jsonb_to_recordset(to_jsonb(?)) as items(name text, amount integer, price integer)",
        invoices.items
      ),
    select: %InvoiceItem{name: items.name, amount: items.amount, price: items.price},
    where: items.name == ^"Horse"
Repo.all(query)
1 Like
3

I was unaware that inner_lateral_join was supported in Ecto.
I managed to get it working! Thanks