在salesforce中使用SOQL查询子记录和父记录

1、在子记录中查询父记录

实例1:

Project__c pro = [SELECT Id, Name, Account__r.Name, P_Status__c, P_Plan_Complete_Date__c, P_Actual_Complete_Date__c 
    					  FROM Project__c 
    					  WHERE P_Status__c='Finishing' 
                                          LIMIT 1];

其中Account为自定义的关系字段,Project为自定义的对象

实例2:

Opportunity opp = [select Name, Account.Name from Opportunity limit 1];

2、在父记录中查询相关的子记录

标准对象:

Account acc = [select name, (select name from opportunities) from account limit 1];
system.debug('account name:' + acc.name);
List opp = acc.opportunities;
system.debug('number of opportunities:' + opp.size());
for (opportunity op : opp) {
    system.debug('opportunity name:' + op.name);
}

自定义对象:ACC_Promotion__c(父) - ACC_Promotion_Detail__c(子),关系字段Name:Promotion_Details(关系字段名在子对象ACC_Promotion_Detail__c的Promotion字段中找到)

SELECT Id, (SELECT ACC_Product_Group__c, ACC_Product_Subgroup__c, ACC_Quantity__c FROM Promotion_Details__r) 
FROM ACC_Promotion__c 
WHERE Id = :order.ACC_Promotion__c

3、从junction对象里,查询与之master-details关系的两个details记录

数据模型图如下:


查询需求:查询会议名称日期及与之相关的发言人姓名。

SELECT Session__r.Name,
       Session__r.Session_Date__c,
       Speaker__r.First_Name__c,
       Speaker__r.Last_Name__c
FROM Session_Speaker__c
ORDER BY Session__r.Session_Date__c,
         Session__r.Name

示例:

1-1 、下面查询返回与Closed Won的商机相关的Account的Id和Name

SELECT Id, Name FROM Account 
WHERE Id IN 
  ( SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Closed Won' 
  )
1-2、下面查询返回联系人来源于Web的商机Id
SELECT Id FROM Opportunity
WHERE AccountId NOT IN 
  (
    SELECT AccountId
    FROM Contact
    WHERE LeadSource = 'Web'
  )
1-3、下面查询返回与Closed Won商机且与LeadSource为Web的联系人相关的Account的Id和Name
SELECT Id, Name, Owner.UserName  FROM Account 
WHERE Id IN (SELECT AccountId FROM Contact WHERE LeadSource = 'Web') 
      AND Id IN (SELECT Accountid FROM Opportunity WHERE StageName = 'Prospecting')
1-4、在查询语句中使用  [ORDER BY  fieldOrderByList  {ASC|DESC} [NULLS {FIRST|LAST}] ]
未使用Example:

SELECT Tender_Project__r.Name, Bid_Amount__c, Is_Finalists_Competitive__c, Bid_Price_Fomula__c, Supplier_Bid_Price__c, Bidding_Ranking__c, CreatedDate 
FROM Advertiser_Tender_Relationship__c 
WHERE Tender_Project__c = 'a0L7F000000JyMk' ORDER BY Bidding_Ranking__c ASC
效果预览:

使用后Example:

SELECT Tender_Project__r.Name, Bid_Amount__c, Is_Finalists_Competitive__c, Bid_Price_Fomula__c, Supplier_Bid_Price__c, Bidding_Ranking__c, CreatedDate 
FROM Advertiser_Tender_Relationship__c 
WHERE Tender_Project__c = 'a0L7F000000JyMk' ORDER BY Bidding_Ranking__c ASC NULLS LAST







已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页