The main point DB tables for reproducing bug:
Users - users
UserTags - tags for users (every user able to have one or more tags, or does not have it at all)
UsersUserTag - table that stores relations between Users and UserTags tables, has unique index on user_id, user_tag_id)
Classes:
UserDelivery model relation:
public function relations()
{
return array(
'users_info' => array(self::BELONGS_TO, 'User', 'ud_user_id', 'joinType' => 'INNER JOIN'),
'users_info_with_tags' => array(self::BELONGS_TO, 'User', 'ud_user_id', 'with' => 'users_user_tags', 'joinType' => 'INNER JOIN'),
'users_info_info' => array(self::BELONGS_TO, 'UserInfo', 'ud_user_id', 'joinType' => 'INNER JOIN'),
'delivery_info' => array(self::BELONGS_TO, 'Deliveries', 'ud_delivery_id'),
);
}
User model relation:
public function relations()
{
return array(
'user_info' => array(self::HAS_ONE, 'UserInfo', 'ui_user_id'),
'deliveriesCount' => array(self::STAT, 'Deliveries', 'delivery_owner'),
'users_user_tags' => array(self::MANY_MANY, 'UserTags', 'users_user_tags(user_id, user_tag_id)'), );
}
Code that reproduce the bug:
$criteria = new CDbCriteria;
$criteria->with = array(‘users_info_with_tags’, ‘delivery_info’, ‘users_info_info’);
// Here I configure criteria to get Users by tags
$pages->applyLimit($criteria); // This line of code raise a bug, commenting this line makes code works fine
$subscribers = UserDelivery::model()->findAll($criteria); // With limit we have a bug
Error message:
SQLSTATE[42703]: Undefined column: 7 ERROR: column "tag" does not exist
LINE 1: …fo".“ui_user_id”) WHERE ((delivery_owner=3) AND (tag = 'бед…
^. The SQL statement executed was: SELECT …select fields… FROM “user_delivery” “t” INNER JOIN “users” “users_info_with_tags” ON (“t”.“ud_user_id”=“users_info_with_tags”.“user_id”) LEFT OUTER JOIN “deliveries” “delivery_info” ON (“t”.“ud_delivery_id”=“delivery_info”.“delivery_id”) INNER JOIN “user_info” “users_info_info” ON (“t”.“ud_user_id”=“users_info_info”.“ui_user_id”) WHERE ((delivery_owner=3) AND (tag = ‘бедный’)) LIMIT 10
We have this error message because there are not all joins persist in query, only 3 joins, should be 5 in my case!
And if I comment code $pages->applyLimit($criteria) then it will works fine, I will have 5 joins in query.
I have found following function in CActiveFinder.php (795)
public function buildQuery($query)
{
foreach($this->children as $child)
{
if($child->master!==null)
$child->_joined=true;
else if($child->relation instanceof CHasOneRelation || $child->relation instanceof CBelongsToRelation
|| $this->_finder->joinAll || $child->relation->together || (!$this->_finder->baseLimited && $child->relation->together===null))
{
$child->_joined=true;
$query->join($child);
$child->buildQuery($query);
}
}
}
We have acting together in condition.
I have changed the model relation, adding together => true and it start working fine.
But it really looks like a bug! Together parameter acting on perfomance issues and shouldn’t apply on limit part of query, but it does. So, I am sure we have wrong logic in this function.
Right User’s model relations looks like:
public function relations()
{
return array(
‘user_info’ => array(self::HAS_ONE, ‘UserInfo’, ‘ui_user_id’),
‘deliveriesCount’ => array(self::STAT, ‘Deliveries’, ‘delivery_owner’),
‘users_user_tags’ => array(self::MANY_MANY, ‘UserTags’, ‘users_user_tags(user_id, user_tag_id)’, ‘together’ => true ));
}
Am I right, should it be posted like a bug?