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`);
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.
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)
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).