Dynamic Model For Pivot Query

Yii 1.1 General Discussion
1

Hi,

I’m kind of starting with yii and I really like it! While making one of my first apps I keep hitting this problem:

I have a generic database structure for lets say property information. Like this:


CREATE TABLE properties (

  id INT(11) NOT NULL,

  item_id INT(11) DEFAULT NULL,

  prop VARCHAR(255) DEFAULT NULL,

  value VARCHAR(255) DEFAULT NULL,

  PRIMARY KEY (id)

);

INSERT INTO properties VALUES 

  (1, 1, 'color', 'blue'),

  (2, 1, 'size', 'large'),

  (3, 2, 'color', 'orange'),

  (4, 3, 'size', 'small'),

  (5, 4, 'color', 'violet'),

  (6, 5, 'color', 'green');

I make use of an extension that requires the table to be pivoted as a CActiveRecord model:




SELECT

  item_id,

  MAX(IF(prop = 'color', value, NULL)) AS color,

  MAX(IF(prop = 'size', value, NULL)) AS size

FROM

  properties

GROUP BY

  item_id;

+---------+--------+-------+

| item_id | color  | size  |

+---------+--------+-------+

|       1 | blue   | large |

|       2 | orange | NULL  |

|       3 | NULL   | small |

|       4 | violet | NULL  |

|       5 | green  | NULL  |

+---------+--------+-------+

The input of the extension needs to be in the format of the above printed table as CActiveRecord.

So my question is: is it possible to make a generic model out of the stated query as input for my extension?

In reality my problem is a bit more complicated as the query I want to use makes use of 4 joined tables to gather the column names and associating data.

Thanks in advance!

Marijn

2

Hi Marijn, welcome to the forum.

Properties of "color" and "size" could be defined as HAS_ONE relations of "Item".




// Item.php

public function relations()

{

    return array(

        'color' => array(self::HAS_ONE, 'Property', 'item_id', 'condition' => 'color.prop = "color"'),

        'size' => array(self::HAS_ONE, 'Property', 'item_id', 'condition' => 'size.prop = "size"'),

    );

}

Then you can write like this:




$items = Item::model()->with(array('color', 'size'))->findAll();

foreach($items as $item)

{

    echo "id = " . $item->id . "\n";

    echo "color = " . ($item->color !== null ? $item->color : "none") . "\n";

    echo "size = " . ($item->size !== null ? $item->sizer : "none") . "\n";

}

Well, I’m in the assumption that you are not much accustomed to Relational AR.

I have to say sorry if this has not been a kind of answer that you are looking for. :)

[EDIT]

And the 2nd approach would be creating a database view and making it the basis of an Active Record.

This may fit better to your existing extension.