Get Sum() From Join Tables

Yii 1.1 General Discussion
1

Hi All

I need to get list of record from suppliers table with its balance. Balance is sum of its ‘bill_gross_amount’ minus sum of its transaction_amount. I try to create CDbCriteria. I can get this two sum separate, but can’t difference. In SQL it need to look like this:




SELECT s.*, SUM(sb.supplier_bill_gross_amount) - SUM(st.supplier_transaction_amount) AS balacne ...

My criteria look like this:




$criteria=new CDbCriteria;

$criteria->alias = 's';

$criteria->with = array(

	'supplierBills'=>array(

		'alias'=>'sb',

		'select'=>'SUM(supplier_bill_gross_amount) AS supplier_bill_gross_amount',

		'group'=>'sb.supplier_id'

	),

	'supplierTransactions'=>array(

		'alias'=>'st',

		'select'=>'SUM(supplier_transaction_amount) AS supplier_transaction_amount',

		'group'=>'st.supplier_id'

	)

);

$criteria->order = $sidx .' '. $sord;

$criteria->limit = $limit;

$criteria->offset = $start;


$records = $Suppliers->findAll($criteria);

For feature I want to add this on an scope(ex. balace).

Thanks for any help.

2 



$criteria->select='s.*, SUM(sb.supplier_bill_gross_amount) - SUM(st.supplier_transaction_amount) AS balacne';
3

This can’t be done, becouse Yii make 3 SQL queries. First to Suppliers table, then to SupplierBills and then to SupplierTransactions. Realtions betwen Suppliers and SupplierBills/SupplierTransactions are HAS_MANY.

4

you can define joins explicitly in $criteria->join