CODEWITHSUNDEEP
phpsqlyiisphinxcriteria
Ionut Flavius Pogacian
Ionut Flavius Pogacian

Reputation: 4811

How to order data received from SPHINX when using Yii Criteria?

As SPHINX returns a list of id's, i cant see how to order my final results by using a criteria.

I have an array that contains id's:

$a = array(1,2,3,4,5);

This array of id's was given by SPHINX search engine as a result.

I want to use this array and extract the needed data by id as PK, but i have to order the results like this:

users with videos and photos before all users with just photos, users with photos next in the list, then those users with photos should be sorted by when they most recently logged in, with the most recent logins at the top of the list. After all users with photos, the rest of the users should be there, with the most recently logged in first.

I tried this, with no order fields, as i don't know how to write the code;

$criteria = new CDbCriteria();
$criteria->addInCondition('id', $a);
$criteria->order = "FIELD(id, " . join(',', $a) . ")";

$dataProvider = new CActiveDataProvider('user', array(
                    'criteria' => $criteria,
                    'pagination' => array(
                    'pageSize' => $this->pageSize,
                    ),
                ));

Anyone tried something like this ? or another suggestions ?

Upvotes: 1

Views: 500

Answers (1)

frostyterrier
frostyterrier

Reputation: 1912

Assuming you have relations set up for videos and photos, you could try something like this:

$criteria = new CDbCriteria;
$criteria->order = '
    IF(videos.id IS NOT NULL AND photos.id IS NOT NULL, 1, 0) DESC,
    IF(photos.id IS NOT NULL, t.lastvisit, 0) DESC,
    IF(videos.id IS NULL AND photos.id IS NULL, t.lastvisit, 0) DESC';
$criteria->group = 't.id';

$users = User::model()->with(array('photos', 'videos')))->findAllByPk($c, $criteria); 

$dataProvider = new CArrayDataProvider($users, array(
    'pagination' => array(
        'pageSize' => $this->pageSize,
    ),
));

Make sure that you change lastvisit to the field you're using to store their last log on time.

I am also using Yii + Sphinx and have user models with photos and videos relations so I was able to test this out and it worked for me, but it's possible that I got lucky somehow and my data ordered itself. Still, I think this will give you a good start.

[edit] Also, to optimize this a bit I would recommend creating a scope in the photo and video models specifically for Sphinx that only selects the id from the photos and videos tables. Then to use it you would do:

User::model()->with(array('photos:sphinx', 'videos:sphinx')))->findAllByPk($c, $criteria);

Upvotes: 1

Related Questions