AbleCommerce Gold How To Clean Up Anonymous Users

by Joe Payne 14. April 2014 15:45

Even though Able Gold has a manual cleanup option in the Maintenance page, it doesn’t always work well.  The problem arises from how Able must delete each user individually.  Not such a big deal when you have 500 users to clear out.  

It’s a very different story when you have 2,000,000 unwanted users.   A SQL query can delete all of the unwanted records in a single command.

Below is the updated query to work with Able Gold schema.  Obviously change the dates to something more recent. 

If the queries do not remove as many records as you expected, you might want to remove the “AND (AffiliateId IS NOT NULL)” criteria if you don’t care about affiliate reporting.

use <yourdbname>
DELETE FROM ac_Baskets 
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009'))
 
DELETE FROM ac_Wishlists
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009'))
 
DELETE FROM ac_Users 
WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009')

Tags: , , ,

AbleCommerce Gold | Tech Support

NHibernate sub query on Orders filtering by usergroup

by Joe Payne 17. Maart 2014 09:31

A client had a need to filter the Monthly Sales Summary report by group membership.  In other words, restrict the totals to orders placed by users only in a specific user group.

The initial NHibernate query is such:

ICriteria criteria = NHibernateHelper.CreateCriteria<CommerceBuilder.Orders.Order>("O")
    .CreateCriteria("Items", "OI", NHibernate.SqlCommand.JoinType.InnerJoin)
    .Add(Restrictions.Eq("O.Store", AbleContext.Current.Store));

 

So I added a dropdown that is populated by GroupDataSource.LoadAll().  Then it seemed easy enough to just add this code which I found in the UserSearchCriteria.cs file:

if (groupId >= 0)
{
    criteria.CreateCriteria("O.User.UserGroups", "UG", NHibernate.SqlCommand.JoinType.InnerJoin)
    .Add(Restrictions.Eq("UG.Id.Group.Id", this.GroupId));
}

The problem is, this code throws a big ol’ NHibernate error “nhibernate multi-part identifier <someobject> could not be bound”

For some reason, NHibernate couldn’t resolve the relationship of Order –> User –> UserGroups –> Group.   The code I swiped from UserSearchCriteria had no problem with it.  But here it just wouldn’t work.

I finally figured out the solution was to create a new reference to the User table and base the criteria from there.   So instead of starting at the Order object level, the join and restriction starts as the User object level which sort of makes sense now that I’m typing this…

if (groupId >= 0)
{
    criteria.CreateCriteria("O.User", "U", NHibernate.SqlCommand.JoinType.InnerJoin)
        .CreateCriteria("U.UserGroups", "UG")
        .Add(Restrictions.Eq("UG.Id.Group.Id", groupId));
}

Finally, the end result is the NHibernate query now properly searches the orders for only those records where the user that placed the order is a member of a specific GroupId.

Tags: , , , ,

AbleCommerce Gold | Personal

AbleCommerce 7 How to Clean up anonymous users table

by Joe Payne 23. Julie 2010 14:59

Needed to find this for a client today, so I thought I would post it to the blog and (hopefully) make it easier to find the next time.

With older versions of AC7 i.e. 7.0.3 and earlier, there sometimes is an issue where user maintenance does not run completely through.  As days go on, this problem becomes more pronounced as the ac_Users table continues to grow.  At some point, you'll start noticing slow site performance and degradation.  Check your ac_Users table and you'll probably find hundreds of thousands of old user records.

This query will clean those out in one quick swoop.  After that, the user maintenance routine should be able to keep up.  Just change the date in the three query statements for the cutoff date you want.  Also make sure to set the database name for your particular installation at the beginning of the file.

USE <YourDBName>
DELETE FROM ac_Baskets
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NOT NULL OR ReferringAffiliateId IS NOT NULL)
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009'))

DELETE FROM ac_Wishlists
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NOT NULL OR ReferringAffiliateId IS NOT NULL)
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009'))

DELETE FROM ac_Users
WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NOT NULL OR ReferringAffiliateId IS NOT NULL)
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009')

Tags: , ,

AC7 Articles

Month List