Eager Loading

Yii 1.1 General Discussion
1

I have the following table structure:




 CREATE TABLE `Users` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `creator_id` int(11) NOT NULL,

  `login` varchar(64) NOT NULL,

  `password` varchar(64) NOT NULL,

  `name` varchar(64) NOT NULL,

  PRIMARY KEY (`id`)


  CREATE TABLE `MoneyStorage` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `creator_id` int(11) NOT NULL,

  `name` varchar(255) DEFAULT NULL,

  `owner_id` int(11) NOT NULL,

  `owner_pers_id` int(11) NOT NULL,

  `owner_comp_id` int(11) NOT NULL,

  `comment` text NOT NULL,

  PRIMARY KEY (`id`),




 CREATE TABLE `MoneyOperations` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `creator_id` int(11) NOT NULL,

  `bill_number` int(11) NOT NULL,

  `bill_date` date NOT NULL,

  `in_bill_id` int(11) NOT NULL,

  `out_bill_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),


  CREATE TABLE `Company` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `creator_id` int(11) NOT NULL,

  `name` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),

   CREATE TABLE `Persons` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `creator_id` int(11) NOT NULL,

  `fio` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),


Company.php

public function relations()

        {

                return array(

                    'creator' => array(self::BELONGS_TO, 'Users',

'creator_id'),

                );

        }


MoneyStorage.php

public function relations()

        {

                return array(

                    'creator' => array(self::BELONGS_TO, 'Users',

'creator_id'),

                    'person'=>array(self::HAS_ONE, 'Persons',

array('id' =>'owner_pers_id'), 'on'=>'owner_type=0','joinType'=>'left

JOIN',


                        ),

                    'company'=>array(self::HAS_ONE, 'Company',

                       array('id' =>'owner_comp_id'),

'on'=>'owner_type in (1, 2)','joinType'=>'left JOIN',),

                );

        }


MoneyOperations.php

public function relations()

        {

                return array(

                    'creator' => array(self::BELONGS_TO, 'Users', 'creator_id'),

                    'in_bill' => array(self::BELONGS_TO,

'MoneyStorage', 'in_bill_id',),

                    'out_bill' => array(self::BELONGS_TO,

'MoneyStorage', 'out_bill_id'),

                );

        }

I make a request for eager load of MoneyStorage


$relation = array('person', 'company', 'creator');

$criteria = new CDbCriteria();

$criteria->with = $relation;

$criteria->together = true;

Than make a request for eager load of MoneyStorage


$relation = array('creator',

            'in_bill'=>array('with'=>array('company'=>array('alias'=>'comp1')),

'together'=>true, 'alias'=>'ms1'),

            'out_bill'=>array('with'=>array('company'=>array('alias'=>'comp2')),

'together'=>true, 'alias'=>'ms2'),

                );

And get Integrity constraint violation: 1052 Column ‘owner_type’ in

on clause is ambiguous

Clearly, i need to do alias tables, but I do not understand how to transfer it in relation.

2

I don’t see owner_type defined in any of your tables. Which tables does it belong to?

3

Sorry, owner_type in table MoneyStorage

4

You can get away with this if MoneyStorage is always the main table:




    return array(

        'creator'=>array(self::BELONGS_TO, 'Users', 'creator_id'),

        'person'=>array(self::HAS_ONE, 'Persons', array('id' =>'owner_pers_id'),

            'on'=>'t.owner_type=0',

            'joinType'=>'left JOIN',

        ),

        'company'=>array(self::HAS_ONE, 'Company', array('id' =>'owner_comp_id'),

            'on'=>'t.owner_type in (1, 2)',

            'joinType'=>'left JOIN',

        ),

    );
5

No, sometimes MoneyStorage is a main, sometimes is MoneyOperations.

I think I need to use scope but I do not know how.

6

Try this:




public function relations()

{

    $alias = $this->getTableAlias();


    return array(

        'creator'=>array(self::BELONGS_TO, 'Users', 'creator_id'),

        'person'=>array(self::HAS_ONE, 'Persons', array('id' =>'owner_pers_id'),

            'on'=>"{$alias}.owner_type=0",

            'joinType'=>'left JOIN',

        ),

        'company'=>array(self::HAS_ONE, 'Company', array('id' =>'owner_comp_id'),

            'on'=>"{$alias}.owner_type in (1, 2)",

            'joinType'=>'left JOIN',

        ),

    );

}