Query in Record

[Blesta Addons]

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]

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]

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.