Accessing CActiveDataProvider join data
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:
All of with return property is not defined. So how will I go about accessing ‘invoice_total’ with CGridView