• SOQL
• SOQL 문법
• 상위/하위 개체 읽기
• Relationship
• Aggregate
• SOSL
• SOSL 문법
• SOSL 사례
• DML
• 참고 문헌
• 지원 업체

Force.com의 SOQL, SOSL, DML을 정리 합니다.

 

SOQL

---

Salesforce Object Query Language (SOQL)

 

SOQL 문법

 Savepoint sp = Database.setSavepoint();
 SELECT fieldList  
   FROM objectType
  WHERE conditionExpression
   WITH [CATEGORY] filteringExpression](DATA)
  GROUP BY fieldGroupByList] | GROUP BY ROLLUP|CUBE (fieldSubtotalGroupByList)
 HAVING havingConditionExpression
  ORDER BY  fieldOrderByList ASC | DESC ? NULLS FIRST | LAST ?
  LIMIT ?
 
 List aa = [SELECT id, name, toLabel(name)
                       FROM Account
                      WHERE name = 'Acme'
                        AND LoginTime < 2010-09-21T22:16:30.000Z
                      GROUP BY id, name
                      LIMIT 10
                      FOR UPDATE];
 Database.rollback(sp);

 

상위/하위 개체 읽기

 String name = [id, name from account where name = 'Singha' limit 1](select).name
 SELECT Name, customfield__c, (SELECT OldValue, NewValue FROM foo__history) FROM foo__c
 
 //--- child-to-parent
 SELECT Contact.FirstName, Contact.Account.Name from Contact
 SELECT Id, FirstName__c, Mother_of_Child__r.FirstName__c FROM Daughter__c WHERE Mother_of_Child__r.LastName__c LIKE 'C%'
 //--- parent-to-child
 SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account
 SELECT LastName__c, (SELECT LastName__c FROM Daughters__r) FROM Mother__c

http://www.salesforce.com/us/developer/docs/api/Content/images/dev_guides/api/images/rel_basic.gif 

 Contact item 또는 List data = 
     [SELECT Id, FirstName, LastName,
             Account.Id, Account.Name, Account.Type,  //--- Parent, __r
            (SELECT Id, Subject, Description          //--- Child
               FROM Cases)
        FROM Contact
       WHERE 조건           //--- :var, AND, OR, NOT, LIKE ‘가%’, IN
       ORDER BY FirstName ASC[DESC](DESC.md)
       LIMIT 1];

• data[0].Id, data[0].FirstName, data0.LastName

• data[0].Account.Id, data[0].Account.Name, data0.Account.Type

• List caseData = data0.Cases;

  • caseData[0].Subject === data[0].Cases0.Subject

 
 

Relationship

{|cellspacing="0" cellpadding="2" border="1" width="100%" bgcolor="#FFFFFF" align="center" 
|- |width="20%" align="center" valign="middle" style="background-color:#eee;"|Master-detail (1:n) |width="80%"|

• Master <- Detail

  • Master 삭제시 detail도 삭제됨

  • Detail에서 Master 연동 필드는 반드시 Layout에 표시되어야 함

  • Detail의 소유자는 Master를 따름

  • Detail에 Sharing rules, manual sharing, queue 사용 불가

  • 최대 3레벨까지 detail을 가질 수 있음

  • Standard Object는 detail이 될 수 없습니다
    align="center" valign="middle" style="background-color:#eee;"

• Primary Master <- Junction object -> Secondary Master

  • Junction object에는 최대 2개의 Master만 허용됨

  • Junction object는 Look and feel, Ownership, Division은 Primary master를 따릅니다

   
  

• Report type

  • "Primary master with junction object and secondary master"

  • "Secondary master with junction object and primary master"

   
  

• 제약 사항

  • Division transfer를 지원하지 않음
    align="center" valign="middle" style="background-color:#eee;"
    -
    align="center" valign="middle" style="background-color:#eee;"

• User object에만 허용됨
  |}

 

Aggregate

 Integer  i  =  [ count()  from  contact](select)
 
 AggregateResult[](.md) agrData = �   
     [SELECT CampaignId, AVG(Amount) avg�      
        FROM Opportunity�     
       GROUP BY CampaignId];
 
 SUM(~), AVG(~), MAX(~), MIN(~), COUNT_DISTINCT(~), COUNT()
 Object camp = agrData[0](0.md).get('CampaignId');
 Object avg = agrData[0](0.md).get('avg');

 

• Aggregate Functions

  • count(), count(~) : 레코드의 갯수를 Integer로 반환

  • SUM(~)

  • AVG(~) : 평균

 

 AggregateResult[] result = [AVG(Amount) aver FROM Opportunity](SELECT);
 Object avgAmount = result[0](0.md).get('aver');
 AggregateResult[] result = [CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId](SELECT);

• MAX(), MIN()

• COUNT_DISTINCT()

 
 

SOSL

---

Salesforce Object Search Language (SOSL)

 

• 제약 사항

  • Apex Class에서만 사용할 수 있음

 
 

SOSL 문법

 FIND 'map*'  
      IN ALL FIELDS 
      RETURNING  Account  (id,  name), Contact, Opportunity, Lead
      WITH ~ = ~
      LIMIT 5

 

SOSL 사례

• Account, Contact, Opportunity, Lead 개체의 모든 필드에서 값이 map로 시작하는 필드를 가지는 모든 개체를 반환

 

 List> searchList = [FIND 'map*'  
                                        IN ALL FIELDS 
                                        RETURNING  Account  (id,  name), Contact, Opportunity, Lead];
 Account[] accounts = ((List)searchList[0](0.md));
 Contact[] contacts = ((List)searchList[1](1.md));
 Opportunity[] opportunities = ((List)searchList[2](2.md));
 Lead[] leads = ((List)searchList[3](3.md));
 
 List>  dataSet  =
     [FIND 'map*'
          IN ALL FIELDS
           RETURNING Account (id, name  WHERE ~ LIMIT ~),
                     Contact, Opportunity
          WITH ~ = ~ 
          LIMIT 5];
 List accounts = (List)dataSet[0](0.md);
 List contacts = (List)dataSet[1](1.md);

 

DML

---

DML : Data Manipulation Language

 

• Database.query() == [](~.md)

• insert, update, upsert, delete,
  (merge, undelete

• Database.SaveResult = Database.insert(~, true), update, upsert, delete

• Savepoint sp = Database.setSavepoint();

• Database.rollback(sp);

 
 

참고 문헌

---

 

• Salesforce Object Query Language (SOQL)

• Aggregate Functions

• Salesforce Object Search Language (SOSL)
  [[Category:Salesforce|Category:Salesforce]]
  분류: Cloud

 

지원 업체

---

 

{{지원업체}}

 

분류: CRM

---