with() not working on findAll()

Yii 1.1 General Discussion
1

I have many to many relation categories for Product model.

While using CDbCriteria categories is not included in the query as join for findAll(), but works for count().

Am I missing something?




$sort->applyOrder($criteria);

$criteria->condition = 'categories.id = '.$category->id;

$criteria->with = array('categories');

$pager = new Pagination(Product::model()->count($criteria)); // categories join included, works

$pager->pageSize = 12;

$pager->applyLimit($criteria);

Product::model()->findAll($criteria); // categories join not included, unknow field in where clause categories.id
2

look this http://www.yiiframework.com/forum/index.php?/topic/7062-with-is-not-applied-when-using-a-scope/

you must deal with the latest version of the repository

3

So if I upgrade to svn trunk it will work?

4

I suppose so, but you are working with an unstable version

is much code that you have to change?

if not,try




$sort->applyOrder($criteria);

$criteria->condition = 'categories.id = '.$category->id;

//$criteria->with = array('categories');

$pager = new Pagination(Product::model()->with('categories')->count($criteria)); 

$pager->pageSize = 12;

$pager->applyLimit($criteria);

Product::model()->with('categories')->findAll($criteria);

I think I saw a post with something like,

and you must wait for the next release for use

$criteria->with = array(‘categories’);

5

I have tried that, same resolve.

6

Tried with latest trunk, still the same:




CDbException


Description


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


Source File


/var/www/citystore/framework/db/CDbCommand.php(375)


00363:             }

00364: 

00365:             if($this->_connection->enableProfiling)

00366:                 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');

00367: 

00368:             return $result;

00369:         }

00370:         catch(Exception $e)

00371:         {

00372:             if($this->_connection->enableProfiling)

00373:                 Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');

00374:             Yii::log('Error in querying SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');

00375:             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',

00376:                 array('{error}'=>$e->getMessage())));

00377:         }

00378:     }

00379: }

Stack Trace


#0 /var/www/citystore/framework/db/CDbCommand.php(265): CDbCommand->queryInternal('fetchAll', 2, Array)

#1 /var/www/citystore/framework/db/ar/CActiveFinder.php(711): CDbCommand->queryAll()

#2 /var/www/citystore/framework/db/ar/CActiveFinder.php(403): CJoinElement->runQuery(Object(CJoinQuery))

#3 /var/www/citystore/framework/db/ar/CActiveFinder.php(80): CJoinElement->find(Object(CDbCriteria))

#4 /var/www/citystore/framework/db/ar/CActiveRecord.php(1151): CActiveFinder->query(Object(CDbCriteria), true)

#5 /var/www/citystore/framework/db/ar/CActiveRecord.php(1200): CActiveRecord->query(Object(CDbCriteria), true)

#6 /var/www/citystore/www/protected/controllers/BrowseController.php(21): CActiveRecord->findAll(Object(CDbCriteria))

#7 /var/www/citystore/framework/web/actions/CInlineAction.php(32): BrowseController->actionIndex()

#8 /var/www/citystore/framework/web/CController.php(300): CInlineAction->run()

#9 /var/www/citystore/framework/web/CController.php(278): CController->runAction(Object(CInlineAction))

#10 /var/www/citystore/framework/web/CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)

#11 /var/www/citystore/framework/web/CWebApplication.php(320): CController->run('index')

#12 /var/www/citystore/framework/web/CWebApplication.php(120): CWebApplication->runController('browse/index/id...')

#13 /var/www/citystore/framework/base/CApplication.php(135): CWebApplication->processRequest()

#14 /var/www/citystore/www/index.php(27): CApplication->run()

#15 {main}
7

sorry for the question

the name of relation in "Product" for categories is "categories"?

the default alias is the name of relation

[edit]

look the sql command in runtime/application.log

8

Relation is defined as follows:




    public function relations() {

        return array(

                ...

                'categories' => array(self::MANY_MANY, 'Category', 'products_categories(product_id,category_id)'),

                ...

        );

    }

debug query in application.log:




2010/02/17 14:58:41 [profile] [system.db.CDbCommand.query] begin:system.db.CDbCommand.query(SELECT COUNT(DISTINCT `t`.`id`) FROM `products` `t`  LEFT OUTER JOIN `brands` `brand` ON (`t`.`brand_id`=`brand`.`id`) LEFT OUTER JOIN `products_categories` `categories_categories` ON (`t`.`id`=`categories_categories`.`product_id`) LEFT OUTER JOIN `categories` `categories` ON (`categories`.`id`=`categories_categories`.`category_id`) WHERE (categories.id = 87))

2010/02/17 14:58:41 [profile] [system.db.CDbCommand.query] end:system.db.CDbCommand.query(SELECT COUNT(DISTINCT `t`.`id`) FROM `products` `t`  LEFT OUTER JOIN `brands` `brand` ON (`t`.`brand_id`=`brand`.`id`) LEFT OUTER JOIN `products_categories` `categories_categories` ON (`t`.`id`=`categories_categories`.`product_id`) LEFT OUTER JOIN `categories` `categories` ON (`categories`.`id`=`categories_categories`.`category_id`) WHERE (categories.id = 87))

2010/02/17 14:58:41 [profile] [system.db.CDbCommand.query] begin:system.db.CDbCommand.query(SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `t`.`ingredients` AS `t0_c2`, `t`.`brand_id` AS `t0_c3`, `t`.`country_id` AS `t0_c4`, `t`.`manufacturer_id` AS `t0_c5`, `t`.`unit` AS `t0_c6`, `t`.`weight` AS `t0_c7`, `t`.`varWeight` AS `t0_c8`, `t`.`info` AS `t0_c9`, `t`.`status` AS `t0_c10`, `t`.`price` AS `t0_c11`, `t`.`vat` AS `t0_c12`, `t`.`tare` AS `t0_c13`, `t`.`quantity` AS `t0_c14`, `brand`.`id` AS `t1_c0`, `brand`.`name` AS `t1_c1` FROM `products` `t`  LEFT OUTER JOIN `brands` `brand` ON (`t`.`brand_id`=`brand`.`id`) WHERE (categories.id = 87) ORDER BY brand.name ASC, products.name ASC LIMIT 12)

2010/02/17 14:58:41 [profile] [system.db.CDbCommand.query] end:system.db.CDbCommand.query(SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `t`.`ingredients` AS `t0_c2`, `t`.`brand_id` AS `t0_c3`, `t`.`country_id` AS `t0_c4`, `t`.`manufacturer_id` AS `t0_c5`, `t`.`unit` AS `t0_c6`, `t`.`weight` AS `t0_c7`, `t`.`varWeight` AS `t0_c8`, `t`.`info` AS `t0_c9`, `t`.`status` AS `t0_c10`, `t`.`price` AS `t0_c11`, `t`.`vat` AS `t0_c12`, `t`.`tare` AS `t0_c13`, `t`.`quantity` AS `t0_c14`, `brand`.`id` AS `t1_c0`, `brand`.`name` AS `t1_c1` FROM `products` `t`  LEFT OUTER JOIN `brands` `brand` ON (`t`.`brand_id`=`brand`.`id`) WHERE (categories.id = 87) ORDER BY brand.name ASC, products.name ASC LIMIT 12)

2010/02/17 14:58:41 [error] [system.db.CDbCommand] Error in querying SQL: SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `t`.`ingredients` AS `t0_c2`, `t`.`brand_id` AS `t0_c3`, `t`.`country_id` AS `t0_c4`, `t`.`manufacturer_id` AS `t0_c5`, `t`.`unit` AS `t0_c6`, `t`.`weight` AS `t0_c7`, `t`.`varWeight` AS `t0_c8`, `t`.`info` AS `t0_c9`, `t`.`status` AS `t0_c10`, `t`.`price` AS `t0_c11`, `t`.`vat` AS `t0_c12`, `t`.`tare` AS `t0_c13`, `t`.`quantity` AS `t0_c14`, `brand`.`id` AS `t1_c0`, `brand`.`name` AS `t1_c1` FROM `products` `t`  LEFT OUTER JOIN `brands` `brand` ON (`t`.`brand_id`=`brand`.`id`) WHERE (categories.id = 87) ORDER BY brand.name ASC, products.name ASC LIMIT 12

2010/02/17 14:58:41 [error] [exception.CDbException] exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories.id' in 'where clause'' in /var/www/citystore/framework/db/CDbCommand.php:375

S
9

sorry, I was wrong

10

You might as well try …->with(…)->together()->… and check what difference it makes.

(not tested)

/Tommy

11

for testing only, clean up your assets directory and try again

12

->with()->together() did the trick, but this is really a problem.

Another question, if ar model field is ambigious with relation field how to handle it?

Previously I used brand.name and products.name (Product model), but this has changed now so that model`s alias is ‘t’, so this way is not reliable.

13

create a ticket for this bug

look

http://www.yiiframework.com/doc/guide/database.arr#disambiguating-column-names

http://www.yiiframework.com/forum/index.php?/topic/7029-problem-in-relational-sql-queries-with-ambiguous-column-in-where-clause/