Yii Framework Forum: sum, count, max, min bersamaan - Yii Framework Forum

Jump to content

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

sum, count, max, min bersamaan tentang group by, agregasi, STAT Rate Topic: ***** 1 Votes

#1 User is offline   aqge 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 188
  • Joined: 02-September 10
  • Location:Indonesia

Posted 02 October 2010 - 02:56 AM

Agan-agan,
Suhu-suhu,
Guru-guru...

Bagaimana car mendapatkan

"select A.field1, A.field2, sum(B.harga), count(B.id), max(B.harga), min(B.harga)
from A
Join B.xx = A.xx "

dengan active record?

Telah saja coba dengan relation

public function relations()
	{
       return array(
                   'agregate'=>array(self::STAT,'B','A_id',
			'select'=>'sum(B.price) as Total, count(B.B_id) as jumlah,  
                                   max(B.price) as maks, min(B.price) as Min' )
                   )
    }



gak bisa

mohon diterawang...
Best Regard
Tidak ada coding yang salah
Yang ada hanyalah Coding yang lebih baik
0

#2 User is offline   nasrul 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 98
  • Joined: 14-April 09
  • Location:Sorowako - Indonesia

Posted 09 October 2010 - 02:46 PM

apa ada pesan error:

atau mungkin bisa dengan jalan mendefinisikan masing-masing agregate


public function relations()
{
	return array(
		'agregateSum'=>array(
			self::STAT,'B','A_id',
			'select'=>'sum(B.price)' 
		),
		'agregateCount'=>array(
			self::STAT,'B','A_id',
			'select'=>'count(B.B_id)' 
		),
		'agregateMax'=>array(
			self::STAT,'B','A_id',
			'select'=>'max(B.price)' 
		),
		'agregateMin'=>array(
			self::STAT,'B','A_id',
			'select'=>'min(B.price)' 
		),
   )
}



ada pun pada viewnya:
<?php
    $data = A::model()->findAll();
    foreach ($data as $dataItem)
    {
	echo $dataItem->id;
   		echo "Total = ".$dataItem->agregateSum;
   		echo "Count = ".$dataItem->agregateCount;
   		echo "Min = ".$dataItem->agregateMin;
   		echo "Max = ".$dataItem->agregateMax;
    }
?>

0

#3 User is offline   junxiong 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 543
  • Joined: 21-June 10

Posted 10 October 2010 - 12:51 AM

apa mungkin karena belum pakai "group" ??
ini ada contoh yang dipakai, di thread sebelah

'rating' => array(self::STAT, 'Rating', 'walk_id',
                        'select' =>'ROUND(AVG(rating))',
                        'group' => 'walk_id',
                ),

“The most likely way for the world to be destroyed, most experts argue, is by accident. That’s where we come in; we’re computer professionals. We cause accidents.” - Nathaniel Borenstein

Yii Playground : Collaborative demo apps. You can join to improve it too!
My Team's Blog: In Indonesian.
0

#4 User is offline   aqge 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 188
  • Joined: 02-September 10
  • Location:Indonesia

Posted 18 October 2010 - 04:17 AM

View Postnasrul, on 09 October 2010 - 02:46 PM, said:

apa ada pesan error:

atau mungkin bisa dengan jalan mendefinisikan masing-masing agregate


public function relations()
{
	return array(
		'agregateSum'=>array(
			self::STAT,'B','A_id',
			'select'=>'sum(B.price)' 
		),
		'agregateCount'=>array(
			self::STAT,'B','A_id',
			'select'=>'count(B.B_id)' 
		),
		'agregateMax'=>array(
			self::STAT,'B','A_id',
			'select'=>'max(B.price)' 
		),
		'agregateMin'=>array(
			self::STAT,'B','A_id',
			'select'=>'min(B.price)' 
		),
   )
}



ada pun pada viewnya:
<?php
    $data = A::model()->findAll();
    foreach ($data as $dataItem)
    {
	echo $dataItem->id;
   		echo "Total = ".$dataItem->agregateSum;
   		echo "Count = ".$dataItem->agregateCount;
   		echo "Min = ".$dataItem->agregateMin;
   		echo "Max = ".$dataItem->agregateMax;
    }
?>




terima kasih suhu, yang ini bisa...
dari awal saya memang tidak mencoba trik begini karena khawatir akan ada dua join sehingga selain merusak hasinya juga menyita resouresnya DB,


tapi hasilnya sih bener keluar tapi soal resourcesnya ane kurang paham nih... sebelum ada yang meng-konfirmasi trik diatas tidak boros resources, ane masih deg-degan makainya...

trim again bos nasrul,
Best Regard
Tidak ada coding yang salah
Yang ada hanyalah Coding yang lebih baik
0

#5 User is offline   nasrul 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 98
  • Joined: 14-April 09
  • Location:Sorowako - Indonesia

Posted 20 October 2010 - 12:54 AM

View Postaqge, on 18 October 2010 - 04:17 AM, said:

<br />terima kasih suhu, yang ini bisa...<br />dari awal saya memang tidak mencoba trik begini karena khawatir akan ada dua join sehingga selain merusak hasinya juga menyita resouresnya DB, <br /><br /><br />tapi hasilnya sih bener keluar tapi soal resourcesnya ane kurang paham nih... sebelum ada yang meng-konfirmasi trik diatas tidak boros resources, ane masih deg-degan makainya...<br /><br />trim again bos nasrul,<br />


iya, saya juga berfikir demikian, :P

mungkin bisa pula melalui findAllBySql() http://www.yiiframew...AllBySql-detail
<?php
    $sql="
        SELECT
                A.id, 
                sum(B.price) as Total, 
                count(B.B_id) as jumlah, 
                max(B.price) as maks, 
                min(B.price) as Min
        FROM 
                A INNER JOIN B ON A.ID=B.A_ID
        ";

    $data = A::model()->findAllBySql($sql);
    foreach ($data as $dataItem)
    {
	echo $dataItem['id'];
   	echo "Total = ".$dataItem['Total'];
   	echo "Count = ".$dataItem['jumlah'];
   	echo "Min = ".$dataItem['Min'];
   	echo "Max = ".$dataItem['maks'];
    }
?>

0

#6 User is offline   aqge 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 188
  • Joined: 02-September 10
  • Location:Indonesia

Posted 20 October 2010 - 11:12 PM

View Postnasrul, on 20 October 2010 - 12:54 AM, said:

iya, saya juga berfikir demikian, :P

mungkin bisa pula melalui findAllBySql() http://www.yiiframew...AllBySql-detail
<?php
    $sql="
        SELECT
                A.id, 
                sum(B.price) as Total, 
                count(B.B_id) as jumlah, 
                max(B.price) as maks, 
                min(B.price) as Min
        FROM 
                A INNER JOIN B ON A.ID=B.A_ID
        ";

    $data = A::model()->findAllBySql($sql);
    foreach ($data as $dataItem)
    {
	echo $dataItem['id'];
   	echo "Total = ".$dataItem['Total'];
   	echo "Count = ".$dataItem['jumlah'];
   	echo "Min = ".$dataItem['Min'];
   	echo "Max = ".$dataItem['maks'];
    }
?>



sepertinya emang harus carving sql sendiri ya bos, sementara ini keputusan apa pake relational AR atau carving sql bisa dengan mempertimbangkan kemungkinan banyaknya data...
go go Yii
Best Regard
Tidak ada coding yang salah
Yang ada hanyalah Coding yang lebih baik
0

#7 User is offline   YII alvs 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 53
  • Joined: 11-February 10

Posted 26 October 2010 - 11:40 PM

hasil nya di tampilkan dlm Cgrigview gimana bro :rolleyes:
0

#8 User is offline   aqge 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 188
  • Joined: 02-September 10
  • Location:Indonesia

Posted 28 October 2010 - 09:39 PM

View Postnasrul, on 09 October 2010 - 02:46 PM, said:

apa ada pesan error:

atau mungkin bisa dengan jalan mendefinisikan masing-masing agregate


public function relations()
{
	return array(
		'agregateSum'=>array(
			self::STAT,'B','A_id',
			'select'=>'sum(B.price)' 
		),
		'agregateCount'=>array(
			self::STAT,'B','A_id',
			'select'=>'count(B.B_id)' 
		),
		'agregateMax'=>array(
			self::STAT,'B','A_id',
			'select'=>'max(B.price)' 
		),
		'agregateMin'=>array(
			self::STAT,'B','A_id',
			'select'=>'min(B.price)' 
		),
   )
}



ada pun pada viewnya:
<?php
    $data = A::model()->findAll();
    foreach ($data as $dataItem)
    {
	echo $dataItem->id;
   		echo "Total = ".$dataItem->agregateSum;
   		echo "Count = ".$dataItem->agregateCount;
   		echo "Min = ".$dataItem->agregateMin;
   		echo "Max = ".$dataItem->agregateMax;
    }
?>



menampilkannya di gridview mungkin begini gan...
tapi belum dicoba ya cuma main comot nih
<?php $this->widget('zii.widgets.CDetailView', array(
	'data'=>$data ,
	'attributes'=>array(
		
		array('name'=>'total','value'=>$data->agregateSum->s),
		array('name'=>'count','value'=>$data->agregateCount->s),
                array('name'=>'minimal','value'=>$data->agregateMin->s),
		array('name'=>'maximal','value'=>$data->agregateMax->s),
	),
)); ?>

Best Regard
Tidak ada coding yang salah
Yang ada hanyalah Coding yang lebih baik
0

#9 User is offline   saebaryo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 74
  • Joined: 13-October 10
  • Location:jogjakarta - Indonesia

Posted 26 December 2010 - 05:14 AM

View Postnasrul, on 20 October 2010 - 12:54 AM, said:

iya, saya juga berfikir demikian, :P

mungkin bisa pula melalui findAllBySql() http://www.yiiframew...AllBySql-detail
<?php
    $sql="
        SELECT
                A.id, 
                sum(B.price) as Total, 
                count(B.B_id) as jumlah, 
                max(B.price) as maks, 
                min(B.price) as Min
        FROM 
                A INNER JOIN B ON A.ID=B.A_ID
        ";

    $data = A::model()->findAllBySql($sql);
    foreach ($data as $dataItem)
    {
	echo $dataItem['id'];
   	echo "Total = ".$dataItem['Total'];
   	echo "Count = ".$dataItem['jumlah'];
   	echo "Min = ".$dataItem['Min'];
   	echo "Max = ".$dataItem['maks'];
    }
?>



kalo seperti code diatas, apa perlu didefinisikan terlebih dahulu ya, Total, jumlah, min dan maks nya.
soalnya ketika saya coba spt cara itu mesti ada pernyataan kalo field yg digunakan sebagai "as" itu bukan merupakan property salah satu tabel.

kalo saya baca di threadsebelah ada yg jawab spt ini

View Postkodeart, on 30 May 2009 - 03:40 AM, said:

You don't have to declare all your properties. Do it as you always code in OOP fashion. It's interesting topic, lets see if this helps:

In your model

1. Declare some properties. One class property as a placeholder for dynamic fields, one array with your preferred field names and one for languages



2. The power of PHP5, magic methods. Now override __get() and __set()


3. Initialize your properties. This method will set all of them by language. It will create an array like this: 'strTextMk' = null, 'strTitleMk' => null ...>


Ok, now we have getter/setter for properties and a nice function to create them.

In your controller, or elsewhere



Also, you can isolate *global logic* in one model and extend other models from there. Override specific methods as needed. Go OOP ;)
Probably it's not the best solution, but I hope it will help you somehow.

Cheers.


View Postzaccaria, on 18 November 2010 - 02:31 AM, said:

You can achive with a bit of conditions:

$criteria= new CDbCriteria;
$criteria->select= ' SUM(`count`) as sum';
$criteria->join= 'INNER JOIN `orders` USING(`idorders`)';
$criteria->condition='`customer_company`=1';
orderDetail::model()->findAll($criteria);




In orderDetail you have also to add a property sum, in wich you will get the result.


mohon petunjuk dari suhu2 sekalian, ane gak paham maksudnya.
everything must standing on balance
0

#10 User is offline   nasrul 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 98
  • Joined: 14-April 09
  • Location:Sorowako - Indonesia

Posted 03 January 2011 - 01:16 PM

View Postsaebaryo, on 26 December 2010 - 05:14 AM, said:

kalo seperti code diatas, apa perlu didefinisikan terlebih dahulu ya, Total, jumlah, min dan maks nya.
soalnya ketika saya coba spt cara itu mesti ada pernyataan kalo field yg digunakan sebagai "as" itu bukan merupakan property salah satu tabel.
kalo saya baca di threadsebelah ada yg jawab spt ini
mohon petunjuk dari suhu2 sekalian, ane gak paham maksudnya.

menurut sy, tergantung arsitektur dbnya. biasanya untuk mysql butuh enclosed (`) untuk menandai object dalam database. sehingga sql harusnya menjadi:

$sql="
        SELECT
                `A`.`id`, 
                sum(`B`.`price`) as `Total`, 
                count(`B`.`B_id`) as `jumlah`, 
                max(`B`.price) as `maks`, 
                min(`B`.`price`) as `Minimum`
        FROM 
                `A` INNER JOIN `B` ON `A`.`ID`=`B`.`A_ID`
        ";


beda halnya denga sqlite, enclosed tidak diperlukan atau bahkan kadang dibutuhkan dengan quote (")
untuk kasus ini solve dari zaccaria mungkin yang terbaik:

View Postzaccaria, on 18 November 2010 - 02:31 AM, said:

You can achive with a bit of conditions:
$criteria= new CDbCriteria;
$criteria->select= ' SUM(`count`) as sum';   //perhatikan tanda (`) sebagai penanda fieldnya
$criteria->join= 'INNER JOIN `orders` USING(`idorders`)';
$criteria->condition='`customer_company`=1';
orderDetail::model()->findAll($criteria);

In orderDetail you have also to add a property sum, in wich you will get the result.


sehingga jika disesuaikan dengan kasus kita harusnya menjadi
$criteria= new CDbCriteria;
$criteria->select= '`A`.`id`,sum(`B`.`price`) as Total,count(`B`.`B_id`) as jumlah, max(`B`.`price`) as maks,min(`B`.`price`) as minimum';
$criteria->join= 'INNER JOIN `B` USING(`A_ID`)';
//$criteria->condition='`customer_company`=1';      //pada kasus kita tidak ada kondisi
A::model()->findAll($criteria);

atau jika Kamu telah mendefinisikan relations A ke B pada model A
public function relations()
	{
		return array(
   						'relKeB'=>array(selff::HAS_MANY, 'B', 'A_ID'),
		);
	}

kita dapat menggunakan attribut with pada CDbCriteria
$criteria= new CDbCriteria;
$criteria->select= '`id`,sum(`price`) as Total,count(`B_id`) as jumlah, max(`price`) as maks,min(`price`) as minimum';
$criteria->with='relKeB';
A::model()->findAll($criteria);

semoga berhasil
0

#11 User is offline   saebaryo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 74
  • Joined: 13-October 10
  • Location:jogjakarta - Indonesia

Posted 06 January 2011 - 12:38 PM

makasih penjelesannya gan, semakin menambah pengetahuan.
cuma cara mengakses Total itu gimana ya ?

ane coba yang lebih sederhana spt ini :
  $criteria= new CDbCriteria;
  $criteria->select= 'sum(`nominal`) as Total';
  $cari=A::model()->findAll($criteria);
  $jumlahTotal = $cari->Total;

  return $jumlahTotal;


nah dibrowser tampil pesan spt ini :

CException

Deskripsi
Properti "A.Total" tidak didefinisikan.




karena waktu mendesak selama ini sih ane pake algoritma penjumlahan aja gan, cm yg ky gitu kan gak keren ;D
everything must standing on balance
0

#12 User is offline   nasrul 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 98
  • Joined: 14-April 09
  • Location:Sorowako - Indonesia

Posted 07 January 2011 - 12:04 AM

View Postsaebaryo, on 06 January 2011 - 12:38 PM, said:

makasih penjelesannya gan, semakin menambah pengetahuan.
cuma cara mengakses Total itu gimana ya ?

ane coba yang lebih sederhana spt ini :
  $criteria= new CDbCriteria;
  $criteria->select= 'sum(`nominal`) as Total';
  $cari=A::model()->findAll($criteria);
  $jumlahTotal = $cari->Total;

  return $jumlahTotal;


nah dibrowser tampil pesan spt ini :

CException

Deskripsi
Properti "A.Total" tidak didefinisikan.




karena waktu mendesak selama ini sih ane pake algoritma penjumlahan aja gan, cm yg ky gitu kan gak keren ;D



coba pakai
$cari['Total'];
kalau mau pakai $cari->total, sepertinya harus definisikan
public $_total;
dari class model A.


1

#13 User is offline   saebaryo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 74
  • Joined: 13-October 10
  • Location:jogjakarta - Indonesia

Posted 08 January 2011 - 11:01 AM

View Postnasrul, on 07 January 2011 - 12:04 AM, said:

coba pakai
$cari['Total'];
kalau mau pakai $cari->total, sepertinya harus definisikan
public $_total;
dari class model A.




Sukses gan, Makasih banget ya agan nasrul, tp mungkin bisa dijelaskan napa mesti di definisikan
public $_total;
terlebih dahulu ?
kalo ada link referensinya, ntar ane baca sendiri, soalnya yg pernah ane baca gak ada keterangan spt itu ato mungkin ada yg terlewatkan yah :D
sebelumnya ane udah coba pake setAttribut, dll tp gagal mulu.

o iya punya ane gak ada underscore( _ ) nya. soalnya tetap errornya kek yg sebelumnya kalo di kasih underscore ( _ ) karena "as" nya "total" gitu aja gak pake _ ;)
everything must standing on balance
0

#14 User is offline   nasrul 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 98
  • Joined: 14-April 09
  • Location:Sorowako - Indonesia

Posted 09 January 2011 - 04:35 AM

View Postsaebaryo, on 08 January 2011 - 11:01 AM, said:

Sukses gan, Makasih banget ya agan nasrul, tp mungkin bisa dijelaskan napa mesti di definisikan
public $_total;
terlebih dahulu ?
kalo ada link referensinya, ntar ane baca sendiri, soalnya yg pernah ane baca gak ada keterangan spt itu ato mungkin ada yg terlewatkan yah :D
sebelumnya ane udah coba pake setAttribut, dll tp gagal mulu.

o iya punya ane gak ada underscore( _ ) nya. soalnya tetap errornya kek yg sebelumnya kalo di kasih underscore ( _ ) karena "as" nya "total" gitu aja gak pake _ ;)


alhamdulillah, ana cuma ingat2 tutorial blog pada bagian mendefinisikan model User. http://www.yiiframew.../prototype.auth
secara pada model A memang tidak ada atribut 'total', sehingga $A->total tidaklah dikenal. untuk itu kita perlu mendefinisikan atributnya secara public agar dapat diakses dari mana saja.
semoga cukup memberikan gambaran, dan mohon maaf atas keterbatasan ana.
0

#15 User is offline   saebaryo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 74
  • Joined: 13-October 10
  • Location:jogjakarta - Indonesia

Posted 09 January 2011 - 11:54 AM

mantap penghayatan agan :lol:
everything must standing on balance
0

#16 User is offline   edoriansyah 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 25-March 11
  • Location:Depok, Indonesia

Posted 20 June 2011 - 11:40 PM

Mantap gan.. Tapi ane belum tau cara menampilkan hasilnya ke CGridView?

Mohon pencerahannya dr agan2 sekalian. :D
Yii-Pii
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