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];

1 Answer 1

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.

6
  • 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, 2014 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, 2014 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]; Oct 27, 2014 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, 2014 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. Oct 27, 2014 at 11:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .