CODEWITHSUNDEEP
yii
Brett Gregson
Brett Gregson

Reputation: 5923

Yii order by another table CActiveDataProvider

I am a bit stumped on this. Basically I have two tables:

Page:

Points:

I am looking to get the records from the Page table, and sort it by the amount of points it has in the Points table (the points field)

Currently I have:

$dataProvider=new CActiveDataProvider('Page',array(
        'criteria'=>array(
            'condition'=>"active = 1 AND userid IN (".$ids.")",
            'order'=>"???",
        ),

        'pagination'=>array(
            'pageSize'=>30,
        ),
    ));

I just don't know how to sort it by the Points table value for the relevant record I have set up a relation for the Page/Points tables like so:

(in the Page model)

'pagepoints' => array(self::HAS_ONE, 'Points', 'pageid'),

Thanks

Upvotes: 3

Views: 9341

Answers (3)

Neil McGuigan
Neil McGuigan

Reputation: 48297

this is the sql you want to generate:

select * from page inner join points on page.id = points.page_id order by points.points desc

Upvotes: 0

Jon
Jon

Reputation: 437904

You need to do two things:

  1. Add the pagepoints relation to the with part of the query criteria
  2. Reference the column you want to sort by in the order part of the criteria

I 've marked the lines where this happens in the code below:

$dataProvider = new CActiveDataProvider('Page', array(
    'criteria'=>array(
        'with' => array('pagepoints'),  // #1 
        'condition' => 'active = 1 AND userid IN ('.$ids.')',
        'order' => 'pagepoints.points', // #2
    ),

    'pagination'=>array(
        'pageSize'=>30,
    ),
));

What you need to know to understand how this works is that when Yii builds the SQL query (which is a LEFT OUTER JOIN to the Points table), it uses the name you gave to the relation in the Page model (you give the definition for this, it's pagepoints) to alias the joined table. In other words, the query looks like:

SELECT ... FROM Page ... LEFT OUTER JOIN `Points` `pagepoints` ...

It follows that the correct specification for the sort order is pagepoints.points: pagepoints is the table alias, and points is the column in that table.

Upvotes: 4

Elitmiar
Elitmiar

Reputation: 36899

Try the following

$dataProvider=new CActiveDataProvider('Page',array(
        'criteria'=>array(
            'with'=>array('pagepoints'),
            'condition'=>"active = 1 AND userid IN (".$ids.")",
            'order'=>"t.points DESC",
        ),

        'pagination'=>array(
            'pageSize'=>30,
        ),
    ));

Upvotes: 1

Related Questions