I need to implement some type of function that allows me to duplicate rows inside CActive record. Allow me to explain:
I have a product catalog list shown in a Yii platform I’m developing. Each product allows me to specify a list of target countries to where this product can be sold, which is serialized and stored into the database. The problem now is that I need to show a new column specifying the target countries but, instead of showing an imploded array of the target countries, my employer wants me to show each country as an individual row, duplicating the remaining information.
Example:
In product sheet:
ID | Name | Target Countries | Price
10 | Oranges | Brasil, Portugal | 5,00€ / Kg
In product catalog:
ID | Name | Target Countries | Price
10 | Oranges | Brasil | 5,00€ / Kg
10 | Oranges | Portugal | 5,00€ / Kg
So, as you can see, the original row is converted in two or more rows (according to the number of countries), duplicating the remaining values. The CActiverecord is then shown through an CGridView.
It’s very unlikely that duplicating rows is the correct approach. You should try to keep your database normalised to prevent data inconsistency.
You need a table and model to represent countries, and another table to act as the link between products and countries. If all other attributes are the same, the joining table will look something like this:
CREATE TABLE `product_country` (
`product_id` int(10) unsigned NOT NULL,
`country_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`product_id`,`country_id`),
KEY `country_id` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `product_country`
ADD CONSTRAINT `product_country_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `product_country_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
You can then set up a MANY_MANY relation in the product model.
If there are other attributes that change by country, these can be moved into the product_country table.
Thanks for the thought. My idea was not to duplicate rows inside the table itself, but to create a view or to duplicate the values after fetching them from the database. Yet, your approach is also valid, I just need to figure out how to save and load the information to and from the database and into checklistbox. Any suggestions on this? (I’m still a newbie in Yii)
