function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
AMIT AGRAWAL 5AMIT AGRAWAL 5 

Soql query on User and Login History objects

I need to fetch the user login history based on the user id's I have but I need to fetch only limited no of login history records for each user. I thought of running a parent to child soql for that with limit clause like -

for(User objUser: [SELECT Id, Name, (SELECT Id,Browser,LoginTime FROM LoginHistories ORDER BY LoginTime DESC LIMIT 10) FROM User LIMIT 10]) {
    for(LoginHistory objLoginHistory: objUser.LoginHistories)
        System.debug('LoginHistory:'+ objLoginHistory);
}

This way I can get latest 10 login history records for each user. But Salesforce is not identifying the relationship name 'LoginHistories' here. 
I tried to get the relationship name from the ChildRelationship class using below code but its showing null value for 'Login History' object relationship name.

for(Schema.ChildRelationship cr : user.SObjectType.getDescribe().getChildRelationships()){
    System.debug('child::>>>' + cr.getChildSObject() + ' reversed is: ' + cr.getRelationshipName());
}

Please tell me if that is not possible to run soql from user to login history like this or is there any other way I can get it done ?
Raj VakatiRaj Vakati
Yes .. You canny able to query directly as there is no physical relationship as part of the metadata. You need to fire two different queries  
 
Map<Id,User> users =new Map<Id,User>([Select Id from User Limit 10]) ;
Map<Id,List<LoginHistory >> loginDetals = new Map<Id,List<LoginHistory >>();
for(String uIds : users.keySet()){
    loginDetals.put(uIds , [SELECT Id, UserId, Browser, APIVersion,
                            LoginTime from LoginHistory where UserId=:uIds  ]);
    
}



 
AMIT AGRAWAL 5AMIT AGRAWAL 5
Hi Raj,

Thank for your reply but it will not work.
1. It is having soql inside the for loop which is against the best practices.
2. I have a very large no of records coming from login history for each user. Let's say I am querying on only 5 users but there are 2 users having more than 50K login history records individually. So that hits 50k governer limits for soql queries.

I need to control that not more than 200 Login History records should come for each user in soql query.

Thanks,
Amit
 
Raj VakatiRaj Vakati
1. It is having soql inside the for loop which is against the best practices.
-- NOO 
2. I have a very large no of records coming from login history for each user. Let's say I am querying on only 5 users but there are 2 users having more than 50K login history records individually. So that hits 50k governer limits for soql queries.
 -- You will hit the limits But you could able to use the batch apex with low size batch 
AMIT AGRAWAL 5AMIT AGRAWAL 5
I am facing this issue inside a batch class only where start method query is on users and then I need to process login history records for those users inside the execute method. The issue is this batch is failing even for batch size of 10 because of large no of login history records related to each user. 

Alternative approach that is coming to me is to call another batch from this batch and run the main query in that batch on login history object records to do further processing but I needed to know if only one batch can do the magic.
Maharajan CMaharajan C
Hi Amit,

To Avoid the Soql inside the for loop you can use the below code:

Map<Id,User> users =new Map<Id,User>([Select Id from User Limit 10]) ;
Map<Id,List<LoginHistory >> loginDetals = new Map<Id,List<LoginHistory >>();
List<LoginHistory> LHList = [SELECT Id, UserId, Browser, APIVersion,
                            LoginTime from LoginHistory where UserId=:uIds Limit 50000 ];
for(LoginHistory lh:LHList)
{
    If(!loginDetals.ContainsKey(lh.UserId))
    {
        loginDetals.put(lh.UserId,new List<LoginHistory>{lh});
    }
    else
    {
        loginDetals.get(lh.UserId).add(lh);
    }
}

But if you have login histories more than 50000 records then it will hit the limit to avoid that you have to use the batch class or narrow down the soql query based the some limits statements in the SOQL ex: get the login history for last 3 months. 

In the Batch class set the batch size as 1 while run.


Thanks,
​Raj
AMIT AGRAWAL 5AMIT AGRAWAL 5
Hi Raj,

Reducing the batch size to 1 will increase batch processing time to a large extent which will not be a good option but I will try to reduce query result by using the filter for number of days.

Thanks