Propel ORM - Joining a table to itself without a foreign key

Question

I'm just getting started with Propel. I have a situation where I am left joining a table to itself to obtain a min value. I have the query written that works as I need it to, but I can't figure out how to do it using the Propel models.

This query gets the first successful payment made by each user that registered after a given date:

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

Please help me translate that to Propel code.

Answer 1

Try this:

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

I want to use the add method for having columns equal each other, but this cannot be done because it will convert the second column into a string. I've asked this on the Propel Users Google Group with no response so far. Therefore, I'm not sure if the first and fourth where clauses will work.