Querying From Database

Hello

I want to create a user system, where logged in user see menu links based on his role.

I have 4 tables: User >|----|| Role ||----|< AdminRoleHasPage >|----|| Pages


CREATE TABLE IF NOT EXISTS `AdminUser` (

  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

  `username` varchar(20) NOT NULL,

  `password` varchar(40) NOT NULL,

  `role_id` tinyint(2) unsigned NOT NULL,

  `status` enum('0','1') NOT NULL DEFAULT '1',

  PRIMARY KEY (`id`),

  UNIQUE KEY `username` (`username`),

  KEY `fk_role` (`role_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;


CREATE TABLE IF NOT EXISTS `AdminRole` (

  `id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,

  `role_name` varchar(25) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `title` (`role_name`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;




CREATE TABLE IF NOT EXISTS `AdminRoleHasPage` (

  `admin_role_id` tinyint(2) unsigned NOT NULL,

  `admin_page` varchar(20) NOT NULL,

  KEY `fk_page` (`admin_page`),

  KEY `fk_role` (`admin_role_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `AdminPage` (

  `page` varchar(20) NOT NULL,

  `title` varchar(24) NOT NULL,

  `parent_page` varchar(20) DEFAULT NULL,

  `sort` tinyint(2) DEFAULT NULL,

  PRIMARY KEY (`page`),

  UNIQUE KEY `title` (`title`),

  KEY `fk_page_parent` (`parent_page`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Basically User have one Role and different Roles have many Pages.

Also i have AdminUser model, where is relation:


'role' => array(self::BELONGS_TO, 'AdminRole', 'role_id')

and AdminRole model, where is relation:


'page' => array(self::MANY_MANY, 'AdminPage', 'AdminRoleHasPage(admin_role_id, admin_page)'),

What is the best and efficient way to query all user menu links what his role has?

I was trying something like that in controller:


CVarDumper::dump(   AdminUser::model()->with('role.page')->findAllByPk(Yii::app()->user->getId())   ,10,true);

But it selects all user data also.