Relations using connections table, but no foreign key

Yii 1.1 General Discussion
1

Hello yii forum members!

i have a little problem with some database i created and i need a little help.

i have 3 tables: Users, Fruits and Transactions every user has many fruits and many transactions, every fruit have many transactions but every transaction just one fruit. this is the database model:

i have this relations:

in User activeRecord i have:


'fruits' => array(self::HAS_MANY, 'Fruit', 'seller'),

in Fruit active record i have:


'transactions' => array(self::HAS_MANY, 'Transaction', 'fruit'),

but i need a relation from user to transactions so i will get all the user transactions by some fruit,

any help is welcome, thanks in advance!

2

In User:


'transactions' => array(self::HAS_MANY, 'Transaction', array('id' => 'fruitID'), 'through' => 'fruits');

In Fruit:


'users' => array(self::HAS_MANY, 'Transaction', array('id' => 'userID'), 'through' => 'transactions');

In Transaction:


'fruits' => array(self::HAS_ONE, 'Fruit', array('id' => 'transactionID'));

'users' => array(self::HAS_MANY, 'Users', array('id' => 'userID'));

This way you can display which fruit got ordered by which owner it in grid form:


<?php

  widget('zii.widgets.grid.CGridView', array(

    //It is important to note, that if the Table/Model Primary Key is not "id" you have to

    //define the CArrayDataProvider's "keyField" with the Primary Key label of that Table/Model.

    'dataProvider' => new CArrayDataProvider($fruitModel->transactions, array('keyField'=>'ID')),

    'columns' => array(

    array('header'=>'Fruit', 'value'=>'$data->fruits->fruitName'),

    array('header'=>'User', 'value'=>'$data->users->userName'),   

    ),

  ));

?>

Hope that helps.

I written it up in a post that you responded to here.

I also have a more detialed version that I have written up on my blog here.

3

You are amazing!! thank you so much! this works and because i have two different AR i found cool solution because of you :slight_smile: this is my fixed code:(all the relation in the user AR)




            'fruits' => array(self::HAS_MANY, 'Fruit','seller'),

            'deals' => array(self::HAS_MANY, 'Deal', 'user'),

            'ftrans' => array(self::HAS_MANY, 'Transaction',array('id'=>'fruit'),'through'=>'fruits'),

            'dtrans' => array(self::HAS_MANY, 'Transaction',array('id'=>'deal'),'through'=>'deals'),

and in the view i have:


'dataProvider'=>new CActiveDataProvider('Transaction', array('data'=>$user->dtrans+$user->ftrans)),

thank you again!

4

Glad I could help.