CakePHP Translate: Getting group by latest date

Question

How do I translate below to cakePHP code? The code below comes from a solution here MySQL order by before group by.

Getting the author's latest post using group by.

 SELECT p1.* 
    FROM wp_posts p1
    INNER JOIN
    (
        SELECT max(post_date) MaxPostDate, post_author
        FROM wp_posts
        WHERE post_status='publish'
           AND post_type='post'
        GROUP BY post_author
    ) p2
      ON p1.post_author = p2.post_author
      AND p1.post_date = p2.MaxPostDate
    WHERE p1.post_status='publish'
      AND p1.post_type='post'
    order by p1.post_date desc

Below is still similar situation:

SELECT t1.* FROM payment_status t1
  JOIN (SELECT payment_id, MAX(created) max_created
        FROM payment_status
        GROUP BY payment_id
        ) t2
    ON t1.payment_id = t2.payment_id AND t1.created = t2.max_created;

I need some cakePHP translation for either the two mySQL statements.

------------------------------------------------------------------------------------

---

I did something like the code below but it gives me error

Error: SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'SELECT max(dateEncoded) maxDate, findings FROM maintain GROUP BY computer_id Office.main_office LIKE' is too long

How do I fix it?

 $this->Computer->unbindModel(array(
                'belongsTo' => array('Office'),
                'hasMany' => array('Brand','Maintain')
            ));


            $model_view = $this->Computer->bindModel(array(
                    'hasOne' => array(
                        'Office' => array(
                            'foreignKey' => false,
                            'conditions' => array('Office.id = Computer.office_id')
                        ),
                        'Maintain' => array(
                            'foreignKey' => false,
                            'conditions' => array('Computer.id = Maintain.computer_id'),
                        )
                    )
                )
            );

            $main_office = trim($this->request->data['Office']['office_id']);
            $joins = array(
                array(
                    'table' => "SELECT max(dateEncoded) maxDate, findings FROM maintain GROUP BY computer_id",
                    'alias' => 'P2',
                    'type' => 'INNER',
                    'conditions' => array('Maintain.findings = p2.findings','Maintain.dateEncoded = p2.maxDate')
                )
            );
            $conditions=array("Office.main_office LIKE"=>"%$main_office%");



            $result = $this->Computer->find('all',array(
                $model_view,
                'joins'=>$joins,
                'conditions'=>$conditions,
                'order' =>  array('Office.description'),
                'group' =>  'Computer.id'
            ));

Answer 1

This can be written something like this :-

$joins = array(
                array(
                    'table' => 'SELECT max(post_date) MaxPostDate, post_author FROM wp_posts WHERE post_status='publish' AND post_type='post'GROUP BY post_author',
                    'alias' => 'P2',
                    'type' => 'INNER',
                    'conditions' => array('WpPost.post_author = p2.post_author','WpPost.post_date = p2.MaxPostDate')
                )
            );
$conditions=array("WpPost.post_status='publish'","WpPost.post_type='post'");

$this->WpPost->find('all',array('fields'=>array('WpPost.*'),'joins'=>$joins,'conditions'=>$conditions);