pagination / filter problem

Using Yii 1.1.8 with Oracle 10g2

Can’t get pagination working correctly when CDbCriteria->together = true.

When I set together = true, all the filters work, but the pagination does not.

Setting together to null, false or not setting it at all, results in a working pagination, but filtering the data causes an "invalid identifier" SQL error to occur.

I have been browsing around online for an answer to this, but have been unsuccessful. Any ideas or "right direction" pointing is appreciated. If I am leaving anything important out, please let me know and I will do my best to include it.

The code I included below is for a class called ParcelLink and has a HAS_MANY relationship with "parcel". There is only one "parcel" per ParcelLink per year, and I am filtering on the year. Also, "PARCEL_NUMBER" is the only field I am using within the search that is owned directly by ParcelLink. Each of the related fields have setters and getters within the ParcelLink model.

    public function search()

    {

        $criteria=new CDbCriteria;


        $criteria->with = array(

            'parcel',

            'parcel.city',

            'parcel.taxDistrict',

            'parcel.businessNames',

            'parcel.section',

            'parcel.subdivision',

            'parcel.section.township',

            'currentOwnerInterests.ownerExemptions',

            'currentOwnerInterests.ownerName',

        );

        $criteria->together = true;


        $criteria->compare('"t".PARCEL_NUMBER', $this->PARCEL_NUMBER, true);


        $criteria->compare('"parcel".HOUSE_NUMBER', $this->houseNumber, true);

        $criteria->compare('"parcel".PREFIX_DIRECTION', $this->streetDir, true);

        $criteria->compare('"parcel".STREET', $this->streetName, true);

        $criteria->compare('"parcel".STREET_TYPE', $this->streetType, true);

        $criteria->compare('"parcel".SUFFIX_DIRECTION', $this->streetSuffixDir,

                true);

        $criteria->compare('"parcel".UNIT', $this->streetUnit, true);

        $criteria->compare('"parcel".LEGAL_DESCRIPTION',

                $this->legalDescription, true);


        $criteria->compare('"subdivision".SUBDIVISION_CODE', $this->subdivision,

                false);


        $criteria->compare('"section".SECTION_NUMBER', $this->section,

                false);

        $criteria->compare('"township".TOWNSHIP',

                $this->township, false);

        $criteria->compare('"township".RANGE_',

                $this->range, false);


        $criteria->compare('"currentOwnerInterests"."ownerName".OWNER_NAME',

                $this->ownerName, true);

        $criteria->compare('"currentOwnerInterests"."ownerName".ADDRESS1',

                $this->ownerAddress1, true);

        $criteria->compare('"currentOwnerInterests"."ownerName".ADDRESS2',

                $this->ownerAddress2, true);

        $criteria->compare('"currentOwnerInterests"."ownerName".CITY',

                $this->ownerCity, true);

        $criteria->compare('"currentOwnerInterests"."ownerName".STATE',

                $this->ownerState, true);

        $criteria->compare('"currentOwnerInterests"."ownerName".ZIP',

                $this->ownerZip, true);

        $criteria->compare('"currentOwnerInterests"."ownerName".ZIP4',

                $this->ownerZip4, true);

        $criteria->compare('"currentOwnerInterests"."ownerName".OWNER_NUMBER',

                $this->ownerNumber, false);


        $criteria->compare('"parcel"."city".CITY_CODE', $this->cityCode, true);


        $criteria->compare('"parcel"."taxDistrict".TAX_DIST_NAME',

                $this->taxDistrictCode, false);


        $criteria->compare('"currentOwnerInterests"."ownerExemptions".EXEMPT_CODE',

                $this->homestead, true);


        $criteria->compare('"parcel"."businessNames".BUSINESS_NAME',

                $this->businessName, true);


        return new CActiveDataProvider(get_class($this), array(

			'criteria' => $criteria,

            'sort'=>array(

                'defaultOrder'=>'"t".PARCEL_NUMBER ASC',

            ),

            'pagination' => array(

                'pageSize' => 20,

            ),

		));

    }

I was going to link a screenshot of the search page, but I am not allowed to embed links in the forum at this time.

Will a plea of desperation help?

I have exactly the same problem, and it’s also driving me nuts.

I created a MANY_MANY relationship between my tables avh and tag using the intermediary table avh_tag.

public function relations()

{

    return array(

    	/* ... */

        'tags' => array(self::MANY_MANY, 'Tag', 'avh_tag(avh_id, tag_id)'),

    );

}

Working fine, no problem. Now, for the index page, I need to filter on some tags. Here a simplified version of the filter code.

$criteria = new CDbCriteria;

$criteria->with = array('tags');


$criteria->addCondition('tags.name="'.$tagKey.'"');


$criteria->together = true;


$dataProvider=new CActiveDataProvider('Avh', array(

        'criteria'=> $criteria,

    )

);

If I remove the condition and set the together param to false, the pagination is working fine. If I keep the condition and remove the together, the request fails because of unknown column name. Ok. If I use exactly this code, no error is thrown, the filter is working fine… but the pagination goes completely crazy.

For example, for a pageSize of ten, I have a random number of items by page between 1 and 10. The total number of items is correct, and so is the total number of pages… except it’s calculated at ten items by page. The order of display is the one specified, and there is no fused items or other strange data. Problem exists both in JS and non-JS pagination.

Hi, GrendelTheory and Skarn,

I had just the same problem sometimes ago.

http://www.yiiframework.com/forum/index.php/topic/21781-relational-filter-and-pagination-with-has-many-or-many-many/

And my conclusion has been … it’s a pity but … it’s impossible to (1) filter by has_many(or many_many) relation and (2) paginate correctly AT THE SAME TIME using CActiveDataProvider in an ordinary way. There should be a decent work around, I hope, but I really don’t know by now.

Please check the answer from qiang.xue in the following link.

http://code.google.com/p/yii/issues/detail?id=1298

[P.S.]

What Phillip has suggested in this thread could be a decent solution.

See the #3 post.

http://www.yiichina.net/forum/index.php/topic/17633-filter-on-has-many-relation-without-together/

Thanks for your quick answer.

For lack of anything better, I solved my problem using the extra query solution. Hope one day someone will find a best way to go around this feature.