How to pull out the maximum time values ​​for each user in a query?

Question

MySQL

Good afternoon, there are 2 tables - orders and customers, I need to pull out customers who have not bought anything for 30 days. You can, of course, take it easy by collecting data in arrays and running in a loop, but the solution with the request is interesting:

$is_lie_customer = Yii::app()->db->createCommand()
            ->select('m_users.CODE, m_users.NAME')
            ->from('m_users')
            ->join('m_orders', 'm_users.CODE = m_orders.CUSTOMER')
            //->where('m_orders.SYS_DATE <= :time', array(':time'=> (time() - (60 * 60 * 24 * 30))))
            ->where('MAX(m_orders.SYS_DATE) <= '.(time() - (60 * 60 * 24 * 30)))
            ->queryRow();

In the query, the problem with WHERE swears at MAX .

SELECT `m_users`.`CODE`, `m_users`.`NAME`
FROM `m_users`
JOIN `m_orders` ON m_users.CODE = m_orders.CUSTOMER
WHERE MAX(m_orders.SYS_DATE) <= 1435638477

How can this be played with mysql ?

Answer 1

learning mysql: aggregate functions cannot be used in a WHERE clause.
https://dev.mysql.com/doc/refman/5.5/en/group-by-f...

Answer 2

SELECT `m_users`.`CODE`, `m_users`.`NAME`
FROM `m_users`
JOIN `m_orders` ON m_users.CODE = m_orders.CUSTOMER
WHERE NOT EXISTS m_orders.SYS_DATE >= NOW() - INTERVAL 1 MONHT

Pull out all users who do not have a date greater than a month ago. (i.e. if they didn't shop.)