There appears to be a bad bug in CActiveDataProvider :
I have a Category table (for an online shop), which has a relation to a CategoryCategory table (since a sub-cat can appear in more than one category - think accessories)
relation:
‘cats’ => array(self::HAS_MANY, ‘CategoryCategory’, ‘childId’),
When I specify
public function getChildren()
{
return new CActiveDataProvider('Category', array(
'criteria'=>array(
'with'=>array('cats'),
'condition'=>'id IN ('.$tmp.') AND status<>0', // $tmp is a list of cat ID's (4,2,3)
// ‘order’=>‘cats.rank’, // doesn’t work
),
'pagination'=>array(
'pageSize'=>20,
),
));
}
it seems that two SQL queries are generated (why?) - from the weblog these are :
Querying SQL: SELECT t.id AS t0_c0, t.title AS t0_c1,
t.urlTitle AS t0_c2, t.description AS t0_c3, t.image AS
t0_c4, t.status AS t0_c5, t.createdAt AS t0_c6,
t.modifiedAt AS t0_c7, t.authorId AS t0_c8, t.template AS
t0_c9 FROM Category t WHERE (id IN (4,2,3) AND status<>0) LIMIT 20which ignores the ‘cats’ relation, and
Querying SQL: SELECT t.id AS t0_c0, cats.parentId AS t1_c0,
cats.childId AS t1_c1, cats.rank AS t1_c2 FROM Category t
LEFT OUTER JOIN CategoryCategory cats ON (cats.childId=t.id)
WHERE (t.id IN (2, 3, 4))
which doesn’t.
However, BOTH are wrong - the first SHOULD refer to ‘cats’ (otherwise, how can I order the results on the ‘rank’ field of the ‘cats’ table.
The second shouldn’t be there, if the first is correct, and why does it only select the id from the main table ?
Kind regards,
Synthetic