Need help writing a query

Questions & Help Questions
1

Hi, I’m not even sure how to put what I’m doing into words, so I’ll try to provide some examples.

ecto: 3.10.3
ecto_sql: 3.10.1
postgresql: 15

I have 2 schemas:

defmodule Listing do
  use Ecto.Schema

  schema "bulk_trade_listings" do
    belongs_to :user, User, type: :binary_id
    has_many :items, Item, on_replace: :delete, foreign_key: :bulk_trade_listing_id

    timestamps()
  end
end

defmodule Item do
  use Ecto.Schema

  schema "bulk_trade_listing_items" do
    belongs_to :bulk_trade_listing, Listing
    field :item_id, :string
    field :stock, :integer
    field :price_amount, :float
    field :price_currency, :string
  end
end

I need to get a number of listings containing an item filtered by item_id and stock.

So, given there are following listings in the DB:

[
  %Listing{
    id: 1,
    items: [
      %Item{id: 1, item_id: "1", stock: 1}
    ]
  },
  %Listing{
    id: 2,
    items: [
      %Item{id: 2, item_id: "1", stock: 2},
      %Item{id: 3, item_id: "2", stock: 10},
      %Item{id: 4, item_id: "3", stock: 100},
    ]
  },
  %Listing{
    id: 3,
    items: [
      %Item{id: 5, item_id: "3", stock: 3}
    ]
  }
]

and querying it with values %{item_ids: ["1", "2"], min_stock: 1} and a limit of 2, I want to get the following:

[
  %Listing{
    id: 1,
    items: [
      %Item{id: 1, item_id: "1", stock: 1}
    ]
  },
  %Listing{
    id: 2,
    items: [
      %Item{id: 2, item_id: "1", stock: 2},
      %Item{id: 3, item_id: "2", stock: 10}
    ]
  }
]

Normal join does not fit my needs, because as I understand, in a query like

      from(
        l in Listing,
        inner_join: i in assoc(l, :items),
        where: i.item_id in ^item_ids and i.stock >= ^min_stock,
        limit: 2
      )

limit is applied to the total number of rows in query, so I will only get 2 items.

I probably need to group by listing id, but I’m not sure how to aggregate and return the items.

I only have basic knowledge of SQL, so any hints or tips are appreciated.

2

Just as I posted my question, I tried

      from(
        l in @schema,
        join: i in assoc(l, :items),
        where: i.item_id in ^item_ids and i.stock >= ^min_stock,
        limit: 2,
        group_by: l.id,
        select: %{l | items: fragment("array_agg(?)", i)}
      )

and it sort of worked, but items are not being casted to structs:

[
  %Listing{
    id: 1,
    items: [
      {1, "1", 1}
    ]
  },
  %Listing{
    id: 2,
    items: [
      {2, "1", 2},
      {3, "2", 10}
    ]
  }
]

Is there a way to make Ecto do it, or do I just cast them manually?

3

You can maybe write a custom query for your items preload?

4

Haven’t figured out how to make such query yet. Decided to use Repo.load/2 after fething the data for now.

5

Didn’t try but can be something like this

items_query = 
  from i in Item,
    where: i.item_id in ^item_ids and i.stock >= ^min_stock,
    limit: 2

query = 
  from l in Listing,
    join: i in assoc(l, :items),
    where: i.item_id in ^item_ids and i.stock >= ^min_stock,
    preload: [items: ^items_query]

Repo.all(query)
6

You can use lateral join if you need the limit to apply to joined rows per parent instead of the overall number of results.