[solved] yii model relation sort by third table

Hi All,

Could someone help me on this?

I have three tables,

transaction

-----------

id int PK

transactionNo varchar(50) not null

date date not null

memo text null

status tinyint(2) not null


transaction_detail

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

id int PK

transactionFk int not null 'FK to transaction(id)

itemFk int not null 'FK to item(id)

quantity decimal(15,2) not null

unitPrice decimal(15,2) not null


item

----

id int PK

code varchar(15) not null

name varchar (40) not null

unit varchar(10) not null

description text null

status tinyint(2) not null

The question is that how could I make a relation to details order by item.name

 * @return array relational rules.

     */

    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, 'TransactionDetail', 'transactionFk'),

            'totalTransaction' => array(self::STAT, 'TransactionDetail', 'transactionFk', 'select' => 'SUM(quantity * unitPrice)'),

            'totalItems' => array(self::STAT, 'TransactionDetail', 'transactionFk', 'select' => 'SUM(quantity)'),

        );

    }

Thank you in advance.

Hi

It is basically the same as for two tables: you have to set up a ‘with’ “condition” to make sure that your third table is available in the SQL statement.

Then you can add a conditon like $criteria->compare(’<table_name_or_alias>.column’,1);

Personally, I use my extension :

http://www.yiiframew…searchbehavior/

Check the demo: http://relatedsearch…or.ynamics.com/ . Example sorting on artist: http://relatedsearch…ine_sort=Artist . This is the SQL that is created with help from the extension:

SELECT 't'."InvoiceLineId" AS "t0_c0", 't'."InvoiceId" AS "t0_c1", 't'."TrackId" AS "t0_c2", 't'."UnitPrice" AS "t0_c3", 't'."Quantity" AS "t0_c4",

 'track'."TrackId" AS "t1_c0", 'track'."Name" AS "t1_c1", 'track'."AlbumId" AS "t1_c2", 'track'."MediaTypeId" AS "t1_c3", 'track'."GenreId" AS "t1_c4", 'track'."Composer" AS "t1_c5", 'track'."Milliseconds" AS "t1_c6", 'track'."Bytes" AS "t1_c7", 'track'."UnitPrice" AS "t1_c8",

 'album'."AlbumId" AS "t2_c0", 'album'."Title" AS "t2_c1", 'album'."ArtistId" AS "t2_c2"

 FROM 'invoiceline' 't'

LEFT OUTER JOIN 'track' 'track' ON ('track'."TrackId"='t'."TrackId")

LEFT OUTER JOIN 'album' 'album' ON ('album'."AlbumId"='track'."AlbumId")

LEFT OUTER JOIN 'artist' 'artist' ON ('artist'."ArtistId"='album'."ArtistId")

GROUP BY 't'."InvoiceLineId" ORDER BY 'artist'."Name"

LIMIT 4

Check this Link

Thank you for your prompt response. I have just come back from a trip.

Actually, my current implementation is by using "with" clause in CDbCriteria.

However, being lazy, I want simple solution.

I can get the details from a Transaction by defining the relation in the Transaction model.

 * @return array relational rules.

     */

    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, 'TransactionDetail', 'transactionFk'),

            ...

        );

    }

I know that I can just add ‘order’ in the array. But, what I want to sort is according to details item name, not details item id, while details name are stored in the Item table not in the TransactionDetail table.

Below solution is not what being expected, since it will sorted according to the TransactionDetail’s itemFk not item.name.

 * @return array relational rules.

     */

    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, 'TransactionDetail', 'transactionFk', 'order' => 'itemFk ASC'),

            ...

        );

    }

Is that possible to do so? Or the only solution is to use CDbCriteria?

My current solution:

public function getSortedDetails($transactionFk) {

    $criteria = new CDbCriteria;

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

    $criteria->compare('transactionFk', $transactionFk);

    $criteria->order = 'item.name ASC';


    return TransactionDetail::model()->findAll($criteria);

}

The relation is specified with " ‘order’ => ‘itemFk ASC’" , hence it orders by itemFk. Either: change itemFk into ‘name’ or ‘details.name’, or duplicate the relation in ‘foo’ and change ‘itemFk’ in ‘foo.name’ and then use ‘foo’ in your with clause.

I do not know how " ‘with’=>‘item’ " works because your relation is ‘details’.

BTW: sometimes to be lazy you have to invest some time. with RelatedSearchBehavior, I am pretty lazy now, I did invest soem time to write the extension though but it made me gain a lot of time. By sharing it, others do not need to write it, just spend some time to learn how to use it.

Hi le_top,

Thank you. Great extension, I will find some time to learn your extension. In the beginning, I was thinking that your extension is overkill to my need. That’s why I am so lazy to learn it further.

I am still curious with "direct approach" is that possible to achieve it using "through" ?

In Transaction model, I have relation to table TransactionDetail named as "details". Then, can I use details as "through" to have relation to Item table?

Hi All,

Finally, I found the solution. I was too afraid (or too lazy). I only need to add the ‘with’ clause in the relation definition and it works.

    /**

     * @return array relational rules.

     */

    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, 'TransactionDetail', 'transactionFk', 'with' => 'item', 'order' => 'item.name ASC'),

            'totalTransaction' => array(self::STAT, 'TransactionDetail', 'transactionFk', 'select' => 'SUM(quantity * unitPrice)'),

            'totalItems' => array(self::STAT, 'TransactionDetail', 'transactionFk', 'select' => 'SUM(quantity)'),

        );

    }