How to display the most purchased products through Active Record or query?

Question

PostgreSQL

Good afternoon,
There are 3 tables:
products
.id
.name
.price
orders
.id
.price
orders_products
.id
.order_id
.product_id
.qty
.price How To pull the best selling products by quantity from products
via link orders->orders_productsvia (via Having By , I suppose) . given orders_products.qty ?

Answer 1

Make 2 queries, I think it will be easier, the first one is possible even on pure sql.
Example of the first:
'select product_id, sum(qty) as qty from orders_products
order by qty desc limit 10'
ps - didn't read that you have postgresql, but the idea is the same.
then you map the array and ask with the following request:
And the second request:
$modes = Products::find()->andWhere(['id'=>$product_id])->all();
One query variant:
$models = Products::find()->select('*, sum(orders_products.qty) as qqty')
->innerJoin('orders_products', 'products.id=orders_products')
->orderBy( 'qqty desc')
->limit(10);