mysql - Propel ORM - 在没有外键的情况下将表连接到自身

Question

我刚刚开始使用 Propel。我的情况是 left joining a table to itself to obtain a min value .我编写了可以按需运行的查询，但我无法弄清楚如何使用 Propel 模型来完成它。

此查询获取在给定日期后注册的每个用户的首次成功付款:

SELECT `p`.`id` AS `payment_id`,
    `p`.`request_date`,
    `u`.`id` AS `user_id`,
    `u`.`registration_date`
FROM `payments` AS `p`
LEFT JOIN `payments` AS `filter`
    ON `p`.`user_id` = `filter`.`user_id`
    AND `p`.`id` > `filter`.`id`
INNER JOIN `users` AS `u`
    ON `p`.`user_id` = `u`.`id`
    AND `u`.`registration_date` >= '2013-07-28'
WHERE `p`.`completed` = 1
AND `filter`.`id` IS NULL
ORDER BY `u`.`registration_date` DESC

请帮我把它翻译成 Propel 代码。

Answer 1

试试这个:

<?php
$q = \PaymentsQuery::create();
$q->select(array('Payments.RequestDate', 'Users.RegistrationDate'));
$q->withColumn('Payments.Id', 'payment_id');
$q->withColumn('Users.Id', 'user_id');
$q->withAlias('Filter', \PaymentsPeer::TABLE_NAME);
// The object to join must ALWAYS be on the right side
$q->addJoin(\PaymentsPeer::USER_ID, \PaymentsPeer::alias('Filter', \PaymentsPeer::USER_ID), \ModelCriteria::LEFT_JOIN);
$q->addJoin(\PaymentsPeer::USER_ID, \UsersPeer::ID, \ModelCriteria::INNER_JOIN);
$q->where('Payments.Id > Filter.Id');
$q->where('User.RegistrationDate >= ?', '2013-07-28');
$q->where('Payments.Completed = ?', 1);
$q->where('Filter.Id IS NULL');
$q->orderBy(\UsersPeer::REGISTRATION_DATE, \ModelCriteria::DESC);

我想使用 add 方法使列彼此相等，但这无法完成，因为它会将第二列转换为字符串。 I've asked this在 Propel Users Google Group 上，到目前为止没有任何回应。因此，我不确定第一个和第四个 where 子句是否有效。