[Fachrizal]

gan , mau tanya dong,,,

gmana klo query ane ini dimasukin ke cdbcriteria, gini querynya :

SELECT
a.nik,
a.nama,
b.status,
b.tahun,
b.bulan
FROM rpt0001 a
LEFT JOIN docspkl b
ON a.nik = b.nik
WHERE a.bandposisi = 'VI'
AND a.namaunit = 'blablabla';

mohon bantuaannya gan ....

hasil dari criteria mau dipake di :

return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
        ));

terima kasih....

[petra]

Kalo pake CActiveDataProvider, tabel utamanya biasanya direname jadi `t`.
Harusnya gini.

$criteria = new CDbCriteria(array(
		'select' => array(
			'`t`.`nik`',
			'`t`.`nama`',
			'`b`.`status`',
			'`b`.`tahun`',
			'`b`.`bulan`'),
		'join' => 'LEFT JOIN `docspkl` AS `b` ON `t`.`nik` = `b`.`nik`',
		'condition' => '`t`.`bandposisi` = :bandposisi AND `t`.`namaunit` = :namaunit',
		'params' => array(
			':bandposisi' => 'VI',
			':namaunit' => 'blablabla',
		)
	));

[Fachrizal]

petra, on 14 December 2011 - 10:00 PM, said:

Kalo pake CActiveDataProvider, tabel utamanya biasanya direname jadi `t`.
Harusnya gini.

$criteria = new CDbCriteria(array(
		'select' => array(
			'`t`.`nik`',
			'`t`.`nama`',
			'`b`.`status`',
			'`b`.`tahun`',
			'`b`.`bulan`'),
		'join' => 'LEFT JOIN `docspkl` AS `b` ON `t`.`nik` = `b`.`nik`',
		'condition' => '`t`.`bandposisi` = :bandposisi AND `t`.`namaunit` = :namaunit',
		'params' => array(
			':bandposisi' => 'VI',
			':namaunit' => 'blablabla',
		)
	));

oke terima kasih banyak mas petra ..... ternyata solved ,,, thx yak ...

[ahmadroni]

Mantab gan.. ternyata udah Solve

[Vataraxia]

gan, mau lanjutin nanya, kalo sql sprti ini:

$tampung = select name from tbl_customer where lower(code) like 'cd%' limit 1;

kalo memakai cdbcriteria jadinya gmn gan?

[Hermans]

Kalau t_customer pakai ActiveRecord (misal Customer namanya).

Pakai ActiveProvider:

$criteria=new CDbCriteria;

$criteria->condition = "lower(t.code) like 'cd%'";
$criteria->limit = 1;
$criteria->order = 'model';
		
		$dataProvider = new CActiveDataProvider("Customer",array(
			'keys'	=> array('product_id'),
			'sort' => array(
					'attributes' => array(
							'model',
						),
				),
			'criteria' => $criteria,
			));

kalau mau langsung pakai sql: Ini

[qikio]

mau tanya juga, klu sql seperti ini gmn bikin activedataprovidernya?

SELECT p.nama_program AS program, SUM(i.jumlah_definitif) AS pagu
FROM tahun_anggaran t, item i, akun a, suboutput so, komponen k, subkomponen sk, kegiatan kg, program p, dipa d
WHERE d.tahun_anggaran=t.tahun_anggaran AND t.aktif=1 AND i.id_akun=a.id_akun AND a.id_suboutput=so.id_suboutput AND a.id_komponen=k.id_komponen AND a.id_subkomponen=sk.id_subkomponen AND so.id_kegiatan=kg.id_kegiatan AND kg.id_program=p.id_program GROUP BY p.nama_program


Bingung, soalnya tabel 't' nya adalah tabel dipa, tapi ga ada atribut yg perlu dimunculin dari tabel dipa itu sendiri.

[Vataraxia]

@Hermans,, very helpful gan,, thanks

[amculin]

Aku ada masalah nih

SQL asli:

SELECT d.id, d.swt_users_id, d.company_desc, d.name, d.company_logo, v.start_date, v.close_date
FROM ccn_employer_data d
JOIN ccn_employer_vacancy v ON d.swt_users_id = v.swt_users_id
WHERE v.online = 1
GROUP BY d.swt_users_id
ORDER BY v.start_date

$dataProvider=new CActiveDataProvider('CcnEmployerData',array(
			'criteria' => array(
				'alias'		=> 'd',
				'select'	=> 'd.id, d.swt_users_id, d.company_desc, d.name, d.company_logo, v.start_date, v.close_date',
				'join'		=> 'JOIN ccn_employer_vacancy v ON d.`swt_users_id` = v.`swt_users_id`',
				'condition'	=> 'v.online = 1 ',
				'group'		=> 'd.swt_users_id',
				'order'		=> $order
			),
			'pagination'=>array(
				'pageSize'=>5,
			)
		));

Kalo pake JOIN gitu kenapa hasil seleksi dari tabel yg dijoin g tampil yah? dalam contoh di atas adalah v.start_date dan v.close_date yang nggak mau tampil..

Ada solusi?