Manage Subscriptions - Distinct User

[mbartens]

I'd like the results on this page to show distinct users with a link to the latest expiring subscription
I can achieve that in SQL using this:

Code: Select all

SELECT *
FROM ac_Subscriptions A
WHERE EXISTS (SELECT B.UserId
         FROM ac_Subscriptions B
        WHERE B.UserId = A.UserId
          AND B.ExpirationDate < A.ExpirationDate)
       
       OR (SELECT COUNT(*) FROM ac_Subscriptions C
       WHERE C.UserId = A.UserId) = 1

How can I do that with the SubscriptionRepository? I'm looking at SubscriptionRepository, BuildSearchCriteria

TIA!

[jmestep]

There is code in the ConLib/MoreCategoryItems.ascx.cs that you can use as a pattern for one approach. It's many lines, but you can see the pattern. You would want to use your sql then return a list type of <User>. Here is the end of the query:

Code: Select all

 ISQLQuery query = NHibernateHelper.CreateSQLQuery(sqlquery);                    
                    query.SetParameter("categoryId", _CategoryId);
                    query.SetParameter("nodeType", CatalogNodeType.Product);
                    query.SetParameter("minOrderBy", minOrderBy);
                    query.SetParameter("maxOrderBy", maxOrderBy);                    
                    query.SetParameter("visibilityId", (byte)CatalogVisibility.Public);
                    query.AddEntity(typeof(CatalogNode));
                    productNodes = query.List<CatalogNode>();

Your code would add
IList<User> users= new List<User>();
instead of
IList<CatalogNode> productNodes = new List<CatalogNode>();
end up using query.AddEntity(typeof(User));
users = query.List<User>();

(I haven't tested this)

[mbartens]

Thank you. I was really hoping to just modify what was existing (make my own copy) in the SubscriptionRepository.BuildSearchCriteria
The page I'm looking at that outputs the data (Admin/People/Subscriptions/Default.aspx) uses and AbleGridView and a DataSource so I would really have to modify how that page works which I don't want to do.
I'm new to NHibernate so that make it a little harder for me. Isn't there a way that I can modify the Subscription criteria to do this?