Search code examples
phpsqlsymfony-1.4propel

How to get the next record that is greater than or equal to the current one in symfony?

Here's my code:

$c = new Criteria();
$c->addAscendingOrderByColumn(ItemPeer::ITEM_DATE);
$c->add(ItemPeer::ITEM_DATE, $item->getItemDate(), Criteria::GREATER_THAN);
$this->next = ItemPeer::doSelectOne($c);

This works fine except when multiple dates are the same, for example: 3/1/2013 and 3/1/2013

When this happens it doesn't select the next one. For example (sorted by date):

Apple     2/27/2013
Banana    2/28/2013
Kiwi      3/1/2013
Dolphin   3/1/2013
ICBM      3/1/2013

If the current item is Banana, the next one will be correctly chosen as Kiwi. However, if the current item is Kiwi, it will be unable to find the next one. I'd like it to always choose the next one in date, even if the next date is the same. How can I do this?

Solution

  • Use Criteria::GREATER_EQUAL instead of Criteria::GREATER_THAN

    EDIT: Ah, I see your problem now, you should add a second ordering criteria. Perhaps use the primary key as the second criteria, so you get a consistent ordering, then use the second ordering to determine the next item. Basically, you need the equivalent of select ... from ... where (item_date > mydate) or (item_date = mydate and id > myid) order by date, id asc in symfony.

    EDIT2:

    $c = new Criteria();
// [item.item_date > $item->getItemDate()]
$crit0 = $c->getNewCriterion(ItemPeer::ITEM_DATE, $item->getItemDate(), Criteria::GREATER_THAN);
// [item.item_date = $item->getItemDate()]
$crit1 = $c->getNewCriterion(ItemPeer::ITEM_DATE, $item->getItemDate());
// [item.id > $item->id]
$crit2 = $c->getNewCriterion(ItemPeer::ID, $item->getId(), Criteria::GREATER_THAN);

// [item.item_date = $item->getItemDate()] *AND* [item.id > $item->getId()]
$crit1->addAnd($crit2);

// [item.item_date > $item->getItemDate()] *OR* [[item.item_date = $item->getItemDate()] AND [item.id > $item->getId()]]
$crit0->addOr($crit1);

$c->add($crit0);
$c->addAscendingOrderByColumn(ItemPeer::ITEM_DATE);
$c->addAscendingOrderByColumn(ItemPeer::ID);

$this->next = ItemPeer::doSelectOne($c);