Select distinct records from embedded_schema

I have a collection orders, each Order embeds_many invoices:

 @primary_key false
 embedded_schema do
    field(:invoice_id, :string)
    field(:invoice_party, :string)

I’d like to be able to get all orders that have more than one unique invoice_party (generally they would only have one but could have multiple parties). I have some ideas of how to go about this, shown below, but I haven’t had any success yet so any help would be very appreciated.

orders = from(o in Project.Orders.Schemas.Order, 
             where: fragment("select  
                            count(distinct 'invoice_party' from unset(?))", 
                             o.invoices) > 1 )
       |> Project.Repo.all

Edit for context: The above query throws this error:

 ** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near ">"
    (ecto 2.2.12) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto 2.2.12) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto 2.2.12) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

Figured it out with some help:

query = from(o in Project.Orders.Schemas.Order, where: fragment("(select count(distinct json->> 'customer_id') from unnest(?) as json)",  o.invoices) >1)

Apparently select inside the fragment needs to be in parentheses. Also I had to query the column as json to get the actual value out.

1 Like