Joining tables

Yii 1.1 General Discussion
1

Hi all,

Im having trouble figuring out how join two tables and get the required information in Yii.

My two tables are:


CREATE TABLE `tbl_levels` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `status` int(1) NOT NULL,

 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,

 `path` text COLLATE utf8_unicode_ci NOT NULL,

 `stage` int(11) NOT NULL,

 PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci



CREATE TABLE `tbl_scores` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `user_id` int(11) NOT NULL,

 `level_id` int(11) NOT NULL,

 `completed` int(11) NOT NULL DEFAULT '0',

 `fastest` int(11) NOT NULL,

 `longest` int(11) NOT NULL,

 `total_time` int(11) NOT NULL,

 `attempted` int(11) NOT NULL DEFAULT '0',

 `unlock_level_id` int(11) NOT NULL,

 PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

My relations in Scores


public function relations()

	{

	        // NOTE: you may need to adjust the relation name and the related

	        // class name for the relations automatically generated below.

	        return array(

	                'unlocked' => array(self::HAS_MANY, 'Levels', 'id'),

	        );

	}

Whole aim is to get a list of unlock_level_id (from tbl_scores) and print out the level names (from tbl_levels).

This is probably very simple but I can’t seem to get my head around doing this :(

2

The foreign key is in tbl_scores so relations are:

Levels HAS_MANY Scores

Scores BELONGS_TO Levels

Always define the foreign key name instead of primary key name in relation definition. Primary key name is already known by CActiveRecord.

The code seems like a Gii-generated code. Tip: if you define foreign keys in the database (always good idea and InnoDB allows you to do it) relations will be figured out automatically by Gii.

3

Thanks, this helped a lot.