SOQL

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

SOQL

Salesforce Object Query Language (SOQL)

SOQL 문법

Savepoint sp = Database.setSavepoint();
SELECT fieldList  
  FROM objectType
 WHERE conditionExpression
  WITH [DATA CATEGORY] filteringExpression]
 GROUP BY fieldGroupByList] | GROUP BY ROLLUP|CUBE (fieldSubtotalGroupByList)
HAVING havingConditionExpression
 ORDER BY  fieldOrderByList ASC | DESC ? NULLS FIRST | LAST ?
 LIMIT ?

List<Account> 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 = [select id, name from account where name = 'Singha' limit 1].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

Contact item 또는 List<Contact> 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]
      LIMIT 1];

• data[0].Id, data[0].FirstName, data[0].LastName
• data[0].Account.Id, data[0].Account.Name, data[0].Account.Type
• List<Case> caseData = data[0].Cases;

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

Relationship

Master-detail (1:n)	Master <- Detail Master 삭제시 detail도 삭제됨 Detail에서 Master 연동 필드는 반드시 Layout에 표시되어야 함 Detail의 소유자는 Master를 따름 Detail에 Sharing rules, manual sharing, queue 사용 불가 최대 3레벨까지 detail을 가질 수 있음 Standard Object는 detail이 될 수 없습니다
Many-to-many (m:n)	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를 지원하지 않음
Lookup (1:n)
Hierarchical	User object에만 허용됨

Aggregate

Integer  i  =  [select  count()  from  contact]

AggregateResult[] agrData = �   
    [SELECT CampaignId, AVG(Amount) avg�      
       FROM Opportunity�     
      GROUP BY CampaignId];

SUM(~), AVG(~), MAX(~), MIN(~), COUNT_DISTINCT(~), COUNT()
Object camp = agrData[0].get('CampaignId');
Object avg = agrData[0].get('avg');

• Aggregate Functions

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

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

• 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<List<SObject>> searchList = [FIND 'map*'  
                                       IN ALL FIELDS 
                                       RETURNING  Account  (id,  name), Contact, Opportunity, Lead];
Account[] accounts = ((List<Account>)searchList[0]);
Contact[] contacts = ((List<Contact>)searchList[1]);
Opportunity[] opportunities = ((List<Opportunity>)searchList[2]);
Lead[] leads = ((List<Lead>)searchList[3]);

List<List<SObject>>  dataSet  =
    [FIND 'map*'
         IN ALL FIELDS
          RETURNING Account (id, name  WHERE ~ LIMIT ~),
                    Contact, Opportunity
         WITH ~ = ~ 
         LIMIT 5];
List<Account> accounts = (List<Account>)dataSet[0];
List<Contact> contacts = (List<Contact>)dataSet[1];

DML

DML : Data Manipulation Language

• Database.query(~) == [~]
• 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)
  

지원 업체

SuiteCRM을 사용한 영업관리, 고객관리는 아래 담당자에게 연락하여 주시면, 빠르고 친절하게 전문적인 답변을 드리겠습니다.

영업 문의	sales@obcon.biz	010-4667-1106	영업 대표
기술 문의	tech@obcon.biz		구축/컨설팅 담당
고객 지원	support@obcon.biz		고객 지원 담당

OBCon 홈페이지 바로가기