function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
mikefitzmikefitz 

SOQL Query Outer Join Contacts and users

I am trying build an outer join to display a list of contacts showing if they have a related portal user records or not.

 

I know you can get a list of related contacts from the user object but is it possible to go from contacts to user? 

 

I know this works...Select u.isactive, u.Contact.name From User u but what about Select name, c.user.isactive From Contact c?

 

Any thoughts how I can outer join these two tables and display one list? 

 

Thanks

 

kyle.tkyle.t

This might be of use to you.

 

http://wiki.developerforce.com/index.php/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com
McFitz13McFitz13

Thanks for the suggestion but I am familar with that.

 

I can't use a nested SOQL statement or traverse across objects because there is no lookup or m/d relationship from the contact to the user object even though the user record has the contactid on it.

 

It would be a simple outer join in SQL. 

 

Additional thoughts?
kyle.tkyle.t

As I review it more, I am seeing what you are saying.   I am not seeing a way to query.  I am thinking you may need to do this programatically.

 

1. Query Contact Table

2. Query User Table

3. Loop through Contacts and see if contact ID exists in User Table.  

 

Certainly not ideal and hopefully somebody can reply with a better solution.

 

 
Pradeep_NavatarPradeep_Navatar

SOQL supports both parent-to-child and child-to-parent query, Since User object is parent of contact object,
You can write query like given below:

list<Contact> con=[Select id, lastName, firstName, OwnerId, Owner.Country, owner.isActive from Contact];
McFitz13McFitz13

Pradeep, thanks for the feedback.

 

I understand what you are saying but I'm trying to get the associated portal user not the owner. The contact owner has a valid relationship that you can traverse. I am trying to get a full list of contacts with a boolean field displaying if they are a portal user or not. Any thoughts?

 

 

Example of my list

 

NAME                          |                 Portal User

John Doe                                             X

Jane Dow                                                 

Barry White

Jerry Maguire                                     X

Billy Tubbs                                         X
forecast_is_cloudyforecast_is_cloudy

You can't go from Contacts to User. You'll need to query the User object and then write some code to determine if a Contact is a portal user.  For e.g.

 

List<Contact> listOfContacts = however you're getting your list of Contacts;

List<User> portalUsers = [select contactId from User where contactId in (select id from Contact where id in :listOfContacts)];

 

Set<Id> contactIds = new Set<Id> ();

for (User  u : portalUsers)

{

   contactIds.add(u.contactId);

}

 

for (Contact c : listOfContacts)

{

 if (contactIds.contains(c.Id);

{

//You know if that this Contact has a Portal User. Perform any neccessary business logic here

}

}

 

 

Hope this helps...
McFitz13McFitz13

Thanks for the feedback.

 

I think you are getting closer to what I  am looking for but the only thing I'm trying to do is to create a list to display in a vf page.

I am redesigning the contact page and trying to display one list of contacts showing if they are a portal user or not instead of two (one for contacts and one for portal users)
dev_witdev_wit
As far as I know, the only way to go from contact to user is to create a custom lookup field and maintain it yourself.  This is difficult.

I think a good way to produce your list is as follows:
  • Generate the list of contacts you want to display.
    • List<Contact> myContactList = [SELECT Name FROM Contact];
  • Build a map<Id, Booelan> of the contacts that do have a portal license
    • Map<Id, Boolean> portalContactMap = new Map<Id, Boolean>();
    • List<Id> portalProfileIds =  new List<Id>{'portalProfileId1', 'portalProfileId2'};
    • List<User> portalUsers = [SELECT ContactId FROM User WHERE ProfileId IN :portalProfileIds];
    • for(User u : portalUsers) {  portalContactMap.put(u.ContactId, true);  }
  • Then in your vf page, loop through the list of contacts you want to display and use the map to determine if they are portal users or not
    • <apex:pageBlockTable value="myContactList" var="mycon">
      • <apex:column value="{!myCon.Name} />
      • <apex:column>
        • <apex:facet name="header">Portal User</apex:facet>
        • <apex:outputText rendered="{! portalContactMap[myCon.id] }" value="X" />
      • </apex:column>
    • </apex:pageBlockTable>
Jeff MikkelsonJeff Mikkelson
// Hear me now and believe me later, this is all you need:
SELECT 
Name, 
IsPortalEnabled, 
IsActive, 
Id, 
CommunityNickname 
FROM User
WHERE contactId IN (SELECT id FROM Contact)
Michael FitzgeraldMichael Fitzgerald
My original request is almost 5 years old but wanted to update for the next person having the same issue.
The only want to do this is using a wrapper class  and presenting it within a visualforce page. 

Here is a basic example. https://developer.salesforce.com/page/Wrapper_Class

Cheers and good luck.
 
Philip FPhilip F
Thanks, @Jeff Mikkelson.  That worked like a charm!
Tad Aalgaard 3Tad Aalgaard 3

Another way of getting this information.

SELECT 
Name, 
IsPortalEnabled, 
IsActive, 
Id, 
CommunityNickname 
FROM User
WHERE contactId != null