Understanding in performing a relational query.

Archives Yii 1.0.x Discussions
1

I am trying to build a relational query but not sure how.

I have two models, Page and Menu where a menu can have many pages but each page can only belong to one menu.

I have set-up the relationships as shown

class Menu


	public function relations()


	{


		return array(


		'page'=>array(self::HAS_MANY, 'Page', 'pageId', 'order'=>'??.listOrder'),


		);


	}



class Page


	public function relations()


	{


	    return array(


	        'menu'=>array(self::BELONGS_TO, 'Menu', 'menuid',


	            'joinType'=>'INNER JOIN'),


	    );


	}

The Page table has a column menu (int) that relates to menu.id of the menu table. I need to write a query that returns pages where page.status='1' and menu.name is of a certain string.

How would I use the Yii framework to return the results I’m after? I have been reading http://www.yiiframew…de/database.arr but I am lost.

2 


$pages=Page::model()->with('menu')->findAll('page.status=1 AND ??.name=:name', array(':name'=>$name));


foreach($pages as $page)


{


    $page->menu...


}
3

Thanks for the reply.

I have tried this but are getting errors. I have rewritten some of the relations as I noticed that the mysql code for the Yii-blog was a little wrong in the relations() method from which I was copying.

Therefore I am left with this.



class Menu


	public function relations()


	{


		return array(


		'page'=>array(self::HAS_MANY, 'Page', 'id', 'order'=>'??.listOrder'),


		);


	}





class Page


	public function relations()


	{


	    return array(


	        'menu'=>array(self::BELONGS_TO, 'Menu', 'id',


	            'joinType'=>'INNER JOIN'),


	    );


	}

Therefore in sql Page.menu=Menu.id but I want to filter on Menu.alias='main_menu' for example.

I have modified your example to suit as follows:

$pages=Page::model()->with('menu')->findAll('Page.status=1 AND ??.alias=:alias', array(':alias'=>$this->menuAlias));

This however is returning an error that states the error "near '??.alias='main_menu')'"

Should the framework be replacing ??. with the table name? I tried putting in Menu.alias in it's place but it returned an error stating that the column did not exist. I guess I'm almost there but missing something.

4

In your Page class, you may specify "alias" option for the "menu" relation. And then in your query, you can use that alias instead of "??".

5

I'm so confused now I'm just guessing. Do you mean set the relation method to

	public function relations()


	{


	    return array(


	        'menu'=>array(self::BELONGS_TO, 'Menu', 'id', 'with'=>'alias',


	            'joinType'=>'INNER JOIN'),


	    );


	}

In the Page class and set the query to

$pages=Page::model()->with('menu')->findAll('Page.status=1 AND `alias`=:alias', array(':alias'=>$this->menuAlias));

?

It's unfortunate that I have used alias as the column name. Once I have resolved this I will change it.

6

Nope, I mean



class Page


   public function relations()


   {


       return array(


           'menu'=>array(self::BELONGS_TO, 'Menu', 'id',


               'alias'=>'menu',


               'joinType'=>'INNER JOIN'),


       );


   }

Then, your query would be:



$pages=Page::model()->with('menu')->findAll('Page.status=1 AND menu.alias=:alias', array(':alias'=>$this->menuAlias));
7

Thanks again for your response.

The relation you had was executing the query

....... INNER JOIN `Menu` menu ON (`Page`.`id`=menu.`id`) WHERE (Page.status=1 AND menu.alias='main_menu')

where Page.id was incorrect as I needed it to be Page.menu.

I have fixed that in the relation to

   public function relations()


   {


       return array(


           'menu'=>array(self::BELONGS_TO, 'Menu', 'menu',


               'alias'=>'menu',


               'joinType'=>'INNER JOIN'),


       );


   }

and now it's working as I require.