Yii Framework Forum: Get Sum() From Join Tables - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Get Sum() From Join Tables Rate Topic: -----

#1 User is offline   vaneamihailov 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 20-February 13

Posted 20 February 2013 - 02:16 AM

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.
0

#2 User is offline   Reza m 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 148
  • Joined: 07-March 12

Posted 20 February 2013 - 04:40 AM

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

0

#3 User is offline   vaneamihailov 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 20-February 13

Posted 21 February 2013 - 06:49 AM

View PostReza 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.
0

#4 User is offline   Reza m 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 148
  • Joined: 07-March 12

Posted 21 February 2013 - 01:31 PM

View Postvaneamihailov, 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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users