CODEWITHSUNDEEP
cakephpcakephp-2.0
s0ndeb0k
s0ndeb0k

Reputation: 884

Doing multiplication between 2 fields in CakePHP 2 find method

I want to calculate the product of 2 fields in my find operation, and have the included answer in the data returned. The two fields belong to 2 related models.

2 Models: A and B. A has a belongsto relationship with a B.

A has field 'val1', and B has field 'val2'. After a find operation I would like the product of the two values stored in 'A.prod'.

What I've tried:

$this->A->find('all', array('fields' => array('val1', 'B.val2', '(val1*B.val2) AS prod'))

This almost works, but puts the product in a new array, instead of A:

(int) 0 => array(
        (int) 0 => array(
            'prod' => '6'
        ),
        'A' => array(
            'val1' => '3'
        )
        'B' => array(
            'val2' => '2'
        )
    ),

Also tried:

$this->A->find('all', array('fields' => array('val1', 'B.val2', '(val1*B.val2) AS A.prod'))

But this gives a MySQL error.

Using virtual fields:

$this->A->virtualFields = array('prod' => 'A.val1*val2');
$this->B->virtualFields = array('val2' => 'B.val2');
$this->A->virtualFields += $this->B->virtualFields;
$this->A->find('all', array('fields' => array('prod', 'B.val2'))

This gives error "Column not found: 1054 Unknown column 'val2' in 'field list'"

Upvotes: 0

Views: 499

Answers (1)

Holt
Holt

Reputation: 37606

This should work:

$this->A->virtualFields = array('prod' => 'A.val1 * B.val2');
$this->A->find('all', array('fields' => array('prod'));

Upvotes: 2

Related Questions