PROPEL --将sql查询传输到propel

Question

我在将我的sql查询转换为推进查询时遇到了问题。

这个查询看起来像(我之前已经问过这个查询：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();

但不起作用。

来自此推进查询的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喜欢在Schema文件中定义表关系，因此，从模式文件开始并创建外键关系(根据数据中存在的关系类型添加选项)：

<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();

Answer 2

Propel总是给您编写SQL代码的后盾，参见http://propelorm.org/blog/2011/02/02/how-can-i-write-this-query-using-an-orm-.html中的答案#2。