Magento - How to select mysql rows by max value?

Magento - How to select mysql rows by max value?

October 7, 2026 ยท Illustrated author avatarBy Stefan Bogdanescu
Magento - How to select mysql rows by max value?

Selecting MySQL Rows by Max Value: A Comprehensive Guide

As a Magento developer, you frequently need to query customer data based on various criteria. One common requirement is selecting customers who haven't logged in or whose accounts haven't been updated for a certain period. In this post, we will explore how to achieve this using MySQL and Magento's resource collection.

Understanding the Challenge

To identify customers who haven't logged in for 10 months, you need to join the log_customer table with the customer_entity table. The main challenge lies in using the MAX() function on a joined table and applying the appropriate filters.

Approach Using MySQL

The initial approach attempted to use Magento's resource collection, but failed due to incorrect usage of the MAX() function. Let's examine the correct SQL syntax:

SELECT e.*, MAX(l.login_at) AS login_at
FROM `customer_entity` e
LEFT JOIN `log_customer` l ON e.entity_id = l.customer_id
GROUP BY l.customer_id
HAVING created_at < '".date('Y-m-d H:i:s', strtotime('10 months ago'))."'
AND (login_at< '".date('Y-m-d H:i:s', strtotime('10 months ago'))."' OR login_at IS NULL)
AND group_id != 5
ORDER BY `e`.`entity_id` ASC;

This query selects the maximum login time for each customer (MAX(l.login_at) AS login_at) and filters out customers who haven't logged in or whose accounts aren't updated within the last 10 months. It also excludes customers from group ID 5.

Approach Using Magento Resource Collection

While the SQL query provides the desired results, it is more efficient to use Magento's resource collection for better performance and compatibility. Let's modify the provided example to accomplish this:

$collection = Mage::getModel('customer/customer')->getCollection();
$collection->getSelect()->joinRight(array('l' => 'log_customer'), "customer_id=entity_id AND login_at <= '" . date('Y-m-d H:i:s', strtotime('-10 months')) . "'", array('login_at'));
$collection->addAttributeToFilter('group_id', array(
    'neq' => 5,
));

$now = date('Y-m-d H:i:s');
$tenMonthsAgo = Mage::app()->getLocale()->date($now, Varien_Date::DATE_INTERNAL, false)->subMonth(10)->toString('m/d/Y');

$collection->addAttributeToFilter('created_at', array(
    'lt' => $now,
    'datetime' => true
));

This modified code joins the log_customer table, filters customers who haven't logged in within the last 10 months, and applies other necessary filters. It uses Magento's date manipulation functions for better compatibility.

Suspend Customers Shell Script on GitHub

For a full implementation of suspending customers based on inactivity, you can refer to the shell script available on GitHub. This script demonstrates how to load and process customer data efficiently using Magento's resource collection.

Conclusion

Selecting MySQL rows by max value in Magento requires a strategic approach that combines SQL queries with the framework's built-in resource collection. By understanding the underlying tables and applying appropriate filters, you can effectively identify customers based on their login activity and account updates. For more advanced customizations, consider exploring the Magento development tutorials available at Laravel Company.

Remember to carefully test your queries before applying them to a production environment to ensure accurate results and optimal performance. Happy coding!

Database Orders Customers Pricing

Our network