Cmodel-Object With Db-Attributes

Yii 1.1 General Discussion
1

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.

2

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

3

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

4

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

5

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.

6

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;
7

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?

8

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

9

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.

10

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.

11

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

Thank you