Sorting multi-currency prices with pagination?

Questions / Help
1

I am struggling with sorting products by price.
What I’ve experienced before, in most case, was just using order_by price ASC(DESC)

In my current project, since it is a global marketplace, there exist a lot of currencies like below.

products
/ -------- name -------- / - currency - / -- price -- /
  American Hotdog        /  USD         /  10.25
  French Pie             /  EUR         /  8.88
  Kaya Toast             /  SGD         /  4.00
  ...
--------------------------------------------------------

I am using ex_money, decimal and, save it as a composite type of postgres
CREATE TYPE public.money_with_currency AS (currency_code char(3), amount numeric(20,8))
so originally in the database, it would be like

/ -------- name -------- / -------- price -------- /
  American Hotdog        /  (USD,10.25000000)
  ...
----------------------------------------------------

I want to offer sorting by price option to the shoppers. and the conditions are like below.

  • server gets up-to-date currency table everyday.
  • sorting products by price should use above table
  • since we need pagination, all sorting should be done in server.

i am thinking of using calculated column, worrying about the performance

how can I approach this problem?

2

Postgres provides a syntax to access to the elements of a composite data type. For your example, to access the price amount, you would say (price).amount and to sort by price you would say ORDER BY (price).amount DESC. For ecto that would likely require the use of a fragment for the order clause.

Something like order_by: [desc: fragment("(price).amount")]

(Disclaimer, I’m the author of ex_money)

4 Likes
4

Thanks, it worked gracefully !!