如何在CakePHP中编写查询？

Code:

mysql_query("
    SELECT
        message,to_id,from_id,MAX(created) AS created
    FROM
        ".MEMBER_MSG."
    WHERE
            (to_id='".$uid."' OR from_id='".$uid."')
        AND
            from_id != '".$uid."'
        AND
            (to_delete='1' OR from_delete='1')
        AND
            is_chat='1'
    GROUP BY
        from_id,to_id
    ORDER by
        created DESC
");

I want to write above query in CakePHP. How to write in CakePHP?

Edit:

My CakePHP code:

$condition = array(
    'OR' => array(
        'AND' => array(
            'Message.from_id' => $this->Session->read('Auth.User.id'),
            'Message.to_id' => $this->Session->read('Auth.User.id')
        ),
        array(
            'AND' => array(
                'Message.from_id' => $userid['User']['id'],
                'Message.to_id' => $this->Session->read('Auth.User.id')
            )
        )
    ),
    'is_chat' => '1',
    array(
        'OR' => array(
            'AND' => array(
                'Message.from_id' => $this->Session->read('Auth.User.id'),
                'Message.from_delete' => '1'
            ),
            array(
                'AND' => array(
                    'Message.to_id' => $this->Session->read('Auth.User.id'),
                    'Message.to_delete' => '1'
                )
            )
        )
    )
);

This code is working but not getting the accurate result

MySQL Code:

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_id` int(11) NOT NULL,
  `to_id` int(11) NOT NULL,
  `message` text NOT NULL,
  `is_view` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-Not Viewed, 1-Viewed',
  `is_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=No, 1=Yes',
  `is_chat` tinyint(4) NOT NULL COMMENT '0=No, 1=Yes',
  `from_delete` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0-Deleted by From, 1-Not',
  `to_delete` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0-Deleted by To, 1-Not',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `reply_count` smallint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `from_id` (`from_id`),
  KEY `to_id` (`to_id`),
  KEY `from_delete` (`from_delete`),
  KEY `to_delete` (`to_delete`),
  KEY `is_view` (`is_view`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

If you want make it easy:

CakeBook: raw query

$this->Message->query("SELECT message,to_id,from_id,MAX(created) AS created FROM ".MEMBER_MSG." WHERE (to_id='".$uid."' OR from_id='".$uid."') AND from_id != '".$uid."' AND (to_delete='1' OR from_delete='1') AND is_chat='1' GROUP BY from_id,to_id ORDER by created DESC;");

But if you want make something more cakeway:

CakeBook: find

In more complex query sometimes you can't make it easy. Sometimes you only have to use raw query, or rethink your query.

Probably the problem is that inside your or statements you have some values without keys, giving you unproper answer.

Maybe this conditions will help. Note that I did it based on your mysql query rather than your cake $conditions variable:

$conditions = array(
    'OR' => array(
        'to_id' => $uid,
        'from_id' => $uid
    ),
    'from_id !=' => $uid,
    'OR' => array(
        'to_delete' => 1,
        'from_delete' => 1
    ),
    'is_chat' => 1
);

Still, it's contradictory that you have conditions from_id = 1 and from_id != 1