how to create criteria with join in yii

Question

I have to tables, one is profile and the other is rating. rating has a field profileid which is primary key to profile.id and a field with rating values. Now I want to find the field with highest rating and display the corresponding profile. Since I'm new to YII framework I'm having troubles with it. Please help me getting out of it. What I'm doing is described below.

$topRage=new CDbCriteria();
$topRage->select="*";
$topRage->alias="t1";
$topRage->order="rateing DESC";
$topRage->join="JOIN `ratings` ON `profile`.`id` = `t1`.`profileId`";
$topRage->limit="1";

Solution 1

Try this :

join='JOIN profile ON profile.id = t1.profileId';

If you are doing this: Ratings::model()->findAll($topRage) , then ratings table is already being queried, so you need to join with profile table.

Edit:

for echo you'll need to do this:

$echo "Rating id: ".$rating->id."|&nbspProfile Id: ".$rating->profile->id."|&nbspProfile Name: ".$rating->profile->name."|&nbspRating: ".$rating->ratingvalue;

Don't forget to pass $rating from the controller though.

You could also use find($topRage) instead of the findAll($topRage) and remove the limit, but that 's just another way of doing the same thing.

Solution 2

just query without join .

$topRage=new CDbCriteria();
$topRage->select="*";
$topRage->alias="t1";
$topRage->order="rateing DESC";
$topRage->limit="1";

$rating=Ratings::model()->findAll($topRage);
$profile=Profile::model()->findByPk($rating->profileId);

Comments

[Bipin Chandra Tripathi]

its working so now how i will now echo the content of profile table

[bool.dev]

@Bipin i have edited my answer, with echo statements, check it out