mysql> SELECT * FROM `log_customer` WHERE `customer_id` = 224 LIMIT 0, 30;
+--------+------------+-------------+---------------------+-----------+----------+
| log_id | visitor_id | customer_id | login_at            | logout_at | store_id |
+--------+------------+-------------+---------------------+-----------+----------+
|    817 |      50139 |         224 | 2011-03-21 23:56:56 | NULL      |        1 |
|    830 |      52317 |         224 | 2011-03-27 23:43:54 | NULL      |        1 |
|   1371 |     136549 |         224 | 2011-11-16 04:33:51 | NULL      |        1 |
|   1495 |     164024 |         224 | 2012-02-08 01:05:48 | NULL      |        1 |
|   2130 |     281854 |         224 | 2012-11-13 23:44:13 | NULL      |        1 |
+--------+------------+-------------+---------------------+-----------+----------+
5 rows in set (0.00 sec)


mysql> SELECT *  FROM `customer_entity` WHERE `entity_id` = 224;
+-----------+----------------+---------------------------+----------+---------------------+---------------------+
| entity_id | entity_type_id |  email                    | group_id | created_at          | updated_at          |
+-----------+----------------+---------------------------+----------+---------------------+---------------------+
|       224 |              1 |  [email protected] |        3 | 2011-03-21 04:59:17 | 2012-11-13 23:46:23 |
+-----------+----------------+---------------------------+----------+--------------+----------+-----------------+
1 row in set (0.00 sec)

How can i search for customers who hasn't logged in for last 10 months and their account has not been updated for last 10 months. I tried below but failed.

$collection = Mage::getModel('customer/customer')->getCollection();
$collection->getSelect()->joinRight(array('l'=>'log_customer'), "customer_id=entity_id AND MAX(l.login_at) <= '" . date('Y-m-d H:i:s', strtotime('10 months ago')) . "'")->group('e.entity_id');
$collection->addAttributeToSelect('*');
$collection->addFieldToFilter('updated_at', array(
    'lt' => date('Y-m-d H:i:s', strtotime('10 months ago')),
    'datetime'=>true,
));
$collection->addAttributeToFilter('group_id', array(
    'neq' => 5,
));

Above tables have one customer for reference. I have no idea how to use MAX() on joins. Thanks

UPDATE:

This seems returning correct data, but I would like to do magento way using resource collection, so i don't need to do load customer again on for loop.

$read = Mage::getSingleton('core/resource')->getConnection('core_read');
$sql = "select * from (
                select e.*,l.login_at
                    from customer_entity as e
                    left join log_customer as l
                    on l.customer_id=e.entity_id
                    group by e.entity_id
                    order by l.login_at desc
            ) as l
            where (
                l.login_at <= '".date('Y-m-d H:i:s', strtotime('10 months ago'))."'
                or (
                    l.created_at <= '".date('Y-m-d H:i:s', strtotime('10 months ago'))."'
                    and
                    l.login_at is NULL
                    )
                )
            and
            group_id != 5";
$result = $read->fetchAll($sql);

I have loaded full shell script to github

• https://github.com/dbashyal/Magento-ecommerce-Shell-Scripts/blob/master/shell/suspendCustomers.php