Need help for query builder

Need Help
1

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’)], true);

$result = $queryObject ->count();

here query is

$queryObject = " SELECT count(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. :slight_smile:

2

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.

3

You can use Query objects cleancopy method

5

Thanks, Its works :slight_smile:

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

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