CODEWITHSUNDEEP
sqlselectzend-framework2
vanamerongen
vanamerongen

Reputation: 837

Calculate percentage in query ZF2

Is there a way to calculate the percentage between two values in a table and select it as a column? I know it's possible, but I'd like to know if it's possible in a ZF2 context specifically.

I have a select in my ZF2 application that fetches a bunch of data from a db (SQL Server). This query concerns a table "libraries" that I want to order by free disk space (a column in the table). But I don't want it to order by the absolute amount of free space but rather the percentage relative to the total disk space.

So I mean something like

libraries.freeSpace / libraries.totalSpace as 'percentage'

but within a ZF2 select. This is the query currently:

$resultSet = $this->tableGateway->select(function(Select $select) use($report){
    $select->where('id = ' . $report[0]->customer)
    ->order('freeSpace', 'asc');
});

e: ANSWER.

Use Zend\Db\Sql\Expression your model.

Add columns to your select:

$select->columns(array(
    'percentage' => new Expression('cast(libraries.freeSpace') as float / cast.('libraries.totalSpace') as float', false);

Upvotes: 1

Views: 632

Answers (1)

Andrew
Andrew

Reputation: 12809

You can pass an expression to Columns, don't forget the second parameter, you will need to set this to FALSE to stop the table prefix being automatically added for you when you manually supply them.

use Zend\Db\Sql\Expression; 

$resultSet = $this->tableGateway->select(function(Select $select) use($report){
    $select->columns(array(
            'percentage' => new Expression('libraries.freeSpace / libraries.totalSpace')
        ), FALSE)
        ->where('id = ' . $report[0]->customer)
        ->order('freeSpace', 'asc')
    ;
});

Upvotes: 2

Related Questions