Blesta Addons Posted November 12, 2017 Report Share Posted November 12, 2017 i have the fallowing Sql query that work in mysql as i want, but when i request it via the record components it return a empty result, when i have investigated it appear that the record components add some strange qoute SELECT `invoices`.`client_id`, `invoices`.`currency`, `invoices`.`total`, SUM(`invoices`.`total`) - SUM(`invoices`.`paid`) as 'total_amount' FROM `invoices` WHERE `invoices`.`status`= 'active' GROUP BY `invoices`.`client_id` in the Record Compement i have this $fields = [ 'invoices.total', 'invoices.currency', 'invoices.client_id', 'SUM(invoices.total) - SUM(invoices.paid)' => 'total_invoices' ]; $this->Record->select($fields) ->from('invoices') ->where('invoices.status', 'in', ['active', 'proforma']) ; $invoices = $this->Record ->group(['invoices.client_id']) ->limit($this->getPerPage(), (max(1, 1) - 1) * $this->getPerPage()) ->fetchAll(); after a small check the above query return the fallowing statement SELECT `invoices`.`total`, `invoices`.`currency`, `invoices`.`client_id`, SUM(`invoices`.`total`)-`SUM`(`invoices`.`paid`) AS `total_invoices` FROM `invoices` WHERE `invoices`.`status` IN (?,?) GROUP BY `invoices`.`client_id` LIMIT 0, 20 as you see the error come from the fallowing "SUM(`invoices`.`total`)-`SUM`(`invoices`.`paid`)" normally it should be SUM(`invoices`.`total`)-SUM(`invoices`.`paid`) it appear that the record components add the quote, is their any way to make it work the record components ? Quote Link to comment Share on other sites More sharing options...
Blesta Addons Posted November 12, 2017 Author Report Share Posted November 12, 2017 after a quick look at the record class, we found a escape parameter should be false, so we fixed the query with the fallowing : $fields = [ 'invoices.total', 'invoices.currency', 'invoices.client_id', //'SUM(invoices.total) - SUM(invoices.paid)' => 'total_invoices' ]; $this->Record->select($fields) ->select(['SUM(invoices.total) - SUM(invoices.paid)' => 'total_amount'], false) ->from('invoices') ->where('invoices.status', 'in', ['active', 'proforma']) ; $invoices = $this->Record ->group(['invoices.client_id']) ->limit($this->getPerPage(), (max(1, 1) - 1) * $this->getPerPage()) ->fetchAll(); Quote Link to comment Share on other sites More sharing options...
Tyson Posted November 13, 2017 Report Share Posted November 13, 2017 Just wanted to confirm here that any non-fields, like aggregate expressions or custom values, in your SELECT should not be escaped by passing false in Record::select(..fields.., false), as you've discovered already. See the Record documentation for more. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.