applyLimit of Pagination acts on joins of MANY_MANY

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?

When you need a relation for database join condition you have to explicitly set together=true to force eager loading, as you did.

Can’t see a bug here.