Getting the current lowest price for a record in my database?

Questions / Help
1

How would I grab the current lowest price for a Game given this query I’m using for my search results.

For each Game, I need to grab every StoreItem. And for every StoreItem grab the latest by StoreItemPrice.inserted_at.

These are the current prices across multiple stores.

Finally return the StoreItemPrice that has the lowest StoreItemPrice.price value.

That’s the Game’s current lowest price.

query =
  from(g in Game,
    order_by: [desc_nulls_last: :first_release_date],
    where:
      fragment(
        "to_tsvector('english', name || ' ' || slug) @@ to_tsquery(?)",
        ^prefix_search(search_term)
      ),
    preload: [
      :genres,
      :platforms,
      store_items: [:store_item_prices]
    ]
  )

Something like this feels about right but not quite there. Since it’s grabbing the absolute lowest StoreItemPrice and that price could be old and superseded by a newer higher price in that store.

query =
  from(g in Game,
    order_by: [desc_nulls_last: :first_release_date],
    where:
      fragment(
        "to_tsvector('english', name || ' ' || slug) @@ to_tsquery(?)",
        ^prefix_search(search_term)
      ),
    preload: [
      :genres,
      :platforms,
      store_items: [
        store_item_prices: [
          order_by: [asc: :price],
          limit: 1
        ]
      ]
    ]
  )

I need the latest price per store, then use the lowest price of between those stores.

Been noodling on this for a day or so, and kind of stuck. :sob:

2

This sounds like an ideal situation for window functions. Ecto has great support for this via the Ecto.Query.WindowAPI macros.

2 Likes
3

This seems right, I would only need to add a limit: 1 to the store_item_prices_query, but the store_item_prices don’t come back ordered.

latest_store_item_price_query =
  from(sip in StoreItemPrice,
    select: %{id: sip.id, row_number: over(row_number(), :store_item_partition)},
    windows: [
      store_item_partition: [partition_by: :store_item_id, order_by: [desc: :inserted_at]]
    ]
  )

store_item_prices_query =
  from(sip in StoreItemPrice,
    join: r in subquery(latest_store_item_price_query),
    on: sip.id == r.id and r.row_number <= 1,
    order_by: [desc: :price]
  )

query =
  from(g in Game,
    order_by: [desc_nulls_last: :first_release_date],
    where:
      fragment(
        "to_tsvector('english', name || ' ' || slug) @@ to_tsquery(?)",
        ^prefix_search(search_term)
      ),
    preload: [
      :genres,
      :platforms,
      store_item_prices: ^store_item_prices_query
    ]
  )

Repo.all(query)

Could it be that the price field which is ex_money is causing the issue? Can’t I sort by that field?

4

You can definitely sort by Money.Ecto.Composite.Type fields.

Just note that the comparison operators in Postgres operate like they do in Elixir - they are structural comparisons. So in Postgres terms, Money.new(:AUD, 100) < Money.new(:USD, 50) is true because sorting first compares "AUD" to "USD" then compares 100 to 50.

2 Likes
5

Thanks what would a comparison in Ecto look like to get a max value of an ex_money field? Do I need to specify the currency as well?

6

I haven’t implemented custom Postgres aggregate functions except sum. But I will now also work on min, max and avg over the holiday break.

For now, assuming that all currencies are the same, you can call max on the amount subfield like this:

Repo.one(from sip in StoreItemPrice, select: fragment("max(amount(price))"))
1 Like
7

May be possible to use lateral joins as well to include everything in a single query. I’ve left out some bits but this is the basic pattern:

from(g in Game,
  left_join: i in assoc(g, :store_items),
  as: :store_item,
  left_lateral_join: lateral_p in subquery(
    from(StoreItemPrice,
      where: [store_item_id: parent_as(:store_item).id],
      order_by: [desc: :inserted_at],
      limit: 1,
      select: [:id]
    )
  )
  left_join: p in assoc(i, :store_item_prices),
  on: p.id == lateral_p.id,
  preload: [
    store_items: {i, store_item_prices: p}
  ]
)

No idea on the actual perf of this vs. window functions, but I find lateral joins a bit easier to read sometimes.

3 Likes
8

I’ve just published ex_money_sql version 1.8.0 that provides a new mix task to generate a migration that installs a min and max function for the money_with_currency data type in a Postgres database. That means you can use the Ecto min and max functions with money_with_currency types per your earlier question.

Example

# Generate the migration
% mix money.gen.postgres.min_max_functions
* creating priv/repo/migrations
* creating priv/repo/migrations/20221225170722_add_postgres_money_minmax_functions.exs
4 Likes
9

Thanks so much for all your help and open source code @kip you ship so much high quality work! I’ll give this a run on my project. I’m still trying to figure out how to show the lowest current price by store in my search results, had to table it to knock some other low hanging fruit out but I will circle back and share my code here for any would be elixir devs with my same problem.

@slouchpie you probably want this for your project, mix/max is tremendously useful!

1 Like