Jump to content
Sign in to follow this  
Blesta Addons

Query in Record

Recommended Posts

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 ?

Share this post


Link to post
Share on other sites

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();

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×