Active record external parameter in join clause

Archives Yii 1.0.x Discussions
1

Hello,

I want to join two tables with additional parameter (application language), so my query should look like:


select pl.id, p.lang from pagelang pl

left join page p

on p.pageid = pl.id and p.lang = 'en';

And result set will contain nulls if page does not exist in current language:


id | lang

-----------

1  | en

2  | en

3  | <null>

4  | en

Is there any way to do this?

I cant add lang to where clause as it will filter empty pages.

I need this to know which pages are not available in current language so user can add them.

2

Yes that’s possible, but could you show some code? E.g. do you use ActiveRecords for both page and pagelang? Did you define a relation?

3

Page class contains language depended data, PageLang stores common data. Here is structure:

Page relations:


<?php	public function relations()

	{

		return array(

			'page' => array(self::BELONGS_TO, 'Page', 'pageId'),

		);

	}

PageLang relations (autogenerated was ‘page’ => array(self::HAS_MANY, ‘Page’, ‘pageId’) but with additional lang parameter it becomes 1:1 relation):


<?php	public function relations()

	{

		return array(

//			'page' => array(self::HAS_MANY, 'Page', 'pageId'),

			'pages' => array(self::HAS_ONE, 'Page', 'pageId'),

		);

	}?>

I use it with CActiveDataProvider:


<?php   $dataProvider=new CActiveDataProvider('PageLang', array('criteria' => array(

                 'with' => array('pages'),

                 'condition' => 'pages.lang = :lang',

                 'params' => array(':lang' => Yii::app()->language),

                 'order' => 'pages.title ASC',

  )));?>

With ‘condition’ => ‘pages.lang = :lang’ it returns all existing pages for current language. What i miss here is some additional instruction on join clause. Yii generates query:


SELECT *

FROM `PageLang` `t`

LEFT OUTER JOIN `Page` `pages`

  ON (`pages`.`pageId`=`t`.`id`)

WHERE (pages.lang = 'en')

ORDER BY pages.title ASC LIMIT 10;

But i want to move lang parameter to join like that:


SELECT *

FROM `PageLang` `t`

LEFT OUTER JOIN `Page` `pages`

  ON (`pages`.`pageId`=`t`.`id` AND `pages`.`lang` = 'en')

ORDER BY pages.title ASC LIMIT 10;

Then in grid view i want add links to create new page if lang is null otherways it will point to update action.

4

Try this:


<?php   $dataProvider=new CActiveDataProvider('PageLang', array('criteria' => array(

                 'with' => array(

                     'pages' => array(

                         'condition'=>'lang=:lang',

                         'params' => array(':lang'=>Yii::app()->language),

                     ),

                 ),

                 'order' => 'pages.title ASC',

  )));?>

EDIT: Sorry, typo…

5

It generates same query, with WHERE (lang=‘en’). Is there any way to add it to relations definition?

6

Then try ‘on’ instead of ‘condition’ (see here for more options: http://www.yiiframework.com/doc/guide/database.arr#relational-query-options).

You can also specify that in your relations(), yes. If defined there, you could also use ‘condition’ again. It should then be used in your ON clause for the related table.

7

Thanks a lot, that solved my problem. My relation now looks like that:


<?php

'pages' => array(self::HAS_ONE, 'Page', 'pageId', 'on' => 'lang=:lang', 'params' => array(':lang' => Yii::app()->language)),?>