Need help for writing query for a marketplace in postgres

Question

I have database for a marketplace which looks like this:

I have different suppliers selling the same product at different price points, also some products are more popular than others in a given location. For example we have product A and product B, and product A is more popular that product B based on how many has already been sold in that location, and for product A we have 3 suppliers. I want my query to show product A from the cheapest seller, then product B from the cheapest seller. I can achieve that with this code:

WITH tem_1 AS (SELECT product_id, MIN(price) AS price FROM product_supplier GROUP BY product_id) ,
     tem_2 AS (SELECT product_id, SUM(quantity) AS n_orders FROM orders Group by product_id)
SELECT products.product_id, suppliers.supplier_id, products.name, tem_1.price,
COALESCE(tem_2.n_orders,0) AS quant FROM products
  INNER JOIN product_supplier ON product_supplier.product_id = products.product_id
  INNER JOIN suppliers ON suppliers.supplier_id = product_supplier.supplier_id
  INNER JOIN product_code ON product_code.code_id = products.code_id
  INNER JOIN product_crop ON product_crop.product_id = products.product_id
  INNER JOIN crops ON crops.crops_id = product_crop.crop_id
  INNER JOIN product_tags ON product_tags.product_id = products.product_id
  INNER JOIN tags ON tags.tag_id = product_tags.tag_id
  INNER JOIN tem_1 ON tem_1.price = product_supplier.price AND tem_1.product_id = products.product_id
  LEFT JOIN tem_2 ON tem_2.product_id = products.product_id
WHERE crops.crops_id = 1 AND product_supplier.quantity >= 3 AND tags.tag = 'علف کش'
ORDER BY quant DESC
LIMIT 10;

The problem is, if i have two different suppliers from different locations, selling the same product with the same price, the results show that product twice, but i only want the results from the closest supplier to the user, in this case product 101 from supplier 3 and not the supplier 1.

I think i have to use MIN(ST_Distance("geopoint from user", "geopoint from suppliers")) and LATERAL to have a distance filed, but because i'm using aggregate functions, in order to do a GROUP BY to remove the duplicate results, i have to add all the fields product_id, supplier_id, name, price, ... to the GROUP BY and that won't result in removing the duplicates.

Any suggestion on how to achieve that?

I would suggest another CTE(WITH clause) to isolate the distance calculations from the rest of the query so you can use that value later as a tie-breaker. — Eric Brandt

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-02-14T15:14:05

Your query is rather hard to follow. But, distinct on solves your problem. I'm not 100% sure what you want to be distinct, but something like this:

select distinct on (product_id, price) . . .
from . . .
where . . .
order by product_id, price, ST_Distance("geopoint from user", "geopoint from suppliers");

This returns one row per product and price, based on the smallest distance.

If you want the data ordered in a different way, then use this as a subquery or CTE and order by again in the outer query.

Need help for writing query for a marketplace in postgres

1 Answers