Cmodel-Object With Db-Attributes

Hi,

last time I used was about 2 year ago. Now I will start a new project and I have a problem with an main-object.

The object has to handle orders but the order-infos are in more than one table so I can’t use a CActiveRecord-object. I’m not sure how the object should be. If I load the order-data with an db-query it’s returned as an array and not handled as object-attributes.

But I have to define the attributenames which only exists as array-data…

Guess it’s to long ago since my last yii-project and my last was all with CActiveRecord-object.

Before you tell me it’s a bad db-design. We are using magento and I’m about to create a tool to proccess some orders. I don’t want to copy all order-data in a new table. This would create alot of duplicated data and will bring data-sync issues.

I hope someone can give me a good advice how to start with thins kind of objects.

You should still be able to access the data via active record if you set up the appropriate relations.

But I need to enter a classname for a relations and I wont create a class for each subtable of this object.

Can you give an example of the table structure that’s preventing you from doing this?

Ya ofc.

Magento saves the most order data in the table: sales_flat_order

I create a new table for the new tool called: ysb_order

ysb_order will have the entity_id (as foreign key) of the sales_flat_order with additional infos and only a part of the orders, not all.




CREATE TABLE `sales_flat_order` (

	`entity_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',

	`status` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Status',

	`store_id` SMALLINT(5) UNSIGNED NULL DEFAULT NULL COMMENT 'Store Id',

	`customer_id` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Customer Id',

[...]

PRIMARY KEY (`entity_id`),

)

COMMENT='Sales Flat Order'

COLLATE='utf8_general_ci'

ENGINE=InnoDB






CREATE TABLE `ysb_order` (

	`entity_id` INT(10) UNSIGNED NOT NULL,

	`order_status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '1',

[...]

	UNIQUE INDEX `entity_id` (`entity_id`),

	CONSTRAINT `FK_ysb_order_sales_flat_order` FOREIGN KEY (`entity_id`) REFERENCES `sales_flat_order` (`entity_id`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB



For the order I could add the columns in the magento sales_flat_order table but I don’t like it and when I start with the order_items I’ll run into this problem again because magento saves the infos about items in many tables (eav-structure).

e.g.

catalog_product_entity as product main table

catalog_product_entity_int for all product attributes with int values

catalog_product_entity_varchar for all product attributes with varchar values

and so on…

and all connected by an eav_attribute table with the attribute_ids

I’ts not possible to handle this with ORM. I need to join a few tables to get the data without a class for each table.

Here is a example SQL-Query to get a few informations about an sold item with could be an SalesItem-Object




SELECT

	i.item_id,

	i.product_type,

	i.name,

	i.sku,

	i.product_id,

	i.product_options,

	p_d1.value as ek,

	CASE p_i1.value

		WHEN 3 THEN 'Stock'

		WHEN 4 THEN 'DF (always)'

		WHEN 95 THEN 'DF (partial)'

		WHEN 5 THEN 'Just In Time'

	END as partnertype,

   o.value as partner

FROM sales_flat_order s

LEFT JOIN sales_flat_order_item i

ON 

	s.entity_id = i.order_id 

	AND 

	(

		(

			i.product_type = 'simple'

			AND i.parent_item_id IS NULL

		)

		OR	i.product_type = 'configurable'

	)

LEFT JOIN catalog_product_entity_int p_i1

ON 

	p_i1.entity_id = i.product_id

	AND p_i1.attribute_id = 145 # Partner_Type

	AND p_i1.store_id = 0

LEFT JOIN catalog_product_entity_int p_i2

ON 

	p_i2.entity_id = i.product_id

	AND p_i2.attribute_id = 144 # Partner

	AND p_i2.store_id = 0

LEFT JOIN catalog_product_entity_decimal p_d1

ON 

	p_d1.entity_id = i.product_id

	AND p_d1.attribute_id = 172 # EK

	AND p_d1.store_id = 0

LEFT JOIN eav_attribute_option_value o

ON o.option_id = p_i2.value

WHERE s.entity_id = 1111;



What I found untill now is:

http://www.yiiframework.com/forum/index.php/topic/9155-yii-and-mysql-eav-databases-model/

Looks like yii can’t handle eav out of the box and I have to try the extension. Guess yii2 is the same?

It seems likely. EAV isn’t commonly used, so it’s unlikely to ever be managed in the core.

Looks like the extension won’t help me. It only supports pure eav but I also need couple attributes from a single table in a non eav structure.

Do you know if I can use own SQL-querys with a CModel-Object and still be able to use it as an object like a CActiveRecord? I know that its possible to put a SQL-query in an CModel but I get an array.

I don’t know if it helps, but you could assign the results to models individually something like this:




    $queryResult = $someCommand->queryAll();

    $results = array();


    foreach ($queryResult as $row)

    {

        $model = new YourModel;

        $model->setAttributes($row, false);

        $results[] = $model;

    }



I don’t imagine that’s particularly useful to you though.

Ya maybe thats an option. I have to test the performance, if I create alot of objects by this way.

Thank you