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


Problem :

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.



Solution :

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)

Mysql Tutorials

Mysql Howto..

How do I parse xml to insert it into a mysql database?

How to make a MySQL generated table interactive PHP

MySQL - how to select query values which doesn't have a specific data

How to generate a dynamic sequence table in MySQL?

how to get avarage time 'xx:xx:xx' on mysql?

How to use “value” as a column name in MySql?

How to design category and subcategories in MySQL?

How to avoid duplicate entry in mysql with php

how to change font in mysql database to store unicode charactors

How can I fetch lists within lists (similar to multidimensional arrays)?

How to insert new (only changed) lines from a csv file into a mysql table

How to select unique value randomly from a table in heavy load server

How to create MySql Update Command to work with DataGridView?

How to handle fragmentation of auto increament ID column in MySQL

How to update MySQL(MyISAM) table in parallel?

how to edit table column data with jquery/ajax and save to mysql

Inserting a row in mysql database it is showing rows twice

How to avoid encoding warning when inserting binary data into a blob column in MySQL using Python 2.7 and MySQLdb

How can I build my own version of facebook FQL?

How to print the Text data type in mysql

how to connect android to online database?

show a dynamic select input from mysql table

MySQL - How to create a new table that is a join on primary key of two existing tables

How to find duplicates in mysql table using PHP?

how to handle a large php mysql $_POST UPDATE/INSERT

How to select rows with same ID sharing a group of values?

How to add an extra row at end of query with value of (previousRowColA + 1) in mysql?

How to fetch the row from MySQL having latest date

How can I determine type of mysql database : whether it is InnoDB or MyISAM?

How to backup a MySQL database?