[vaneamihailov]

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.

[Reza m]

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

[vaneamihailov]

Reza m, on 20 February 2013 - 04:40 AM, said:

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

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.

[Reza m]

vaneamihailov, on 21 February 2013 - 06:49 AM, said:

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.

you can define joins explicitly in $criteria->join