SOQL error with relationship

Question

I have one query as below:

select id,functional_system_user__c,
       Test_Created_Date__c,
       Test_Login_Date__c,lastmodifieddate,name,
       isactive,Reactivation_Date__c,email,isportalenabled 
  from user where id='ABC'

Another query is :

select id,isfrozen,userid from userlogin where userid ='ABC'

I wanted isfrozen field to be included in 1st SOQL.I have written below query and i am getting error saying invalid relationship.

select id, functional_system_user__c, Test_Created_Date__c,  
       Test_Login_Date__c, isactive, Reactivation_Date__c, email,  
       isportalenabled,
       (select isfrozen from userlogin ) 
  from user where id='ABC'

Id of 1st SOQL is equal to userid of 2nd SOQL.

Answer 1

It does not seem possible to subquery this child relationship. I ran the following:

for (ChildRelationship relationship : SObjectType.User.getChildRelationships())
{
    if (relationship.getChildSObject() == UserLogin.sObjectType)
    {
        system.debug(relationship.getField() + ' - ' + relationship.getRelationshipName());
    }
}

The results I got were:

USER_DEBUG [5]|DEBUG|LastModifiedById - null
USER_DEBUG [5]|DEBUG|UserId - null

This means that while the relationship is there, it does not have a name you can use.

Contrast this with, for example, an attempt to subquery AccountTeamMember records. If I change the SObjectType I am looking for above, I get

USER_DEBUG [5]|DEBUG|CreatedById - null
USER_DEBUG [5]|DEBUG|LastModifiedById - null
USER_DEBUG [5]|DEBUG|UserId - AccountTeams

So I cannot subquery AccountTeamMember records based on LastModifiedById from a User query, but I can merge in the relationship name to look at any matches on UserId:

SELECT (SELECT Account.Name FROM AccountTeams) FROM User