Yii Framework Forum: applyLimit of Pagination acts on joins of MANY_MANY - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

applyLimit of Pagination acts on joins of MANY_MANY Rate Topic: -----

#1 User is offline   Maxim Popelnitskiy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 13-August 12

Posted 13 August 2012 - 05:24 AM

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?
0

#2 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 532
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 16 August 2012 - 05:51 AM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users