How to convert msql query to Yii syntax?

Question

MySQL

Hello.
I started to deal with Yii and at the same time redo old developments.
Got this request:

SELECT ord.id_order,ord.id_customer,ord.payment,ord.total_paid,ord.date_add,sel.id_order_state,sel.date_add AS date,addr.city,cust.lastname,cust.firstname,posl.name,pos.color,ord.is_long,ord.delivery_company,ord.notation
      FROM ps_orders ord
            INNER JOIN 
            (SELECT hist1.id_order, hist1.id_order_state, hist1.date_add
              FROM ps_order_history hist1
              INNER JOIN 
              (SELECT id_order, MAX(date_add) AS date_add
                FROM ps_order_history
                GROUP BY id_order) hist2
              ON hist1.id_order =hist2.id_order AND hist1.date_add =hist2.date_add) sel
            ON ord.id_order=sel.id_order
            INNER JOIN ps_customer cust 
            ON cust.id_customer=ord.id_customer
            INNER JOIN ps_order_state pos
            ON pos.id_order_state=sel.id_order_state
            INNER JOIN ps_order_state_lang posl
            ON posl.id_order_state=sel.id_order_state
            INNER JOIN ps_address addr
            ON addr.id_address=ord.id_address_delivery
            ORDER BY sel.date_add DESC

But the problem is only with this part:

(SELECT hist1.id_order, hist1.id_order_state, hist1.date_add
   FROM ps_order_history hist1
   INNER JOIN 
      (SELECT id_order, MAX(date_add) AS date_add
         FROM ps_order_history
      GROUP BY id_order) hist2
   ON hist1.id_order =hist2.id_order AND hist1.date_add =hist2.date_add) sel

It turns out that in the table model you need to register a connection with this table itself. But how exactly?
Maybe someone will tell?

Answer 1

$criteria = new CDbCriteria();
$criteria->join='INNER JOIN (SELECT id_order, MAX(date_add) AS date_add FROM ps_order_history GROUP BY id_order) hist2 ON  t.id_order =hist2.id_order AND t.date_add =hist2.date_add';

$models = OrderHistory::model()->findAll($criteria);