# Need help for query builder

**URL:** <https://discourse.cakephp.org/t/need-help-for-query-builder/3836>\
**Category:** Need Help\
**Created:** [January 30, 2018, 1:50pm UTC](https://discourse.cakephp.org/t/need-help-for-query-builder/3836 "2018-01-30T13:50:06Z")\
**Posts on this page:** 4\
**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:** [January 30, 2018, 1:50pm UTC](https://discourse.cakephp.org/t/need-help-for-query-builder/3836/1 "2018-01-30T13:50:06Z")

</div>

Hi,  
Using cakePHP 3.x

I am working on large complex query having 6-8 table join dynamically as per condition and required fields.  
by running this query its take more time to execute.

$queryObject = " SELECT FIELDS(20+ from different join tables) FROM table1  
LEFT OUTER JOIN table2 ON condition  
LEFT OUTER JOIN table3 ON condition  
LEFT OUTER JOIN table4 ON condition  
LEFT OUTER JOIN table5 ON condition  
LEFT OUTER JOIN table6 ON condition  
LEFT OUTER JOIN table7 ON condition  
LEFT OUTER JOIN table8 ON condition  
LEFT OUTER JOIN table9 ON condition  
WHERE condition  
GROUP BY table2.fields"

I want result for 1) Count() and 2) data result =\> all()-\>toArray()

**1) count**

if use $queryObject -\>count() its take more time.

for reduce execution time I modify query object remove select fields and add only count() as

$queryObject = $queryObject -\> **select** ([‘recordCount’ =\> $queryObject -\>func()-\>count(‘[table1.id](http://table1.id)’)], **true** );

$result = $queryObject -\>count();

here query is

$queryObject = " SELECT **count([table1.id](http://table1.id))** FROM table1  
LEFT OUTER JOIN table2 ON condition  
LEFT OUTER JOIN table3 ON condition  
LEFT OUTER JOIN table4 ON condition  
LEFT OUTER JOIN table5 ON condition  
LEFT OUTER JOIN table6 ON condition  
LEFT OUTER JOIN table7 ON condition  
LEFT OUTER JOIN table8 ON condition  
LEFT OUTER JOIN table9 ON condition  
WHERE condition  
GROUP BY table2.fields"

this gives result with less time.

**But**

**2) For data retrieve**

here I am **expecting my original queryObject**. but its give me modified query object as in 1) count section.

Please give me any suggestion to roll back my query object as original for data retrieve with select fields.  
here I am trying to store queryObject in different variable but its modify original query Object.

I want LIMIT for this result set. if I go with COUNT() it exceed LIMIT().  
I want LIMIT for some condition too.

Thanks in advance. 🙂

---

<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:** [January 31, 2018, 10:16am UTC](https://discourse.cakephp.org/t/need-help-for-query-builder/3836/2 "2018-01-31T10:16:22Z")

</div>

In simple term, I have create Query then I modified some select fields and execute. but now for all data records I want to original query (before modify). how I can **reset (revert back) original query**.

is there any way to get all **select fields** from query object.

its urgent please help for this problem.

thanks.

---

<div class="post-metadata">

**Author:** ![rrd](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/rrd/32/113_2.png) [@rrd](https://discourse.cakephp.org/u/rrd)\
**Post date:** [January 31, 2018, 5:02pm UTC](https://discourse.cakephp.org/t/need-help-for-query-builder/3836/3 "2018-01-31T17:02:11Z")

</div>

You can use Query objects cleancopy method

---

<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:** [February 1, 2018, 6:08am UTC](https://discourse.cakephp.org/t/need-help-for-query-builder/3836/5 "2018-02-01T06:08:53Z")

</div>

Thanks, Its works 🙂

```
$queryCount = $query->cleanCopy();
$queryCount = $queryCount->select(['recordCount' => $queryCount->func()->count('*')], true);
$result = $queryCount->count();
		

echo '<br> modified -->>> '.$queryCount;
    echo '<br><br> orginal -->>>> '.$query;
```