php - PROPEL - 将 sql 查询传输到 propel

Question

我在将 sql 查询转换为 propel 查询时遇到问题。

查询看起来像(我之前已经要求过此查询:Changing a sql query using join instead of a subselect)

SELECT cr.* 
FROM confirmation_requests cr
LEFT JOIN confirmations c ON (cr.id = c.confirmation_request_id AND c.device_id = 1)
WHERE c.id IS NULL;

所以我尝试将此查询转移到 propel，如下所示:

$confirmationRequests = ConfirmationRequestQuery::create()
                    ->leftJoin("Confirmation c")
                        ->addJoinCondition("c","c.device_id = ?",$device->getId())
                    ->where("c.id IS NULL")
                    ->find();

但是这不起作用。

来自此 propel 查询的 sql 查询:

SELECT confirmation_requests.id, 
    confirmation_requests.customer_id, 
    confirmation_requests.confirmation_type_id, 
    confirmation_requests.file_path, 
    confirmation_requests.user_id, 
    confirmation_requests.state, 
    confirmation_requests.created_at, 
    confirmation_requests.updated_at 
FROM `confirmation_requests` 
CROSS JOIN `confirmations` LEFT JOIN `confirmations` `c` ON (confirmation_requests.id=c.confirmation_request_id AND confirmations.user_id = 10) 
WHERE confirmations.id IS NULL

Answer 1

Propel 喜欢在架构文件中定义表关系，因此，从架构文件开始并创建外键关系(根据数据中存在的关系类型添加选项):

<table name='confirmation_request' ...
  <column name='id' ....
  ...
</table>

<table name='confirmation' ...
  ...
  <column name='cr_id' ...
  <foreign-key foreignTable='confirmation_request' ...
    <reference local='cr_id' foreign='id' ...
  </foreign-key>
  ...
</table>

然后你可以编写如下查询:

$crs = ConfirmationRequestQuery::create()
        ->join('ConfirmationRequest.Confirmation')
        ->where('Confirmation.UserId = 10')    // Add other conditions as required
        ->where('Confirmation.DeviceId = ?', $define->getId())
        ->where('Confirmation.Id IS NULL')
        ->find();