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->…

Accepted Answer

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)`