+ Start a Discussion
VarunCVarunC 

I'm going crazy over an SOQL Query .. :(

Here is my SOQL query which is Not returning Records At all for SubQuery items (Matters).

Contact[] contacts = [Select Debits__c, Credits__c, Name,
(Select Id, Actual_Expenses__c, Contact__c, Name From Matters__r Where ID IN : mat_ids)
From Contact
Where ID IN : con_ids];
Matter__c[] ms = [Select Id, Actual_Expenses__c, Contact__c, Name From Matter__c Where ID IN : mat_ids];

 

con_ids & mat_ids are SET<ID> variable instances. And they Both hold a value each. I can see the values in debug.

 

I have 1 Contact record with 1 Matter (a Custom Object with a Lookup field Contact). And to be sure of permisisons to my understanding are all set correct Since, Owner of Contact record and Contact's Account Owner and Matter Records Owner are All Same User.

Now, in above queries:

for (Contact c : contacts) {
... c = Contact Record values are returned here.
... c.Matters__r = EMPTY. No record is returned here.
}

...
...
...

ms = Returns the Record correctly.

 

Why the record is not returned in first query ... but is returned in second query .. Anyopne got any idea of this ... ? plz ...

 

 

Both queries are Executed in a Trigger.

 

 

Record values are these:

 

mat_ids : (a008000000DRR5LAAX)

con_ids : (0038000000j0HUJAA2)

ms : (Matter__c:{Name=XXXXX, Contact__c=0038000000j0HUJAA2, Id=a008000000DRR5LAAX})

c : (Contact:{Name=YYYYY, Credits__c=0.00, Id=0038000000j0HUJAA2, Debits__c=0.00})

c.Matters__r.size() : 0

Message Edited by vchaddha on 09-04-2009 11:27 AM
VarunCVarunC
Anybody .. holding any clue here ... ? plz .. :(
VarunCVarunC

Ok here is what I could refine the issue Statement to ... :

 

When I view Data in Salesforce using Browser, with ANY User Login, I cna view All Matters for the Contact records, but when I Use Eclipse or SOQL to view All Matters for the Contact using Contact object with Matter Child Relationship object I get No Records.

 

Can anyone Point me to any direction where I can resolve this issue ... ? Is this somekind of Security issue ... but I've not done anything concering Security changes in my ORG .. it was working beautifully earlier but its broken now ... :(

k2sfdevk2sfdev
Contact[] contacts = [Select Debits__c, Credits__c, Name,
(Select Id, Actual_Expenses__c, Contact__c, Name From Matters__c Where ID IN : mat_ids)
From Contact
Where ID IN : con_ids];
Nick1746323Nick1746323

Try this

 

Contact[] contacts = [Select Debits__c, Credits__c, Name, 
(Select Id, Actual_Expenses__c, Contact__c, Name From Matters__r)
From Contact
Where ID IN : con_ids];

 You don't need the where clause in the "subquery". It will just come back with related Matters for each contact.

VarunCVarunC

@k2sfdev:

I don't think we can Use "__c" in ChildRelatiop Collection ... as Collections are List of Records.

 

@Nick1746323:

Yes I agree, but with the Data i've this Query was returning Results earlier .. but is failing now.

 

Well for an update on my issue I figured out that this is some I must say a BUG in Salesforce in Naming ChildRelationships, see in my Org I few days ago created a Lookup relationship with a Name which was already being used  by another Lookup field for Same Object.

 

See the scenerio now is:

Objects:

1. Contacts

2. Matters

 

I have 2 Fields in Matters object, namely, "Contact" & "Opp. Contact", later field was created few days back.

 

What happened here was accidently without knowing what effect it would have I named ChildRelationship name "Matters" for second Field too :(. So now if I use an SOQL on Contacts to read All Matters & Opp. Contacts I got a query like this will Not WOrk anymore:

 

Select Id, Name

    (Select Id, Actual_Expenses__c, Name From Matters__r), //For "Contact__c" Lookup field on Matter Object

    (Select Id, Name From Matters__r) //For "Opp_Contact__c" Lookup field on Matter Object

From Contact

Order By Name

 

 

Since Both have Same ChildRelationship name so I Have to refer to them using Same Relationships... Eclipse Object Browser DOES not Execute this Query and throws Malformed Query Error.

 

And I'm referring this as a Bug becoz when Creating Fields SF did not have any validation to put on duplicate relationship name ...  if they knew this will not work with duplicate relationship names.