"SOQL"의 두 판 사이의 차이
둘러보기로 가기
검색하러 가기
잔글 |
잔글 |
||
160번째 줄: | 160번째 줄: | ||
== 지원 업체 == | == 지원 업체 == | ||
+ | |||
{{지원업체}} | {{지원업체}} | ||
+ | |||
+ | [[Category:CRM|Category:CRM]] |
2018년 2월 12일 (월) 10:55 기준 최신판
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) |
|
Many-to-many (m:n) |
|
Lookup (1:n) | |
Hierarchical |
|
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 | 고객 지원 담당 |