How to generate a MySQL IS NOT NULL condition in CakePHP?

Question

I'm trying to get a subset of results as a virtualField for use in my view. I may even be way off on how I'm approaching this, but here's what I've done so far:

I started with this question here: CakePHP virtualField find all not null which lead to this little beauty.

Now I have an issue where the find statement passing (Array) into the MySQL.

My code looks like:

class Transaction extends AppModel {
public function __construct($id = false, $table = null, $ds = null) {
    parent::__construct($id, $table, $ds);
    $this->virtualFields['Accounts'] = $this->find("all", array("conditions" => array("account !=" => null)));
}

And I'm seeing:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'field list'

SQL Query: SELECT `Transaction`.`id`, `Transaction`.`name`, 
`Transaction`.`person_id`, `Transaction`.`account`, (Array) 
AS `Transaction__Accounts` FROM `my_database`.`transactions` 
AS `Transaction` WHERE `Transaction`.`person_id` = (2)

I've also tried $this->Transaction->find and "Transaction.account !=", to no avail. I've found some other issues with the (Array) but none that help my situation. Any pointers in the right direction would be great.

Answer 1

Problem: your query results are an array, and you're telling SQL to assign a field name to each query result containing that array - virtual fields are only made to contain single level variables like strings.

Solution: use a join structure onto itself with those conditions which will return a nested result set along with each of your results. Use CakePHP's model relationships to do this:

<?php    
class Transaction extends AppModel {
    var $hasMany = array(
        'Accounts' => array(
            'className' => 'Transaction',
            'foreignKey' => false,
            'conditions' => array('Accounts.account IS NOT NULL')
        )
    );
}    
?>

Example output:

Array(
    'Transaction' => array( // transaction data),
    'Accounts' => array( // associated transaction data with account set to null
)

Now, as you can probably gather from that result, if you return 1000 rows from Transaction, you'll get all results from Accounts nested into each Transaction result. This is far from ideal. From here, you can either make the join conditions more specific to target relevant Accounts records, or this is not the right approach for you.

Other approaches could be:

• Accounts model, uses Transaction database table, implicit find conditions are that account is null
• Manual query to retrieve these results in the afterFind() method of your Transaction model, which will retrieve these results once, and you'll then return array_merge($accounts, $transactions)