Hi.
In my model I have something like this:
//`quote` model
public function beforeFind(){
$this->with('tags');
}
I don`t want write with() construction in every place thats why i put it there.
But now I need filter rows by some ‘tags’ column and count it(for pagination).
Example:
// some controller
// tag_id - column of tags table
$criteria = new CDbCriteria();
$criteria->order = 'rating DESC';
$criteria->addColumnCondition(array('enabled' => '1','tag_id'=>$tagId));
$quoteModel = Quote::model();
$count = $quoteModel->count($criteria);
$pages = new Pagination($count);
$pages->pageSize = $this->quotesPerPage;
$pages->applyLimit($criteria);
$quotes = $quoteModel->findAll($criteria);
Yii ignore my beforeFind() rule and I got error “Column not found: 1054 Unknown column ‘tag_id’”.
Ok. I can write ‘with’ condition in criteria:
// some controller
// tag_id - column of tags table
$criteria = new CDbCriteria();
$criteria->order = 'rating DESC';
$criteria->addColumnCondition(array('enabled' => '1','tag_id'=>$tagId));
$criteria->with = 'tags'; // NEW LINE
// code here
Now I have correct “$count”, but incorrect “$quotes = $quoteModel->findAll($criteria);”. It`s again ignore my with(‘tags’).
Example:
Correct count query with JOIN:
SELECT COUNT(DISTINCT `t`.`id`) FROM `quote` `t` LEFT OUTER JOIN `tag_quote` `tags` ON (`tags`.`quote_id`=`t`.`id`) WHERE (enabled=:ycp2 AND tag_id=:ycp3). Bound with :ycp2='1', :ycp3='4'
Incorrect quotes query without JOIN:
Querying SQL: SELECT `t`.`id` AS `t0_c0`, `t`.`content` AS `t0_c1`, `t`.`title` AS `t0_c2`, `t`.`rating` AS `t0_c3`, `t`.`add_date` AS `t0_c4`, `t`.`approve_date` AS `t0_c5`, `t`.`approver_id` AS `t0_c6`, `t`.`enabled` AS `t0_c7` FROM `quote` `t` WHERE (enabled=:ycp2 AND tag_id=:ycp3) ORDER BY rating DESC LIMIT 15. Bound with :ycp2='1', :ycp3='4'
This same method of the same controller. Varies only limit and offset. I don`t know what I’m doing wrong, can anyone tell why the event beforeFind not work as expected and why “count” and “findAll” work differently to the same conditions.
[added]
It seems like yii generates two queries in findAll:
First:
[12:44:16.324][trace][system.db.CDbCommand] Querying SQL: SELECT `t`.`id` AS `t0_c0`, `t`.`content` AS `t0_c1`, `t`.`title` AS `t0_c2`, `t`.`rating` AS `t0_c3`, `t`.`add_date` AS `t0_c4`, `t`.`approve_date` AS `t0_c5`, `t`.`approver_id` AS `t0_c6`, `t`.`enabled` AS `t0_c7` FROM `quote` `t` LIMIT 15
And second:
log: [12:44:16.326][trace][system.db.CDbCommand] Querying SQL: SELECT `t`.`id` AS `t0_c0`, `tags`.`tag_id` AS `t1_c0`, `tags`.`quote_id` AS `t1_c1` FROM `quote` `t` LEFT OUTER JOIN `tag_quote` `tags` ON (`tags`.`quote_id`=`t`.`id`) WHERE (`t`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15))
So I can`t filter results by tag_id column in AR without writing sql. Is it right?
