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