Use dynamic subquery in insert/update query

Need Help
1

Hi,

i am using cakephp 3.*

I have search several forum for this topic but there is no any comment for using subquery in insert query.

I am trying to execute insert query with subquery like:

// option 1
$subquery = $this->tableModel->query()->select([‘maxLRS’=>‘max(fld2)+1’]);
// option 2
//$subquery = ‘( SELECT max(fld2)+1 AS maxLRS FROM tblname as tbl_12 )’;

// option 3
//$subquery = $this->table->find(‘all’)->select([‘maxLRS’=>‘max(fld2)+1’])->all->toArray();
//==> $subquery[maxLRS];
// option 3 give final value but it make execution slow for loop. so its not recommended for me :frowning:

$dataArray = [
‘fld1’ => $id,
‘fld2’ => $subquery,
‘fld3’ => $countdt
];

$DataEnter = $this->tableModel->newEntity();
$DataEnter = $this->tableModel->patchEntity($DataEnter , $dataArray ,[‘validate’ => false]);
$this->tableModel->save($DataEnter );

its give me final query as follows:
/**********************************************************/
// select query with single quote.

INSERT INTO tblname(
fld1, fld2,fld3
)
VALUES
(
‘2’, ‘( SELECT max(fld2)+1 AS maxLRS FROM tblname as tbl_12 )’,
‘101’
);

/**********************************************************/
but its enter into database as subquery string because its add single quote for subquery.

how can i achieve this as expected query without single quote ==>
/**********************************************************/
// select query without single quote.

INSERT INTO tblname(
fld1, fld2,fld3
)
VALUES
(
‘2’, ( SELECT max(fld2)+1 AS maxLRS FROM tblname as tbl_12 ),
‘101’
);

/**********************************************************/
so its add proper data as expected.

is there any other way to do this?

I read about model.beforeSave behaviour, but how can i use and how to pass behaviour data to query?

2

Don’t use patchEntity and save method, use different way to save data with query->insert() method

#solution

// subquery
$subquery = $this->tableModel->find()->select(['maxVal'=>'(MAX(fld2)+1)']);

$dataArray = [
	'fld1' => $id,
	'fld2' => $subquery,
	'fld3' => $countdt
];
   
    // insert query
$query = $this->tableModel->query()
		->insert(array_keys($dataArray))   // pass only fields name array
		->values($dataArray)                     // pass fields and value array
		->execute();

    // last inserted Id
$insertID =  $query->lastInsertId('tableName');

:slight_smile: