CakePHP找到最近的日期

Question

EDIT: I figured it out. Check at the bottom for my solution.

I'm trying to query for all rows where the MOST RECENT activity.date is NOT between 30 days in the past AND 30 days in the future. In other words, don't select them if they have an activity in the last month. Here's the array being sent to find() as it is right now:

'Declined' => array(
            'conditions' => array(
                'Program.deal_status' => 'declined',
                "Program.date_submitted > DATE_SUB(CURDATE(), INTERVAL 60 DAY)",
                'Activity.date NOT BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) and DATE_ADD(CURDATE(), INTERVAL 30 DAY)',
            ),
            'joins' => array(
                array(
                    'table' => 'programs',
                    'alias' => 'Program',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'Program.customer_id = Customer.customer_id'
                    )
                ),
                array(
                    'table' => 'activities',
                    'alias' => 'Activity',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'Activity.customer_id = Customer.customer_id',
                    )
                ),
            ),
            'order' => array(
                'Program.date_submitted DESC',

            ),
            'group' => array(
                'Customer.customer_id',
            ),
            'fields' => array(
                'Customer.customer_id',
                "CONCAT_WS(' ', CustomerPersonalInformation.first_name, CustomerPersonalInformation.last_name) AS full_name",
                'CustomerContactInformation.email',
                'Program.date_submitted',
                'Program.underwriters_notes',
                'Activity.date',
            )
        )

I've tried doing MAX(Activity.date), but no luck at all. I'm sorta thinking it's got something to do with the joins. Since there can be many activities associated with a single customer_id, I think it's just joining the first activity row it can find. I'm not sure how to make it so that it's picking the MOST RECENT activity.date.

Cake version is 2.4.5.

Thanks, any help is much appreciated.

SOLUTION:

'Declined' => array(
            'conditions' => array(
                'Program.deal_status' => 'declined',
                "Customer.sales_associate {CONDITION}",
                "Program.date_submitted > DATE_SUB(CURDATE(), INTERVAL 60 DAY)",
                'Customer.store {STORE_CONDITION}',
            ),
            'joins' => array(
                array(
                    'table' => 'programs',
                    'alias' => 'Program',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'Program.customer_id = Customer.customer_id'
                    )
                ),
                array(
                    'table' => 'activities',
                    'alias' => 'Activity',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'Activity.customer_id = Customer.customer_id',
                    )
                ),
            ),
            'order' => array(
                'Program.date_submitted DESC',
                "MAX(Activity.date) ASC"
            ),
            'group' => array(
                'Activity.customer_id HAVING most_recent NOT BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) and DATE_ADD(NOW(), INTERVAL 30 DAY)'
            ),
            'fields' => array(
                'customer_id' => 'Customer.customer_id',
                'Customer' => "CONCAT_WS(' ', CustomerPersonalInformation.first_name, CustomerPersonalInformation.last_name) AS full_name",
                'Email' => 'CustomerContactInformation.email',
                'Program Date' => 'Program.date_submitted',
                'Underwriter notes' => 'Program.underwriters_notes',
                'Last Activity Date' => 'Activity.date',
                'hidden' => 'MAX(Activity.date) AS most_recent'
            )
        ),

Answer 1

SOLUTION:

'Declined' => array(
        'conditions' => array(
            'Program.deal_status' => 'declined',
            "Customer.sales_associate {CONDITION}",
            "Program.date_submitted > DATE_SUB(CURDATE(), INTERVAL 60 DAY)",
            'Customer.store {STORE_CONDITION}',
        ),
        'joins' => array(
            array(
                'table' => 'programs',
                'alias' => 'Program',
                'type' => 'LEFT',
                'conditions' => array(
                    'Program.customer_id = Customer.customer_id'
                )
            ),
            array(
                'table' => 'activities',
                'alias' => 'Activity',
                'type' => 'LEFT',
                'conditions' => array(
                    'Activity.customer_id = Customer.customer_id',
                )
            ),
        ),
        'order' => array(
            'Program.date_submitted DESC',
            "MAX(Activity.date) ASC"
        ),
        // HAVE TO GROUP BY THE DATE
        'group' => array(
            'Activity.customer_id HAVING most_recent NOT BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) and DATE_ADD(NOW(), INTERVAL 30 DAY)'
        ),
        'fields' => array(
            'customer_id' => 'Customer.customer_id',
            'Customer' => "CONCAT_WS(' ', CustomerPersonalInformation.first_name, CustomerPersonalInformation.last_name) AS full_name",
            'Email' => 'CustomerContactInformation.email',
            'Program Date' => 'Program.date_submitted',
            'Underwriter notes' => 'Program.underwriters_notes',
            'Last Activity Date' => 'Activity.date',
            'hidden' => 'MAX(Activity.date) AS most_recent'
        )
    ),