# Use dynamic subquery in insert/update query

**URL:** <https://discourse.cakephp.org/t/use-dynamic-subquery-in-insert-update-query/5118>\
**Category:** Need Help\
**Tags:** behavior\
**Created:** [October 18, 2018, 9:09am UTC](https://discourse.cakephp.org/t/use-dynamic-subquery-in-insert-update-query/5118 "2018-10-18T09:09:16Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![cmeshram](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@cmeshram](https://discourse.cakephp.org/u/cmeshram)\
**Post date:** [October 18, 2018, 9:09am UTC](https://discourse.cakephp.org/t/use-dynamic-subquery-in-insert-update-query/5118/1 "2018-10-18T09:09:16Z")

</div>

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 ☹

$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?

---

<div class="post-metadata">

**Author:** ![cmeshram](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@cmeshram](https://discourse.cakephp.org/u/cmeshram)\
**Post date:** [October 22, 2018, 11:54am UTC](https://discourse.cakephp.org/t/use-dynamic-subquery-in-insert-update-query/5118/2 "2018-10-22T11:54:17Z")

</div>

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'); 

```
`

🙂