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
NatashaNatasha 

How do we do JOINS on 2 tables through SF API?

Hi All,
I am looking for some means of running 2 queries but the results of one should be depenedent on the results of other - so basically a JOIN.
 in usual SQL I am looking for results as would be returned by:

select C.id, C.accountid, C.last name, A.AccountName
from Contact C
JOIN Account A
ON C.accountid=A.id
WHERE A.Website='abcd'

Could someone please help me with achieving equivalent results through the SF API calls? What is the best way to achieve this?
Looking forward to some early responses...
Natasha

DevAngelDevAngel

Hi Natasha,

This is not that simple.  The API does not support joins currently (there is no structure defined in the wsdl that can hold a few fields from one object and a few fields from another).

To do joins you will have to join them at the client.  The pattern is to query the object that is used in the where clause, from your example it would be the account using the website field.  You would then query the Contact object using the Account Ids like select fields from contact where accountid = id1 or accountid = id2, or accountid = id(n).

This is pretty simple although a little clumsy.  The hard part comes in tying these two results together such that you have a hybrid data row that contains the data from both objects.  One method of doing this would be to push the accounts into a data table, push the contacts into another data table and put both tables into a dataset.  You can then create a relationship on the dataset using the Id of the account for the primary key and the accountid on the contact for a foriegn key.  Then using ado, you would be able to access the master and detail records properly related.

 

NatashaNatasha

I feared this too Dave...thanks for your explanation though.
This brings me to another question then - so for example, I queried contacts first and got back say 10K contact records. Now to get the AccountName or any other fields of Account for each contact, I will have to query the SF with query like "select fields from account where id=accountid1"...etc 10K times?? Is there a better way that this can be done more efficiently?
Natasha

DevAngelDevAngel

Hi Natasha,

Well, yes and no.  Yes because you can query using a really long or (although probably not 10k or-s),

id = accountid1 or id = accountid2 or etc.

No, because the difference between what you asked and what I suggest is just the number of records to be returned.  IE, you have to do this query on accountid, it's just a matter of how many times.

ScotScot

Another alternative, though not as cleanly API-based, is to extract the information using a report to perform the join.  Unlike the API, the reports can handle selected joins - including contacts with accounts. The downside, of course, is that reports cannot be created via the API.

I build the reports in the UI, and then use Excel to run them under the control of VBA, using the Office Edition refresh functions. 

NatashaNatasha

I tried a query with approximately 1K OR conditions in it and I got the Error from SF saying "UNKNOWN_EXCEPTIOn - too many filters or filter criteria is too long". I am limiting to NOT give more than 2K OR conditions....but it is not even handing 1K. Could you please let me know the limit on this?
I really need to get this done asap and this is what is holding me up - I will need to change my design completely if this thing does not work or has a very low OR condition limit......

Just a sample query I am using..
select id, accountid, email from Contact where accountid = '00100000002wLoYAAU' or accountid = '00100000002w9aYAAQ' or accountid = '00100000002w80tAAA' or accountid = '00100000002waIdAAI' or accountid = '00100000002wbjSAAQ' or ..........

Looking forward to some early reply...
Thanks
Natasha

DevAngelDevAngel
Hi Natasha,

That's not the error I would expect. I tested your statement and results in an error of

MALFORMED_QUERY: SOQL statements can not be longer than 10000 characters.

1000 ids results in about 31058 characters. (AccountId='00130000000ZdoW' or AccountId='00130000000ZdoW' or ....)

With your select statement I found that I could include 320 or'd ids in the where clause. Beyond that, I would get the exception above.

Try reducing the overall soql statement to 10k characters or less.
gudduguddu

hello ,

can anyone tell me please how to push the results of the query "select name from account " into datatable in c# ,asp.net ?

I have to display the results in datagrid .

 

Thanks,

Guddu

 

 

 

DevAngelDevAngel

Code:

//Ok, here is a new algorithm.  Basically, we want to take a queryresult and create a datatable
//from it.  .
public static DataTable QueryToDataTable(DataSet ds, sforce.QueryResult qr) 
{
 if (qr.size == 0) return null;
 //Check to see if a table exists for this sobject type
 String tableName = qr.records[0].type;
 if (!ds.Tables.Contains(tableName)) 
 {
  ds.Tables.Add(new DataTable(tableName));
  ds.Tables[tableName].ExtendedProperties.Add("sforceType", qr.records[0].type);
 }
 DataTable thisTable = ds.Tables[tableName];
 System.Diagnostics.Trace.WriteLine("New query process...");
 for (int i=0;i<qr.records.Length;i++) 
 {
  System.Diagnostics.Trace.WriteLine("   Process record: " + i);
  sforce.sObject obj = qr.records[i];
  DataRow thisRow = AddSObjectToTable(ds, thisTable, obj);
 } //End of qr.records iteration
 return thisTable;
}


 

Code:

public static DataRow AddSObjectToTable(DataSet ds, DataTable thisTable, sforce.sObject obj) 
{
 DataRow thisRow = thisTable.NewRow();
 if (obj.Any == null) return null;
 for (int j=0;j<obj.Any.Length;j++)
 {
  XmlElement currEl = obj.Any[j];
  AddFieldValue(thisTable, currEl, thisRow);
 }  //End of any iteration
 thisTable.Rows.Add(thisRow);
 return thisRow;
}


 

Code:

private static void AddFieldValue(DataTable table, XmlElement element, DataRow row) 
{
 if (!table.Columns.Contains(element.LocalName)) 
 {
  table.Columns.Add(CreateDataColumn(element.LocalName));
 }
 row[element.LocalName] = element.InnerText;
}


 
Code:

private static DataColumn CreateDataColumn(string fieldName) 
{
 DataColumn dc = new System.Data.DataColumn(fieldName);
 return dc;
}


 

This is a modified version of the sforce explorer code, readily available from the sforce explorer source forge project.