Table C relates to B and B relates to A. Use A and C in Listview

I have 3 tables, Table C relates to Table B and Table B relates to Table A. I was trying to use CListview to display data in Table C and Table A. (The account name is in Table A, so I wanted to display that versus the Account ID). I couldn’t figure it out, so I ended writing a simple dao query in the Table C model. From there I used CArrayDataProvider to get the data to CListview. All is working, but I’m wondering if there is a better method to do this? I’ve included my table structures and query below.




SELECT

   illinois_data.id,

   illinois_data.name AS name,

   illinois_data.quote,

   account.name AS account

FROM

   illinois_data, account

WHERE

   illinois_data.account_id=account.id






CREATE TABLE IF NOT EXISTS `account` (

  `id` int(4) NOT NULL AUTO_INCREMENT,

  `company_code` varchar(4) NOT NULL,

  `name` varchar(200) NOT NULL,

  PRIMARY KEY (`vip_code`),

  UNIQUE KEY `id` (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2167 ;






CREATE TABLE IF NOT EXISTS `account_illinois` (

  `account_id` int(11) NOT NULL,

  PRIMARY KEY (`account_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `account_illinois`

  ADD CONSTRAINT `account_illinois_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`);






CREATE TABLE IF NOT EXISTS `illinois_data` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `account_id` int(11) NOT NULL,

  `name` varchar(100) NOT NULL,

  `quote` text NOT NULL,

  PRIMARY KEY (`id`),

  KEY `account_id` (`account_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


ALTER TABLE `illinois_data`

  ADD CONSTRAINT `illinois_data_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account_illinois` (`account_id`);



Not sure i get your problem because you only use 2 tables in your query but talk about 3.

But maybe there’s help on the way: Wait some hours until 1.1.7 comes out today and then check out the new through feature for ActiveRecord relations…

I actually only need data from two tables. Can I ask what my Yii model relations should look like for the tables that I posted so that when I load illinois_data it pulls in the related data from account? illinois_data still needs to belong to account_illinois though.

I’ve tried several things such as making illinois_data belong to account and account_illinois. Making illinois_data belong to account_illinois and having one account. I just cannot figure it out.

Thanks in advance.

IllinoisData.php




'illinois_account'=>array(self::BELONGS_TO, 'IllinoisAccount', 'account_id'),



IllinoisAccount.php




'account'=>array(self::BELONGS_TO, 'Account', 'account_id'),



Eager loading (all data)




$models = IllinoisData::model()->with('illinois_acount', 'illinois_acount.account')->findAll();



Create a CDbCriteria containing the with part, add a suitable condition and pass to a CDataProvider. You can find a lot of examples in the forum. Also read the section on relational AR in The Definitive Guide to Yii.

Access (listview was mentioned so assuming local variable $data)




$data->name;

...

$data->illinois_account->account->name;



The new through support is only applicable to HAS_ONE/HAS_MANY relationships so in that case at least you have to use Account as primary (instead of IllinoisData).

(not tested)

/Tommy

Thank You!