Sign Up ›

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

soql query when using a junction, SOLVED!

I am trying to collect all the data related to a custom object in a  single soql query (I am making the query call from the other side of a remote connection to my org, and only want to make one data transfer)

 

I have two custom objects  (comments__c and attachments) with master/detail look relationships to the source object(problem__c), and the query works fine

 

select id,priority__c,severity__c,summary__c,description__c,status__c,howtoreproduce__c,circumvention__c,impact__c,defectsystemnumber__c,(select name from attachments),(select name, body__c, created__c from comments__r) from problem__c where id='hhgkjhlkhhk'

 

I also have a junction object (map__c) which connects case to my problem__c object.

map__c : {name, id, {case__c: lookup case}, {prob__c:lookup problem__c}}

 

so I want to get fields out of the case record, linked thru the junction off this problem.

 

as a test , I 'should' be able to pull back the name field from the junction object using the normal syntax

 

select id, name, (select name from map__r) from problem__c where id='llllllll'

 

but I get a relationship error.."didn't understand the relationship map__r"

but this is the same syntax I used above for the other related objects..

 

once solved, I actually want to look thru the case lookup on the map to get case fields

 

select id, name (select case.number from case__r.map__r) from problem__c where id='kjkkjjlk'

 

(there could be more than one case related to this problem,so I need to get info from all of them)

(<- means pointing back to)

problem   list of comments (<- problem)

                  list of attachments (<- problem)

                  list of cases (<-problem thru junction object 'map')

                      (a case might be related to multiple problems, thus the need for the many to many junction)

 

thanks for any guidance.

 

Best Answer chosen by Admin (Salesforce Developers) 
pmarszalekpmarszalek

And what can you see when you go to setup -> Create -> Objects, select Map object, then click on prob__c field? What is the value of Child Relationship Name field there?

All Answers

sammydsammyd

this subquery gets me info from the case record, thru the junction

 

select  case__r.id , case__r.casenumber  from map__c where prob__r.id='a0O30000002K79XEAS'
pmarszalekpmarszalek

Hi,

 

I think that the problem is that you don't name properly your child relationship in query.

Maybe try this:

select id, name, (select name from maps__r) from problem__c where id='llllllll'

 

If you want to use a relationship from parent to child, you have to call child relationship. By default it's name is a plural label, so for map__c it should be maps__r.

It is a plural label because it retrives a list of objects.

 

It is also possible that you have changed your child relationship name. Go to setup -> Create -> Objects, select your Map object and than click on your relationship.

You will see there a field called Child relationship name. Use this name in query with __r appendix.
sammydsammyd

thanks.. thats 1/2 the way there

 

select  name, id, (select name, case__r.casenumber from problems__r) from problem__c where id='a0O30000002K79XEAS'

 

now gives me the fields from the junction, using the relationship name.. now I need to get fields THRU the other side of the junction.

 

the above select succeeds, and I get the 'name' of the junction object.. but I get nothing from the case__r.... fields

if I use cases__r I get a syntax error as expected.

 

I have 3 cases associated with this problem.  in the output above I get three entries, but only 1 field each,

shown as 'maps' objects.. I need the cases objects on the other side. this data shows correctly in the Problems UI as data from the related list object.

 

so, in text form,

 

select the casenumber field from the cases related to this problem.

(relationship built thru the map_c object)

the relationship names of the two sides of the map object are

Cases and Problems, each is a master/detail lookup on their associated objects, Case and Problem__c respectively.

 

I 'think' the map.case relationship is child to parent,  (it would be parent to child if I were starting from case)

 

 
pmarszalekpmarszalek

So it would be:

 

Select name, id, (select case__r.casenumber from maps__r) from problem__c where id='a0O30000002K79XEAS'

 
sammydsammyd

thanks.. I get an error on that..(which is what started the thread topic)

 

I cut/pasted from your post

 

error:

didn't understand the realtionship 'maps__r' in FROM part of query call

 

 

------

 

this works (look thru the junction)

 

select case__r.casenumber from map__c

 

so, adding the wrapper, and changing the reference should work

 

select id, name, (select case__r.casenumber from maps__r) from problem__c where id='a0O30000002K79XEAS'

 

but get the syntax error on maps__r again.

 

also, this works case__r.owner from map__c

where this fails (no owner field on Case)??

select case__r.casenumber , case__r.priority, case__r.owner from map__c
pmarszalekpmarszalek

And what can you see when you go to setup -> Create -> Objects, select Map object, then click on prob__c field? What is the value of Child Relationship Name field there?

This was selected as the best answer
sammydsammyd

as posted before

 

the child relationship names of the two fields are

 

case => cases

problem => problems

 

map is the custom junction, and its plural is maps

===========

 

update

and maybe this is backwards..

 

so, switching the child names to

case => problems

problem => cases

 

and using this select

 

select name, ( select case__r.casenumber , case__r.priority from cases__r) from problem__c where id='a0O30000002K79XEAS'

 

I don't get any error, and I get 3 case records for this problem,  but there is no data in the case records

sammydsammyd

a prior set of code thru the juntion which works, looks like this

 

select id,casenumber,  product.name, account.name  from case where id in (select case__c from map__c where problem__r.id='a0O30000002K79XEAS') and isDeleted=false
pmarszalekpmarszalek

I see... I have to say that you've got some misleading child relationship names...

 

Anyway, if this work:

select  name, id, (select name, case__r.casenumber from problems__r) from problem__c where id='a0O30000002K79XEAS'

 

Use it in apex like this:

List<problem__c> problems = new List<problem__c>();
problems = [select  name, id, (select name, case__r.casenumber from problems__r) from problem__c where id='a0O30000002K79XEAS'];

 

List<String> casenumbers = new List<String>();

 

if(!problems.isempty())
for(map__c map_var: problems[0].problems__r)
casenumbers.add(map_var.case__r.casenumber);

 

System.debug(casenumbers);

 

Must work!
sammydsammyd

thanks.. didn't work..

 

just for discussion I changed the child names to

 

problem==> relatedcases

case==>       relatedproblems

 

so, changing your sample

select  name, id, (select name, case__r.casenumber from relatedproblem__r) from problem__c where id='a0O30000002K79XEAS'

fails "didn't understand relationship relatedproblem__r"

 

we are starting with problem, so referencing it doesn't get to the case side of the junction.

 

select  name, id, (select name, case__r.casenumber from relatedcases__r) from problem__c where id='a0O30000002K79XEAS'

 

this yields no syntax error, and returned 'name' is the builtin required field value of the junction object itself.

case__r should be the singular of the from, but I get no data in the query results for this column.

if I add more fields from Case, I get no error, but I get no data.

If I misspell a field name, I get the 'no such column xxx on entity Case' error as expected.

 

so, I get the right count, but not the data thru the child relationship.

 

so, here is my desired total constructed select using the new childname

 

 select id, name, (select name from attachments), (select body__c, name, created__c from comments__r), (select name, case__r.casenumber from relatedcases__r )  from problem__c   where id='a0O30000002K79XEAS'

 

get some data from the specific problem

    and some data from the related attachments

    and some data from the related comments

    and some data from the related cases

 

this causes no error, there are 3 records returned for the relatedcases(as expected). but the casenumber field is not present in these records.

 

this code is running in Java in a remote system connected over a partner wsdl connection.

 

the Eclipse schema soql test window produces the same results.as shown here

 
pmarszalekpmarszalek
Seems like Eclipse soql schema is tricking you :) I've got the same data model as you described (except attachments and comments) Here is what Eclipse displays: It is not good idea to use Eclipse for that. Use Force.com Explorer: http://wiki.developerforce.com/page/ForceExplorer Results are here:
sammydsammyd

thanks.. VERY much..  I put the soql into my java app and it WORKED!!.. I got the data I wanted..

 

I installed the Explorer, but can't login for some reason.(invalid userid, password, token or user locked out'.. well, I'm logged in on the web, and the java code logs in with the same userid/pw/token strings..

 

what is the 'client id' field in the login advanced parms? token?

 

Sam
pmarszalekpmarszalek
I don't know what is client id, but i think that your problem is with password field. login: your login password: yourpassword concatenated with security token Example login: Sam@sam.eu password: samspassword token:1234 To connect with Force.com Explorer: login: Sam@sam.eu password: samspassword1234
sammydsammyd

yep, thanks again.. I fall in this ditch every time!..  wish they would add a field to hold the token so that its 'obvious'!..

 

thank you very much for your time.. all this because the test UI is broken..

 

Sam