Pivot MySQL rows to columns in view

Question

I have the following tables: days, periods, prices, brands, vehicles.

The Price model belongsTo Day and Period.

DB Schema:

CREATE TABLE `days` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `low` int(11) NOT NULL DEFAULT '0',
  `high` int(11) DEFAULT '0',
  `brand_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);
CREATE TABLE `periods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` date NOT NULL DEFAULT '0000-00-00',
  `finish` date NOT NULL DEFAULT '0000-00-00',
  `brand_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);
CREATE TABLE `brands` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand` varchar(255) NOT NULL DEFAULT '',
  `slug` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);
CREATE TABLE `vehicles` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `size_id` int(11) DEFAULT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `slug` varchar(50) NOT NULL,
  `model` varchar(50) DEFAULT NULL,
  `image` varchar(50) DEFAULT NULL,
  `short_specs` text,
  `specs` text,
  `publish` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`)
);
CREATE TABLE `prices` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `vehicle_id` int(3) NOT NULL DEFAULT '0',
  `day_id` int(3) NOT NULL DEFAULT '0',
  `period_id` int(3) NOT NULL DEFAULT '0',
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
);

I'm using virtualFields for the Day and Period models:

// day.php
var $virtualFields = array(
    'name' => "CONCAT(Day.low,IF(Day.high IS NULL, '+', ' to '),IF(Day.high IS NULL, '', Day.high))"
);

// period.php  dates are localized using setLocale and strftime in afterFind()
var $virtualFields = array(
    'name' => "CONCAT(Period.start,'|', Period.finish)"
);
function afterFind($results) {
    foreach ($results as $key => $val) {
        if (isset($val['Period']['name'])) {
            $dates = explode('|',$val['Period']['name']);
            $results[$key]['Period']['name'] = strftime('%d %b %Y',strtotime($dates[0])). ' – ' . strftime('%d %b %Y',strtotime($dates[1]));
        }
    }
    return $results;
}

In the Price model if I do:

function price($id) {
    $this->set('prices', $this->find('all',array('conditions' => ('Price.vehicle_id' => $id))));
}

This is shown in the view

Day            Period                      Price
5 - 20          01 May 2011 to 31 Aug 2011  $87.00
21 to 27        01 May 2011 to 31 Aug 2011  $66.00
28+             01 May 2011 to 31 Aug 2011  $63.00
5 - 20          01 Sep 2011 to 30 Sep 2011  $177.00
21 to 27        01 Sep 2011 to 30 Sep 2011  $165.00
28+             01 Sep 2011 to 30 Sep 2011  $155.00
5 - 20          01 Oct 2011 to 31 Oct 2011  $322.00
21 to 27        01 Oct 2011 to 31 Oct 2011  $310.00
28+             01 Oct 2011 to 31 Oct 2011  $300.00

How can I present the data like this?

                           5 - 20 days     21 - 27 days    28+ days
01 May 2011 to 31 Aug 2011  $87.00      $66.00      $63.00
01 Sep 2011 to 30 Sep 2011  $177.00         $165.00         $155.00
01 Oct 2011 to 31 Oct 2011  $322.00         $310.00         $300.00

I have been trying to learn about pivot tables but am struggling to do this in CakePHP. Any suggestions would be much appreciated.

score 0 · Answer 1 · edited May 23 '17 at 11:56

Managed to figure it out. This was helpful: How to pivot a MySQL entity-attribute-value schema

$prices = $this->Price->find('all',array(
    'conditions'=>array(
        'Price.vehicle_id'=>$id
    ),
    'fields'=>( //GROUP_CONCAT days and prices and separate with colon
        "GROUP_CONCAT( 
            CONCAT_WS(':',
                CONCAT(`Day`.`low`,IF(`Day`.`high` IS NULL, '+', ' to '),IF(`Day`.`high` IS NULL, '', `Day`.`high`)),
                `Price`.`price`
            )
            ORDER BY `Day`.`low`
        ) AS prices, 
        (CONCAT(`Period`.`start`,'|', `Period`.`finish`)) AS `period_name`"
    ),
    'group' => 'Period.id',
    'order' => 'Period.start'
    )
);

$prices looks like:

array(
    array(
        array(
            'prices' => 'days:price,days:price,days:price,days:price...',
            'period_name' => 'yyy-mm-dd|yyyy-mm-dd'
        ),
        ...

score 0 · Answer 2 · edited Jul 08 '12 at 00:34

0

Mark Story wrote a blog post titled "Creating easy reports with pivot tables", which will most likely help you. The essential piece is to create a separate field in the query for each column you want, then use an expression for the field with an IF to select only those rows that should be used in that column.

edited Jul 08 '12 at 00:34

outis

75,655
22
151
221

answered May 17 '11 at 13:48

Chuck Burgess

11,600
5
41
74

Pivot MySQL rows to columns in view

2 Answers2