nearest greatest id from table

Yii 1.1 General Discussion
1

I have a mode MyModel for db table MyTable. There is a primary key "id".

Given $id. I need to get nearest record (model) from db where "id > $id" and "id < $id".

So, if there are 3 records in "mytable" with ids = 2, 5 , 7 , 8 and it is given $id=5,

I need to be able to find model (actually id of this model) with id = 7 (nearest greater) and with id=2 (nearest smaller).

If there are no data, then nothing should be found. For example, if given $id=8, then there is no nearest greatest value.

I can use something like:




$model = MyModel::model()->find('id > '.$id);

This will really return only one model where id is greater, but I am not sure that this gives nearest greatest value.

R.

2

One quick and sure way to do this is to query for a range of records, with your target id in the middle.

And then iterate over the returned result, which is an array.

Store previous id for each iteration until you reach your target, and then you just need to iterate one more time to get the next id.

You could also use this:

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#offsetExists-detail

3

Perhaps something like this: first query (like you did) for a greater id value, id2 > id1, then retrieve the interval [id1, id2] and check if it consists of exactly two records.

/Tommy

4

To get the nearest ID don’t forget to order by ID…

The SQL for this would be:


select * from table where id > $id order by id limit 1
5

Thx, it works now.




$models = MyModel::model()->findAll('id < '.$id, array('order'=>'id'));        

    if (!$models)

    {

      echo 'no next';

      exit;

    }

    // get first 

    $model = $models[0];

    $prevId = $model->id;