Yii CDbCriteria计算相关行

Question

I have a Product (id, name, ...) model, and an OrderItem (id, order_id, product_id) model and I want to sort the products by top selling what I can get from OrderItem model.

The problem is: in yii the STAT relation is in a separate query so I cant sort it in CDbCriteria, how can I put this query in the CDbCriteria?

Answer 1

Did you try to do a join on cdbcriteria and then order by ?

Something like this could work:

<?php

$criteria->mergeWith([
    'join' => ' INNER JOIN (SELECT COUNT(*) as qty, product_id FROM OrderItem oi GROUP BY product_id) oiInner ON oiInner.product_id=t.product_id',
    'order' => ' oiInner.qty DESC'
]);

Something like this should work! Don't forget to add this as a sort option on the DataProvider sort options!