model 'withDeleted()' - Printable Version

model 'withDeleted()' - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: model 'withDeleted()' (/showthread.php?tid=80909)

model 'withDeleted()' - needhelp202 - 01-02-2022

Hi, I'm have a weird problem, and I don't know how to fix it. I noticed that when using the withDeleted or onlyDeleted function of a model that has useSoftDeletes as true, it will add GROUP BY and ORDER BY to the query.
Here is the php code:

PHP Code:
$this->where('product_id', $productID)->selectSum('amount', 'total')->withDeleted()->first(); 

the query generated from this function is this:

Code:
SELECT SUM(`amount`) AS `total`

FROM `orders`

WHERE `product_id` = '40'

GROUP BY`orders`.`id`

ORDER BY `orders`.`id` ASC

 LIMIT 1

but when I don't use the withDeleted function, the generated query is this:

Code:
SELECT SUM(`amount`) AS `total`

FROM `orders`

WHERE `product_id` = '40'

AND `orders`.`cancelled_at` IS NULL

 LIMIT 1

is there any way to let the model build a query like this:

Code:
SELECT SUM(`amount`) AS `total`

FROM `orders`

WHERE `product_id` = '40'

 LIMIT 1

RE: model 'withDeleted()' - iRedds - 01-03-2022

$this->where('product_id', $productID)->selectSum('amount', 'total')->limit(1)->get()->getFirstRow();

RE: model 'withDeleted()' - kenjis - 01-03-2022

I don't understand why GROUP BY and ORDER BY are added only when withDeleted() is used.

RE: model 'withDeleted()' - iRedds - 01-03-2022

@kenjis
ORDER BY gives a more predictable result when using LIMIT.
This is indicated by a comment in the code of the Model::doFirst() method with a reference to PostgreSQL.
The presence of GROUP BY, I cannot explain yet.