Magento - How to select mysql rows by max value?
- by Damodar Bashyal
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 | damodar@stackoverflow.com.au | 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
