STAT on relation where foreign key is not related to primary key

Yii 1.1 General Discussion
1

Hi All,

I have to rebuild an application to yii where the old database should not be modified. The tables are




Purchase

--------

id int PK

code varchar(15) not null unique 

date date not null

...




PurchaseDetail

--------------

id int PK

code varchar(15) not null FK to Purchase(code)

product

quantity

price 

unitPrice

While I successfuly define the details relationship in Purchase to get its details, I got a problem with the totalCharges…




public function relations() {

        // NOTE: you may need to adjust the relation name and the related

        // class name for the relations automatically generated below.

        return array(

            'details' => array(self::HAS_MANY, 'PurchaseDetail', array('code' => 'code')),  // THIS IS OKAY

            'totalCharges' => array(self::STAT, 'PurchaseDetail', 'code', 'select' => 'SUM(quantity * unitPrice'), // PROBLEM HERE!!! ALWAYS RETURN 0

        );

    }

What should I define the totalCharges?

Cheers,

Daniel

2

Check this link for more information and please also check again a relations you have defined for the table.Also try by executing sql statement and check its result.

3

I have no problem with the HAS_MANY query, but on STAT, it gave me a lot of headache…

Somehow, I cannot define foreign key in array and I cannot set it empty

either




'totalCharges' => array(self::STAT, 'PurchaseDetail', array('purchaseCode' => 'purchaseCode'), 'join' => 'INNER JOIN purchase ON purchase.code = t.code', 'select' => 'SUM(quantity * unitPrice)'),

or




'totalCharges' => array(self::STAT, 'PurchaseDetail', array('purchaseCode' => 'purchaseCode'), 'join' => 'INNER JOIN purchase ON purchase.code = t.code', 'select' => 'SUM(quantity * unitPrice)'),

will give me errors. THe first one will complain "The columns in the key must match the primary keys of the table "purchase"." And the second one will complain for "preg_match() expects parameter 2 to be string, array given"

I am using yii 1.1.10. Any help on this?

4

Probably this will work:




public function relations() {

        return array(

            'details' => array(self::HAS_MANY, 'PurchaseDetail', array('code' => 'code')),  // THIS IS OKAY

-            'totalCharges' => array(self::STAT, 'PurchaseDetail', 'code', 'select' => 'SUM(quantity * unitPrice'), // PROBLEM HERE!!! ALWAYS RETURN 0

+            'totalCharges' => array(self::STAT, 'PurchaseDetail', array('code' => 'code'), 'select' => 'SUM(quantity * unitPrice)',

        );

    }

And I hope you might find this wiki interesting:

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview

[UPDATE]

I was wrong. :(

5

Hi softark,

Thanks for the reply, I did wrong copy and paste, it should say ‘totalCharges’ not ‘numOfDetails’. Still, I got error with the STAT. It seemed that STAT and HAS_MANY has different implementation on foreign key. Any idea?

6

I’m sorry, you have been right.

I’ve also confirmed that “array(FK => PK)” doesn’t work for STAT relation. :(