mysql - 编写 CakePHP 查找连接

Question

我有一个 SQL 查询，正在尝试将其转换为 CakePHP 查找，但我不确定如何构造它...有人可以提供一点帮助吗？

SELECT texts.*, people.id, people.first_name, people.last_name FROM texts 
    NATURAL JOIN (
         SELECT person_id, MAX(id) AS id
         FROM texts 
         WHERE texts.status = 'received' 
         GROUP BY person_id
    ) t RIGHT JOIN people ON people.id = t.person_id
    WHERE texts.body is not null
    AND texts.created > '$since'
    AND person.counselor_id = '2' 
    ORDER BY texts.created DESC

这是我所拥有的

    $texts = $this->find('all', array(
        'recursive' => -1,
        'joins' => array(
            array(
                'table' =>  'texts',
                'alias' =>  't',
                'type'  =>  'NATURAL',
                'conditions'    =>   array('t.status' => 'received')
            ),
            array(
                'table' => 'people',
                'alias' => 'Person',
                'type' => 'RIGHT',
                'conditions' => 'people.id = t.person_id'
            )   
        ),
        'conditions' => array('AND' => array('Text.body IS NOT NULL', 'Text.created > 0000-00-00 00:00:00')),
        'order' => 'Text.created DESC'
    ));

这是它编写的SQL

SELECT Text.id, Text.person_id, Text.sid, Text.to, Text.from, Text.body, Text.status, 
     Text.direction, Text.owner, Text.counselor_read, Text.created, Text.modified 
FROM admissionsedge_penfield.texts AS Text 
NATURAL JOIN admissionsedge_penfield.texts 
AS t ON (t.status = 'received') 
RIGHT JOIN admissionsedge_penfield.people AS Person ON (people.id = t.person_id) 
WHERE ((Text.body IS NOT NULL) AND (Text.created > 0000-00-00 00:00:00)) 
ORDER BY Text.created DESC

谢谢!

Answer 1

你有定义任何模型吗？如果是这样，您需要共享它们，以及您希望执行查找的那些，因为通常不使用右连接并且不使用自然连接。

假设你不...

在 Controller 中，使用此代码运行上面的查询。 {$recordset 赋值行通常是 find 方法所在的位置}

$some_sql = 'your sql statement';
$db = ConnectionManager::getDataSource('default');
$recordset = $db->rawQuery($some_sql);
set('recordset', $recordset);

如果你想利用 CakePHP 的 MVC，那么我建议将此查询重写为左连接和内连接