数据库结构
post
–id
–name
tag
–id
–name
post_tag
–post_id
–tag_id
Post中的relation定义如下
‘tags’=>array(self::MANY_MANY, ‘Tag’, ‘post_tag(post_id, tag_id)’),
我现在想根据 tag的name查到对应的post，代码如下：
$criteria = new CDbCriteria();
$with = array(
'comments' => array()
);
if(isset($_GET['tag'])) {
$with['tags'] = array(
'select'=>false,
'joinType'=>'INNER JOIN',
'condition'=>'tags.name="'.$_GET['tag'].'"',
);
}
$count = Post::model()->published()->with($with)->count($criteria);
echo $count;
$pages = new CPagination($count);
$pages->pageSize = 3 * 4;
$pages->applyLimit($criteria);
$Posts = Post::model()->published()->with($with)->findAll($criteria);
问题是：
查询出来的$count的个数是正确的，但是$posts不正确。
通过查看sql发现count的时候sql是：
SELECT COUNT(DISTINCT t.id) FROM post t LEFT OUTER
JOIN comment comments ON (comments.post_id=t.id) INNER JOIN
post_tag tags_tags ON (t.id=tags_tags.post_id) INNER JOIN
tag tags ON (tags.id=tags_tags.tag_id) WHERE (t.disabled=0
and public_time<‘2012-09-04 18:25:18’) AND (tags.name=“t2”)
而下面findAll生成的sql是：
SELECT t.id AS t0_c0, t.disabled AS t0_c1,
t.user_id AS t0_c2, t.path AS t0_c3, t.title AS t0_c4,
t.description AS t0_c5, t.meta_keywords AS t0_c6,
t.meta_description AS t0_c7, t.public_time AS t0_c8,
t.viewed AS t0_c9, t.favourite AS t0_c10, t.created AS
t0_c11, t.updated AS t0_c12 FROM post t WHERE (t.disabled=0
and public_time<‘2012-09-04 18:30:25’) ORDER BY public_time desc LIMIT 12；
和
SELECT t.id AS t0_c0 FROM post t INNER JOIN
group_tag tags_tags ON (t.id=tags_tags.post_id) INNER JOIN
tag tags ON (tags.id=tags_tags.tag_id) WHERE (t.id IN (19,
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30)) AND (tags.name="t2")
继续分析之后发现，如果有了$pages->applyLimit($criteria)这句，sql就会被搞错，没有这句sql就是正确的，但是只要修改了$criteria的limit和offset，sql就会变成下面那个样子。
但是不applyLimit或者修改offset和limit就无法实现分页，求解。