GroupBy in CustomTableItemProvider.GetItems

Tony Feltham asked on March 16, 2018 10:46

I am trying to reproduce the following SQL Command and result set in in Code....

SELECT EventNotes AS IPAddress, COUNT(ItemID) AS LoginCount FROM customtable_FraudAlertLog WHERE UserID = 53 AND EventType='User Login' AND ItemCreatedWhen > '2018-03-15' GROUP BY EventNotes

IPAddress - LoginCount

127.0.0.1 - 3

192.168.0.1 - 4

86.123.23.10 - 6

The following code runs great and returns 3 rows as it should but I cannot get access to the returned values as it always returns the default 0 in the GetValue and GetIntegerValue

private int GetLoginDetails(int notificationInterval, CurrentUserInfo userDetails)
{
    int IPAddressCount = 0;
    string customTableClassName = "CustomTable.FraudAlertLog";
    DataClassInfo customTable = DataClassInfoProvider.GetDataClassInfo(customTableClassName);

    if (customTable != null)
    {
        // Check to see if an alert of the specified type, relating to the user, has sent in the last "notificationInterval" minutes
        var logItems = CustomTableItemProvider.GetItems(customTableClassName)
                                                            .Columns("EventNotes AS IPAddress, COUNT(ItemID) AS LoginCount")
                                                            .WhereGreaterThan("ItemCreatedWhen", DateTime.Now.AddMinutes(-System.Math.Abs(notificationInterval)))
                                                            .WhereEquals("EventType", "User Login")
                                                            .WhereEquals("UserID", 53)
                                                            .GroupBy("EventNotes");
        if (logItems != null)
        {
            IPAddressCount = logItems.Count;
            foreach (var logItem in logItems)
            {
                var IPAddress = logItem.GetValue("IPAddress", 0);
                var LoginCount = logItem.GetIntegerValue("LoginCount", 0);
            }
        }
    }
    return IPAddressCount;
}

Any Ideas please?

Thank you

getitems groupby

Correct Answer

Peter Mogilnitski answered on March 16, 2018 13:58

I would recommend just define a custom query on your custom table (see example) and run it using query api, or do it directly

DataSet ds = ConnectionHelper.ExecuteQuery("SELECT EventNotes AS IPAddress, COUNT(ItemID) AS LoginCount FROM customtable_FraudAlertLog WHERE UserID = 53 AND EventType='User Login' AND ItemCreatedWhen > '2018-03-15' GROUP BY EventNotes", null, QueryTypeEnum.SQLQuery);

1 votesVote for this answer Unmark Correct answer

Recent Answers

Trevor Fayas answered on March 16, 2018 13:40

When you tell it to return it as a get items(custom table classname) it may be parsing only those fields, I would first inspect the results to see if the data row of it has the right thing, otherwise may want to use a custom query and use the connection helper to do what you want and just get a data set back.

0 votesVote for this answer Mark as a Correct answer