setRelation Where In error

Grocery CRUD Enterprise
1

hello @johnny

i have set relation with where in clause but showing error

if i set relation like this
$crud->setRelation(‘user_id’, ‘presensi_emp’, ‘nama’,“user_id in (‘152’,‘69’)”);

this will showing error
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_id' in where clause is ambiguous in file D:\laragon8\www\siintan\vendor\laminas\laminas-db\src\Adapter\Driver\Pdo\Connection.php on line 376

then i change my relation with this script
$crud->setRelation(‘user_id’, ‘presensi_emp’, ‘nama’,“presensi_emp.user_id in (‘152’,‘69’)”);
but still showing error with other message

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'presensi_emp.user_id' in 'where clause' in file D:\laragon8\www\siintan\vendor\laminas\laminas-db\src\Adapter\Driver\Pdo\Connection.php on line 376

this is my complete controller

public function validasi_kinerja_utama_harian($extra)
{
$crud = $this->general->_getGroceryCrudEnterprise();
$crud->setTable(‘sdm_tr_kinerja’);
$crud->where(['status_validasi not like ? ’ => ‘%Valid%’]);
$crud->setSubject(‘Kinerja Utama Harian’, ‘Kinerja Utama Harian’);
$crud->columns([‘user_id’,‘kinerja_id’, ‘tanggal’, ‘bukti_kuantitas’, ‘bukti_kualitas’, ‘status’, ‘nilai_kuantitas’, ‘status_validasi’]);
$crud->displayAs([‘kinerja_id’ => ‘Rencana Kinerja’, ‘nilai_kuantitas’ => ‘Nilai’,‘user_id’=>‘Pegawai’]);
$crud->setRelation(‘user_id’, ‘presensi_emp’, ‘nama’,“presensi_emp.user_id in (‘152’,‘69’)”);
$crud->setRelation(‘kinerja_id’, ‘sdm_mst_kinerja’, ‘rencana_kinerja’, [‘tahun’ => date(‘Y’), ‘jenis’ => ‘Utama’]);
$crud->editFields([‘kinerja_id’, ‘tanggal’, ‘bukti_kuantitas’, ‘bukti_kualitas’, ‘status’, ‘nilai_kuantitas’, ‘komentar_validator’]);
$crud->where(“sdm_tr_kinerja.user_id in (‘152’,‘69’)”);
$crud->unsetAdd();
$crud->unsetDelete();
$crud->callbackAddField(‘tanggal’, function ($fieldType, $fieldName) {
return ‘’;
});
$crud->callbackEditField(‘tanggal’, function ($fieldValue, $primaryKeyValue, $rowData) {
return ‘’;
});
$crud->defaultOrdering(‘sdm_tr_kinerja.id’, ‘desc’);
$crud->fieldType(‘tanggal’, ‘string’);
$crud->defaultOrdering(‘tanggal’,‘desc’);
$crud->requiredFields([‘kinerja_id’, ‘status’, ‘bukti_kualitas’]);
$crud->setFieldUploadMultiple(‘bukti_kuantitas’, ‘assets/uploads/kinerja’, ‘/assets/uploads/kinerja/’);
$output = $crud->render();
return $this->general->_showOutput($output, null, @$extra);
}

regards
Nasrul

2

$crud->setRelation(‘user_id’, ‘presensi_emp’, ‘nama’,“presensi_emp.user_id in (‘152’,‘69’)”);

maybe you miss a bracket,
maybe become like this

$crud->setRelation(
  'user_id',
  'presensi_emp',
  'nama',
  ['`presensi_emp.user_id` in ?' => '("152","69")']
);

hope this help

regards,
dodo

3

thank you @dodo.selusin

i have implement your suggestion
but became error like this

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''("152","69")' ORDER BY nama ASC'

But it doesn’t matter, I’m changing the business flow so that it doesn’t require a set relation and where in clause.

regards
Nasrul

1 Like