ActiveRecord - select trought 3 tables

Hello, I’m new to Yii. In my previous experience I created multilanguage application with help of Doctrine 1.2. Now I’m trying “port” this application to Yii with it’s AR models. DB schema is as following figure:

So basicaly - I got a PAGE entity, which is in tree (nested set). PAGE has_many of PAGE_I18Ns. In i18ns are saved localized name & url of the PAGE. i18n record is also related to LANGUAGE (i18n has_one lang). Please DON’T pay attention to other tables in schema (menus, content, content_i18n)!

So what I basicaly need is to “fetch” (using eager loading approach to save SQL queries) a particular PAGE and coresponding PAGE_I18N record in particular LANGUAGE (identified by languages.code). Remember - I must “fetch” this data using eager loading approach by Yii AR models (don’t wana use third party ORM).

BTW here is a working SQL query, which fullfill my needs:

[sql]

SELECT

pages.id AS page_id,

pages_i18n.id AS pageI18N_id, pages_i18n.name, pages_i18n.url,

languages.id AS lang_id, languages.code

FROM pages

LEFT JOIN pages_i18n ON pages.id = pages_i18n.page_id

LEFT JOIN languages ON pages_i18n.language_id = languages.id

WHERE pages.id = 2 AND languages.code = ‘sk’

[/sql]

Models definitions follows:




class Pages extends CActiveRecord

{

	/**

	 * The followings are the available columns in table 'pages':

	 * @var integer $id

	 * @var integer $menu_id

	 * @var integer $root_id

	 * @var integer $lft

	 * @var integer $rgt

	 * @var integer $level

	 * @var integer $visible

	 * @var integer $contactForm

	 */


	public function relations()

	{

		return array(

			//'contents' => array(self::HAS_ONE, 'Content', 'page_id'),

			//'menu' => array(self::BELONGS_TO, 'Menus', 'menu_id'),

			'root' => array(self::BELONGS_TO, 'Pages', 'root_id'),

			'pages' => array(self::HAS_MANY, 'Pages', 'root_id'),

			'pages_i18ns' => array(self::HAS_MANY, 'PagesI18n', 'page_id'),

			//'images' => array(self::MANY_MANY, 'Images', 'pages_images(page_id, image_id)'),

			//'products' => array(self::MANY_MANY, 'Products', 'pages_products(page_id, product_id)'),

		);

	}

}





class PagesI18n extends CActiveRecord

{

	/**

	 * The followings are the available columns in table 'pages_i18n':

	 * @var integer $id

	 * @var integer $page_id

	 * @var integer $language_id

	 * @var string $name

	 * @var string $url

	 */


	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(

			'language' => array(self::BELONGS_TO, 'Languages', 'language_id'),

			'page' => array(self::BELONGS_TO, 'Pages', 'page_id'),

		);

	}

}





class Languages extends CActiveRecord

{

	/**

	 * The followings are the available columns in table 'languages':

	 * @var integer $id

	 * @var string $code

	 * @var string $name

	 */


	/**

	 * @return array relational rules.

	 */

	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(

			//'content_i18ns' => array(self::HAS_MANY, 'ContentI18n', 'language_id'),

			'pages_i18ns' => array(self::HAS_MANY, 'PagesI18n', 'language_id'),

			//'products_i18ns' => array(self::HAS_MANY, 'ProductsI18n', 'language_id'),

		);

	}

}



Here is also some basic approach I tryied




$c = new CDbCriteria;

$c->addCondition('visible=true');

$c->addCondition('contactForm=false');

$c->addCondition('level!=0');

$c->with  = 'pages_i18ns';

$c->order = 'id ASC';

$c->limit = 1;


$result = Pages::model()->find($c);



Half of a kindom for working solution.

THX.

I believe what you’re looking to do is best suited with the relational active record querying. By the looks of it you already setup the relations for each model (or customized them based of what yii already generated for you from proper db structure ;) )

Have a look at this page:

http://www.yiiframework.com/doc/guide/database.arr

A little ways down you’ll see:

It’ll give you the basic idea of how you can query from one model to the next to combine information. Here’s the sample




// retrieve the post whose ID is 10

$post=Post::model()->findByPk(10);

// retrieve the post's author: a relational query will be performed here

$author=$post->author;

Now you can manipulate the statements and variables to pull what you need from specific models and then make a custom query to join them all. You are allowed to hard code SQL query into a controller if you need to, there’s nothing wrong with that. In theory, you could take what you already did and apply it. It may not run as efficiently though as if you were to run it through the framework’s SQL setup.

Further down in the document you’ll find:

Here you can see how you can set parameters to do what you’re looking for. You’ll definitely be looking to use the joinType, with a value of Left join.

Also in the comments, a fellow poster shows some nice examples:

Hope that helps :)

I read whole DB (AR) chapter. But still cannot achieve my needs. Once again: I need to get particular PAGE with coresponding PAGE_i18n record in particular LANGUAGE. I created another picture which illustrates data in above DB schema:

So basicaly, I have 2 LANGUAGEs, 2 PAGEs. Each PAGE has a i18n record for every LANGUAGE.

When I do regular Page::model()->find($condition), AR engine returns PAGE with two i18ns (all languages). Also LANGUAGE is not loaded (I want to save SQL querys - load LANGUAGE in . I need construct such code, which fetch only needed i18n record in required LANGUAGE.

My basic problems are:

  • joining LANGUAGE to i18n (but still in Page::model() )

  • reference languages.code in $condition

I tryed construct CDbCriteria like this




$c = new CDbCriteria;

$c->addCondition('id=1');

$c->with = array('pages_i18ns', 'pages_i18ns.language');

$c->addCondition('languages.code="en"');

$c->order = 'id ASC';

$c->limit = 1;



but I get error message like this


CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'languages.code' in 'where clause'

Again, I will very appreciate any idea.

THX.

I think tha I need to explain why I need this. In Controller I’m displaying page in required lang, with required URL. If AR engine returns page with all i18ns, I will need to iterate trought all i18ns to look for required language. This will implict lots of “lazy loads” (n languages - n querys) and poor performance. If I will be able fetch i18n directly in required language, no “underneath sql queries” will ocur.

OK, I find solution with lots of experimenting. The final CDbCriteria should look like this:




$c = new CDbCriteria;

$c->addCondition('id=1');

$c->order = 't.id ASC';

$c->with = array('pages_i18ns', 'pages_i18ns.language');

$c->addCondition('language.code="en"');



In last addCondition there must be a singular notation of referenced table. Limit is not required, cos find() returns only one record.

It is working very well, by preloading all referenced tables I saved 2 SQL queries, when accesing $page->pages_i18ns[0]->language->code.