Blesta Addons Posted November 12, 2017 Report 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 ?
Blesta Addons Posted November 12, 2017 Author Report 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();
Tyson Posted November 13, 2017 Report 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.
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now