2

trying to get field with the earliest date from custom object. Here is my current query

BomaGroups =    [SELECT Name, Location__c FROM Group__c WHERE Location__c =:id]; // Selects the Group I want from Location Master object
MeetingsList2 = [SELECT Name, Group__c, Business_Value__c, Meeting_Date__c, GPS_Meeting_Location__Latitude__s, GPS_Meeting_Location__Longitude__s, 
Group__r.Name FROM Meeting__c WHERE Group__c =:BomaGroups 
ORDER BY Meeting_Date__c DESC LIMIT 1]; // Selects Meetings I want with the Groups SOQL  as the where clause

I have 3 custom objects, Location(master), Groups(child), meeting(grand child). The above query works fine for getting 1 record but its supposed to return only the earliest date values for all the records matching the where clause. I was thinking of inserting an SOQL statement in the where clause for minimum date, any clues?

Tried the following subquery to combine the 2 statements, generated Error: Didn't understand relationship 'Group__r'

BomaGroupsList = [select Name, Location__c, (SELECT Name, Group__c, Business_Value__c,       Meeting_Date__c, GPS_Meeting_Location__Latitude__s,GPS_Meeting_Location__Longitude__s FROM Group__r ORDER BY Meeting_Date__c DESC LIMIT 1) FROM Group__c WHERE Location__c =:id];
3

Try this:

list<Group> groups = [select id, name, (SELECT Name, Group__c, Business_Value__c, Meeting_Date__c, GPS_Meeting_Location__Latitude__s, GPS_Meeting_Location__Longitude__s FROM Meetings__r ORDER BY Meeting_Date__c DESC LIMIT 1
 ) from Group where Id =:BomaGroups];

Your limit 1 in the original query applied to the whole of the result set and therefore only returned 1 record. By using a sub-query you can get the records that relate to the groupIds you need. I have guessed at the lookup relationship api name being Meetings__r - this may need adjusting.

  • Hi, thanks for the idea, will try use a sub query. Your answer looks at 2 related objects only and I have 3 custom objects, Location(master), Groups(child), meeting(grand child). Sorry I didn't put it that way, Let me edit my question to reflect that. – Rufus K. Oct 27 '14 at 10:22
  • Just tried this but not working BomaGroupsList = [select Name, Location__c, (SELECT Name, Group__c, Business_Value__c, Meeting_Date__c, GPS_Meeting_Location__Latitude__s, GPS_Meeting_Location__Longitude__s FROM Group__r ORDER BY Meeting_Date__c DESC LIMIT 1) FROM Group__c WHERE Location__c =:id]; – Rufus K. Oct 27 '14 at 10:47
  • An idea of the error would be useful... what about this: BomaGroupsList = [select Name, Location__c, (SELECT Name, Group__c, Business_Value__c, Meeting_Date__c, GPS_Meeting_Location__Latitude__s, GPS_Meeting_Location__Longitude__s FROM Groups__r ORDER BY Meeting_Date__c DESC LIMIT 1) FROM Group__c WHERE Location__c =:id]; – Richard Durrant Oct 27 '14 at 10:56
  • Both mine and your new SOQl query are generating the same error Compile Error: Didn't understand relationship 'Group__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. – Rufus K. Oct 27 '14 at 10:58
  • Thats strange, I'm sure I have groups__r in mine. Anyway, have a look in the UI and see what the relationship API name is and use that in your query. – Richard Durrant Oct 27 '14 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.