[FIXED] MySQL query - 2 sets of conditions

Issue

I've been thinking about this for too long, and have got myself confused! I'm trying to come up with a MySQL query in CakePHP, for the following scenario:

Each record has 2 dates; for the sake of simplicity I'll call them F & I. These determine the quality of the data, as follows:

• Bad data - F & I are both null
• Good data - F & I are both non-null and represent dates within the past 3 months 'In between' data - everything else

The first 2 are easy enough:

Red:

$conditions['AND'] = array('F' => null, 'I' => null);

Green:

$conditions['AND'] = array(
     'F >=' => Three months ago,
     'I >=' => Three months ago
)

But I'm having trouble with the third option. I've tried a few things, and I know it's something like:

$conditions['NOT'] = array(
     'AND' => array(
         'F >=' => Three months ago,
         'I >=' => Three months ago
     ),
     'AND' => array(
          'F' => null,
          'I' => null
     )
)

but obviously that's not going to work because it has two different values for 'AND'.

Solution

I think there's a problem in your boolean logic. If you want F & I to represent dates within the past three months, then you're ruling out any entries where F >= three months OR I >= three months.

It'd be less confusing to use De Morgan’s law on your boolean logic -- NOT (F >= three months OR I >= three months) is the same as F < three months AND I < three months. Therefore, for the third rule, your conditions array would look like:

array('conditions' => array('AND' => array('F <' => three months, 'I <' => three months)));

Answered By - Kai