0

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?

John Smith
  • 8,567
  • 13
  • 51
  • 74
  • You shouldn't be asking the same question twice: http://stackoverflow.com/questions/15147694/how-to-retrieve-the-next-item-in-a-list-of-mysql-records – Jordan Kasper Mar 01 '13 at 14:45

1 Answers1

1

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);
Community
  • 1
  • 1
Lie Ryan
  • 62,238
  • 13
  • 100
  • 144
  • That was my first thought and I tried it but it just sets `$next` to the current `$item` every time. So if I was on `Banana`, with `GREATER_EQUAL` it sets `$next` to `Banana`. – John Smith Mar 01 '13 at 05:15
  • I tried something like this but it didn't seem to fix it: `$c->addAscendingOrderByColumn(ItemPeer::ITEM_ID)->addAscendingOrderByColumn(ItemPeer::ITEM_DATE);` I tried `GREATER_EQUAL` and `GREATER_THAN`, as well as sorting by `DATE` and then `ID` – John Smith Mar 01 '13 at 05:27
  • @JohnSmith: unfortunately, I don't know enough about Propel to do this off the top of my head, try something like the code I've added above. – Lie Ryan Mar 01 '13 at 06:15