How to find non-active users in AppGini

I just got an interesting question from an AppGini user:

Is there an SQL query that I can run manually in PhpMyAdmin that will show me a list of all users that haven’t logged into AppGini for the last 6 months please? Even if it is using one of the date fields in the membership_users table to show me a list of all users that haven’t added or changed a record for the last 6 months, that would suffice.

Well, AppGini doesn’t currently (until AppGini 5.72) store the login date/time of users (but we plan to add this in future releases). So, what we can do is run a query to retrieve non-active users (with no insert/edit activity in the last 6 months).

First, we need to create a new view in MySQL to list users’ last activity:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE VIEW members_activity AS 
select 
    u.memberID as 'memberID', 
    u.signupDate, 
    max(FROM_UNIXTIME(r.dateAdded)) as 'last_insert', 
    max(FROM_UNIXTIME(r.dateUpdated)) as 'last_update'
from 
    membership_users u left join 
    membership_userrecords r on u.memberID=r.memberID
group by u.memberID

Now, you can use the view members_activity that we defined above like you use any table (except it’s not actually a table, but it makes our life easier when performing complex queries). So, to retrieve all users who have no insert/update activity or whose last insert/update activity is more than 6 months old:

1
2
3
4
5
6

select * from members_activity where
	(isnull(last_insert) and isnull(last_update)) or
	(
		last_insert < now() - interval 6 month and
		last_update < now() - interval 6 month
	)

But the above query might retrieve new users who joined less than 6 months ago … to exclude those, we should add another condition:

1
2
3
4
5
6
7
8

select * from members_activity where
	signupDate < now() - interval 6 month and (
		(isnull(last_insert) and isnull(last_update)) or
		(
			last_insert < now() - interval 6 month and
			last_update < now() - interval 6 month
		)
	)