Yii Framework Forum: with() not working on findAll() - Yii Framework Forum

Jump to content

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

with() not working on findAll() Rate Topic: -----

#1 User is offline   juhe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 18-October 09

Posted 16 February 2010 - 03:46 PM

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

0

#2 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 16 February 2010 - 05:46 PM

View Postjuhe, on 16 February 2010 - 03:46 PM, said:

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



look this http://www.yiiframew...-using-a-scope/

you must deal with the latest version of the repository
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#3 User is offline   juhe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 18-October 09

Posted 16 February 2010 - 08:08 PM

So if I upgrade to svn trunk it will work?
0

#4 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 17 February 2010 - 06:03 AM

View Postjuhe, on 16 February 2010 - 08:08 PM, said:

So if I upgrade to svn trunk it will work?

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');
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#5 User is offline   juhe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 18-October 09

Posted 17 February 2010 - 06:56 AM

I have tried that, same resolve.
0

#6 User is offline   juhe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 18-October 09

Posted 17 February 2010 - 07:26 AM

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}

0

#7 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 17 February 2010 - 07:51 AM

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

This post has been edited by Horacio Segura: 17 February 2010 - 07:52 AM

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#8 User is offline   juhe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 18-October 09

Posted 17 February 2010 - 07:55 AM

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

0

#9 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 17 February 2010 - 08:10 AM

sorry, I was wrong

This post has been edited by Horacio Segura: 17 February 2010 - 08:19 AM

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#10 User is online   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 17 February 2010 - 08:22 AM

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

(not tested)
/Tommy
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#11 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 17 February 2010 - 08:26 AM

for testing only, clean up your assets directory and try again
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#12 User is offline   juhe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 18-October 09

Posted 17 February 2010 - 08:53 AM

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

#13 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 17 February 2010 - 09:06 AM

View Postjuhe, on 17 February 2010 - 08:53 AM, said:

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


create a ticket for this bug

Quote

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.


look
http://www.yiiframew...ng-column-names

http://www.yiiframew...n-where-clause/
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
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