[Solved] Accessing CActiveDataProvider join data

Accessing CActiveDataProvider join data

Hello

I’m in the middle of creating a invoicing system for a project and have came across a problem which I cant seem to find a solution to (3 hours search).

So I’m want to use CActiveDataProvider to preform the following query

Demo Query

SELECT

   tbl_invoice.id,

   tbl_invoice.issue_date,

   tbl_invoice.job_id, 

   SUM(tbl_invoice_item.cost) AS invoice_total


FROM `tbl_invoice`


INNER JOIN tbl_job

ON tbl_job.id = tbl_invoice.job_id


INNER JOIN tbl_invoice_item

ON tbl_invoice_item.invoice_id = tbl_invoice.id


WHERE tbl_job.profile_id = 3


GROUP BY tbl_invoice.id


ORDER BY tbl_invoice.issue_date DESC

So I’ve came up with the code below:

            $dataProvider=new CActiveDataProvider('Invoice', array(

                'criteria'=>array(

                    'select'=>'id, issue_date, job_id',

                    'with'=>array( 'job'=>array('joinType'=>'INNER JOIN'), 

                                   'invoiceItems'=>array('joinType'=>'INNER JOIN', 'select'=>'SUM(cost) AS invoice_total', 'together'=>true), 

                                 ),                               

                    

                    'condition'=>'job.profile_id=:profile AND issue_date IS NOT NULL',

                    'params'=>array(':profile'=> Yii::app()->user->profileId),

                    'group'=>'invoice_id',

                    'order'=>'issue_date DESC',

                ),

            ));

What it needs to do is is display all invoice information relating to the current user, this includes the total cost of the invoice (invoice items), the query execute with no errors. Where I’m having problems is accessing ‘invoice_total’ from the ‘invoiceItems’ join when displaying the data with CGridView.

I have tried:

$data->invoice_total

$data->invoiceItems->invoice_total

$data[invoice_total]

All of with return property is not defined. So how will I go about accessing ‘invoice_total’ with CGridView

Bump

Things to do:

1. declare invoice_total as public member of Invoice

2. turn on logging and examine generated SQL

3. show us the relationship definitions

/Tommy

EDIT: SOLVED See end of post

First thanks for the reply.

Ok I tried setting invoice_total as a public variable within the Invoice and InvoiceItems model both didn’t make any difference.

When viewing the log output I noticed that Yii is performing this query

SELECT `t`.`id` AS `t0_c0` , `t`.`issue_date` AS `t0_c2` , `t`.`job_id` AS `t0_c1` , `job`.`id` AS `t1_c0` , `job`.`status` AS `t1_c1` , `job`.`type` AS `t1_c2` , `job`.`description` AS `t1_c3` , `job`.`notes` AS `t1_c4` , `job`.`start_date` AS `t1_c5` , `job`.`finish_date` AS `t1_c6` , `job`.`device_id` AS `t1_c7` , `job`.`profile_id` AS `t1_c8` , `job`.`tech_id` AS `t1_c9` , SUM( cost ) AS invoice_total, `items`.`id` AS `t2_c0`

FROM `tbl_Invoice` `t`

INNER JOIN `tbl_job` `job` ON ( `t`.`job_id` = `job`.`id` )

INNER JOIN `tbl_Invoice_item` `items` ON ( `items`.`invoice_id` = `t`.`id` )

WHERE (

job.profile_id = :profile

AND issue_date IS NOT NULL

)

This query returned successful when ran within MuSQL (replaced :profile with 3), below is the output

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

| t0_c0    | t0_c2      | t0_c1    | t1_c0    | t1_c1 | t1_c2 | t1_c3 | t1_c4   | t1_c5      | t1_c6 | t1_c7 | t1_c8 | t1_c9 | invoice_total | t2_c0 |

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

| 00000001 | 1299499098 | 00000001 | 00000001 | 1     | 1     | test  | ttttest | 1297433708 |  NULL |     2 |     3 |     1 |         24.00 |     1 |

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

1 row in set (0.00 sec)

Below are the relationships from the Invoice model

	public function relations()

	{

		return array(

			'job' => array(self::BELONGS_TO, 'Job', 'job_id'),

			'invoiceItems' => array(self::HAS_MANY, 'InvoiceItem', 'invoice_id'),

			'payments' => array(self::HAS_MANY, 'Payment', 'invoice_id'),

		);

	}

EDIT: Solved

After using var_dump() to view the $data I work my way up to $data->invoiceItems, from there I seen that the variable invoice_total with the invoice items model was viewable and had the current value, after a bit more debugging with var_dump() I came up with this to display the data within CGridView

[...]

        array( 

            'header' => 'Total',   // Column heading 

            'type' => 'raw',

            'value' => '$data->invoiceItems[0][invoice_total]',     // Data to display       

        ),    // Invoice Total. 

[...]

Seems a half-assed way to accessing it but, it works!, thanks again Tommy