Yii Framework Forum: ActiveRecord - select trought 3 tables - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

ActiveRecord - select trought 3 tables Rate Topic: -----

#1 User is offline   srigi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 14-April 10

Posted 14 April 2010 - 03:29 AM

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:

Posted Image

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:

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'


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.
0

#2 User is offline   whoopass 

  • Advanced Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 119
  • Joined: 27-May 09
  • Location:Ajax, Ontario, Canada

Posted 14 April 2010 - 09:11 AM

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.yiiframew...de/database.arr

A little ways down you'll see:

Quote

Performing Relational Query


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:

Quote

Relational Query Options


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:

Quote

accessing relational data by dreamynsx at 10:29am on October 21, 2009. say you have class Foo and class Bar which maps to database tables 'foo' and 'bar' respectively.

table bar has a field called publish_date.

You do a join on 'foo' and 'bar' via active record find() method.

$criteria->join = "JOIN bar on bar.foo_id = foo.id";$foo = Foo::model()->find($criteria);

Now you want to access the publish_date data but if you do

$foo->publish_date, it will say property not found.

To access it, you can setup relational rule like above and then access it like $foo->bar->publish_date.

But if you don't want to setup relational data, you can just add property publish_date to Foo class, and then access it like $foo->publish_date. It will reference the publish_date as result of the relational data.

Came in handy.




Hope that helps :)
0

#3 User is offline   srigi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 14-April 10

Posted 15 April 2010 - 06:07 AM

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:

Posted Image

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.
0

#4 User is offline   srigi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 14-April 10

Posted 16 April 2010 - 05:26 AM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users