SQL ãã SOQL ãžã®ç§»è¡
åŠç¿ã®ç®ç
ãã®åå ãå®äºãããšã次ã®ããšãã§ããããã«ãªããŸãã
- Salesforce ãªããžã§ã¯ãã®ã¡ãªãããçè§£ããã
- SQL ãš SOQL ã®é¡äŒŒç¹ãšçžéç¹ãæããã«ããã
- ã¯ãŒã¯ãã³ãã䜿çšããŠç°¡å㪠SOQL ã¹ããŒãã¡ã³ããäœæããã
- ããè€éãªãªã¬ãŒã·ã§ã³ã¯ãšãªãäœæããã
- éèšã¯ãšãªãäœæããã
Salesforce ãªããžã§ã¯ããçè§£ãã
Salesforce ãã©ãããã©ãŒã ã«ã¯ãã¢ããªã±ãŒã·ã§ã³ããã°ããç°¡åã«äœæã§ããããã«ãã倿°ã®æ©èœãåãã匷åãªããŒã¿ããŒã¹ããããŸããSQL Server ãæäœããããšãããã°ãããŒã¿ãããŒãã«ãè¡ã«ä¿åãããããšããåç¥ã§ãããã仿¹ãSalesforce ã®ããŒã¿ããŒã¹ã§ã¯ããªããžã§ã¯ãã䜿çšããŠããŒã¿ãæ ŒçŽããŸãããªããžã§ã¯ãã¯éåžžã®ããŒãã«ã«ããæ©èœããã¹ãŠåããŠããã»ããæ©èœæ§ãæ±çšæ§ãé«ããæ¡åŒµæ©èœã远å ãããŠããŸããåãªããžã§ã¯ãã¯å€æ°ã®é ç®ã§æ§æããããã®é ç®ãããŒã¿ããŒã¹ã®åã«çžåœããŸããããŒã¿ã¯ãªããžã§ã¯ãã®ã¬ã³ãŒãã«æ ŒçŽããããã®ã¬ã³ãŒããããŒã¿ããŒã¹ã®è¡ã«çžåœããŸããããã ãã§ã¯ãããŸããã
ãªããžã§ã¯ãã«ã¯æ¬¡ã® 2 çš®é¡ããããŸãã
-
æšæºãªããžã§ã¯ã â Salesforce ã«çµã¿èŸŒãŸããŠãããªããžã§ã¯ãã§ããäžè¬ç㪠CRM ãªããžã§ã¯ãã«ã¯ååŒå
ãååŒå
責任è
ãåè«ããªãŒããªã©ããããŸãã
-
ã«ã¹ã¿ã ãªããžã§ã¯ã â ã¢ããªã±ãŒã·ã§ã³åºæã®æ
å ±ãæ ŒçŽããããã«äœæããæ°èŠãªããžã§ã¯ãã§ããã«ã¹ã¿ã ãªããžã§ã¯ãã¯ãæšæºãªããžã§ã¯ããæäŸããæ©èœãæ¡åŒµããŸããããšãã°ãåååšåº«ã远跡ããã¢ããªã±ãŒã·ã§ã³ãäœæããŠããå Žåã¯ãMerchandise (åå)ãOrders (泚æ)ãInvoices (è«æ±æž) ãšãã£ãã«ã¹ã¿ã ãªããžã§ã¯ããäœæã§ããŸãã
ãããããå¯ãã ãšæããŸããããªããžã§ã¯ãã«ã¯ããããªããžã§ã¯ãã®ã¬ã³ãŒããå¥ã®ãªããžã§ã¯ãã®ã¬ã³ãŒãã«ã©ã®ããã«é¢é£ä»ããããå®çŸ©ãããªã¬ãŒã·ã§ã³é ç®ãèšå®ã§ããŸãããããã¯åºæ¬çã«ã¯äž»ããŒããã³å€éšããŒãšåãã§ãããããæè»ã§ãããããããŒã¿ã¢ãã«ã®èšèšãšå®è£ ããã容æã«ãªããŸãã
æšæºãªããžã§ã¯ãã§ãã«ã¹ã¿ã ãªããžã§ã¯ãã§ããSalesforce ãªããžã§ã¯ãã«ãã£ãŠããŒã¿ãæ ŒçŽããæ§é ãæ±ºãŸãã ãã§ãªãããŠãŒã¶ãŒãã€ã³ã¿ãŒãã§ãŒã¹èŠçŽ ãæŽ»çšããŠã¿ããããŒãžã®é ç®ã®ã¬ã€ã¢ãŠããé¢é£ã¬ã³ãŒãã®ãªã¹ããªã©ã®ããŒã¿ãæäœã§ããããã«ãªããŸããæšæºæ©èœã®å Žåããªããžã§ã¯ããªã¬ãŒã·ã§ã³ãããã³ã° (ORM) ãå®è£ ããããããŒã¿ã® CRUD ã®ããã« UI ãèšè¿°ããããããŒãã«ãäœæãããããå¿ èŠã¯ãããŸããããã®æšæºæ©èœã¯ããã©ãããã©ãŒã ã«ãã£ãŠèªåçã«æäŸãããŸãããŸãããªããžã§ã¯ãã«ã¯ã¢ã¯ã»ã¹ç®¡çãå ¥åèŠåãæ°åŒãå±¥æŽç®¡çãªã©ã®æ©èœã«å¯ŸãããµããŒããçµã¿èŸŒãŸããŠããŸãããªããžã§ã¯ãã®å±æ§ã¯ãã¹ãŠã¡ã¿ããŒã¿ã§èšè¿°ããããããããžã¥ã¢ã«ã€ã³ã¿ãŒãã§ãŒã¹ãŸãã¯ããã°ã©ã ã®ããããã«ãã£ãŠã¬ã³ãŒããç°¡åã«äœæããã³å€æŽã§ããŸãã
ã€ãŸãããªããžã§ã¯ãã¯ããŒã¿ãæ ŒçŽããåãªãã³ã³ããã«çãŸããŸããããªããžã§ã¯ãã®è±å¯ãªæ©èœã«ãã£ãŠéçºè ã®æéãå€§å¹ ã«çããããããèªç€Ÿã¢ããªã±ãŒã·ã§ã³ã«ç¹æã®æ©èœã®æ§ç¯ã«å°å¿µã§ããããã«ãªããŸããã«ã¹ã¿ã ãªããžã§ã¯ããé ç®ããªã¬ãŒã·ã§ã³ãªã©ã®äœæã«ã€ããŠã®è©³çްã¯ããããŒã¿ã¢ããªã³ã°ãã¢ãžã¥ãŒã«ãåç §ããŠãã ããã
䌌ãŠãããåãã§ã¯ãªã
.NET éçºè ã®çããã¯ããããã SQL Server ã®æäœã«ã¯ç²ŸéããŠããã§ãããããããŠãSQL ã䜿çšããŠã¢ãããã¯ã¯ãšãªãèšè¿°ããããšã«ãæ £ããŠããã§ããããã§ããããSOQL (Salesforce Object Query Language) ãšåŒã°ãã Salesforce å°çšã«èšèšãããé¡äŒŒã®èšèªã玹ä»ããã®ã«æãè¯ãæ¹æ³ã¯ããã®äž¡è ãæ¯èŒããããšã ãšèããŸããã
ãŸãç¥ã£ãŠããã¹ããªã®ã¯ãã©ã¡ããã¯ãšãªèšèªãšåŒã°ããŠããŸãããSOQL 㯠SELECT ã¹ããŒãã¡ã³ãã«ããã¯ãšãªã®å®è¡ã«ã®ã¿äœ¿çšããããšããããšã§ããSOQL ã«ã¯ INSERTãUPDATEãDELETE ã¹ããŒãã¡ã³ãã«çžåœãããã®ããããŸãããSalesforce ã§ã¯ãããŒã¿æäœã«ããŒã¿æäœèšèª (DML) ãšããæ¹æ³ã䜿çšãããŸããDML ã«ã€ããŠã¯åŸã§èª¬æããŸããããã§ã¯ãSOQL ã® SELECT ã¹ããŒãã¡ã³ãã䜿çšã㊠Salesforce ããŒã¿ãç §äŒããæ¹æ³ã®ã¿ã説æããŸãã
ããã«æ°ã¥ã倧ããªéãã¯ãSOQL ã§ã¯ SELECT *
ãå®è¡ããæ¹æ³ãç°ãªãããšã§ããSOQL 㯠Salesforce ããŒã¿ãè¿ãããã®ããŒã¿ã¯ãã«ãããã³ãç°å¢ã«ååšããŸããããã«ãããã³ãç°å¢ã§ã¯å
šå¡ããããŒã¿ããŒã¹ãå
±æãããŠãããããªãã®ã§ããããã*
ãªã©ã®ã¯ã€ã«ãã«ãŒãæåã䜿çšãããšåé¡ãçºçããŸããççŽã«èšããšãç¹ã«ããŒãã«ã®é
ç®åãããããªãå Žåãªã©ã«ãæ°ãã SQL ã¯ãšãªãéå§ããŠãSELECT * FROM SOME-TABLE
ãšå
¥åããŠããŸããã¡ã§ããããã®ã¢ã¯ã·ã§ã³ã¯ãå
±æç°å¢å
ã®ä»ã®ããã³ãã«å€å€§ãªåœ±é¿ãåãŒãå¯èœæ§ããããŸããããã³ãæ§é ãä¿è·ããé
ç®ã¬ãã«ã»ãã¥ãªãã£ãå°éããããã«ãSOQL ã§ã¯ FIELDS()
ã䜿çšããŠãã¢ã¯ã»ã¹æš©éãããé
ç®ã®ã¿ã衚瀺ããŸããããšãã°ãSELECT FIELDS(ALL) FROM Account
ã¯ãååŒå
ãªããžã§ã¯ãã«å¯ŸããŠã¢ã¯ã»ã¹æš©ããããã¹ãŠã®æšæºé
ç®ãšã«ã¹ã¿ã é
ç®ãè¿ããŸãã
SOQL ã§ã¯ãè¿ãããåé ç®åãæå®ããŸãããã以å€ã¯ãSOQL ã® SELECT ã¹ããŒãã¡ã³ã㯠SQL ãšäŒŒãŠããŸããSOQL ã¯ãšãªã®èšè¿°ã¯ç°¡åã ãšæããããšã§ãããããã ããSQL ãš SOQL ã¯äŒŒãŠããããã©ãåãã§ã¯ãªããšããããšãç¥ã£ãŠããå¿ èŠããããŸããSOQL 㯠SQL SELECT ã¹ããŒãã¡ã³ãããµããŒãããé«åºŠãªæ©èœã®äžéšããµããŒãããŠããŸããããã ããSalesforce Platform äžã§ã¯ããããã®è¿œå æ©èœã¯æ¬åœã«å¿ èŠãããŸãããSOQL ã§ã¯ãã¡ããã©å¿ èŠãªæ©èœã®ã¿ãæäŸãããããããŠãŒã¶ãŒã«ãšã£ãŠäœ¿ãããããªã£ãŠããŸãã
éçºè ã³ã³ãœãŒã«ã§ã¯ãšãªãäœæãã
SOQL ã¯ãšãªã®èšè¿°ã話é¡ã«äžãã£ããšããã§ãã©ããã£ãŠèšè¿°ããã®ã ãããšãèããããããŸããã1 ã€ã®æ¹æ³ã¯ãéçºè ã³ã³ãœãŒã«ã䜿çšããããšã§ãã.NET éçºè ãã¡ã¯ããŒã«å¥œãã§ãããã®åŒ·åãªããŒã«ã䜿çšããã°ãã·ã¹ãã 管çè ãéçºè ãããŸããŸãªæ¹æ³ã§ Salesforce API ã䜿çšããŠçµç¹ã«ã¢ã¯ã»ã¹ã§ããŸãã.NET éçºè ã®æ¹ã¯ããããã Visual Studio ã³ãŒãã®ããšã¯çç¥ãããŠãããšæããŸããå®ã¯ããŒã«ã«ãã·ã³ã§ã«ã¹ã¿ã éçºã SOQL ã®äœæãè¡ãããšãã§ãã VS Code åã Salesforce æ¡åŒµæ©èœãçšæãããŠããŸãããã®æ¡åŒµæ©èœã¯ãææ°ã®ãœãŒã¹é§ååéçºäœéšãæäŸãã Salesforce DX ãšå¯æ¥ã«çµã³ä»ããŠããŸããSalesforce DX ã®è©³çްã¯ãSalesforce DX å ¥éããã¬ã€ã«ãåç §ããŠãã ããã
ããã§ã¯ãéçºè ã³ã³ãœãŒã«ã䜿çšãã SOQL ã¯ãšãªã®äœæã«çŠç¹ãçµããŸãããæéãããã°éçºè ã³ã³ãœãŒã«ã䜿çšããŠããŸããŸãªæ©èœã確èªããŠã¿ãŠãã ããããã£ãšæ°ã«å ¥ããŸãããç¡æã® Developer Edition (DE) çµç¹ã«ãµã€ã³ã¢ããããæ¬¡ã®æé ãå®è¡ããŸãã
- [Setup (èšå®)] (
) ã¡ãã¥ãŒããã[Developer Console (éçºè ã³ã³ãœãŒã«)] ãéžæããŠéçºè ã³ã³ãœãŒã«ãéããŸãã
- äžéšãã€ã³ã® [Query Editor (ã¯ãšãªãšãã£ã¿ãŒ)] ã¿ããã¯ãªãã¯ããŸãã
- 次ã®ã¯ãšãªãå
¥åããŸãã
SELECT Id, Name, Type FROM Account
-
[Execute (å®è¡)] ãã¯ãªãã¯ããŸãã
ã¯ãšãªçµæã«ã¯ 3 ã€ã®åãå«ãŸããŸããçµæãã¹ã¯ããŒã«ããŸãã
ã¯ãšãªã¯ãããŒãã«ã§ã¯ãªãããªããžã§ã¯ãããé ç®ãååŸããŸããããŒã¿ã¯ãªããžã§ã¯ãå ã«ä¿æãããŸããå®éããã®ãªããžã§ã¯ã㯠Salesforce ãªããžã§ã¯ããšããæå³ã§ sObject ãšåŒã°ããSalesforce ãã©ãããã©ãŒã ã«ç·å¯ã«çµ±åãããŠãããããæäœã容æã§ãã
æ®å¿µãªããšã«ãSalesforce ã®æ¥æé ç®ã¯ãSOQL ã§ã SQL ãšåæ§ã«è€éã§æäœãã«ãããªã£ãŠããŸãããã ãã幞ãã«ã Salesforce ã«ã¯ãSOQL ã§ã®æ¥æé ç®ã®æäœã容æã«ããããã€ãã®æ¥ä»é¢æ°ããããŸããããã¥ã¡ã³ããåç §ããã€ãã§ã«ãSOQL ã§ã®é貚é ç®ã®åŠçã«ã€ããŠã確èªããŠãããŸããããé貚é ç®ã®åŠçãå€å°ç°ãªã£ãŠããŠãç¹ã«è€æ°ã®éè²šãæ±ãçµç¹ã§ã¯ç°ãªããŸãã
çµæã®çµã蟌ã¿
SOQL ã§å¿ é ã®å¥ã¯ SELECT ãš FROM ã® 2 ã€ã®ã¿ã§ããWHERE å¥ã¯çç¥å¯èœã§ãããã ããè¯ãéçºè (ããããèªã¿ã®çãããããã§ããã) ã¯ãèšè¿°ããã»ãšãã©ãã¹ãŠã®ã¯ãšãªã« WHERE å¥ãå«ããããšãããã®ã§ããå¿ èŠä»¥äžã®ããŒã¿ãè¿ãããŠãæå³ããããŸãããããã
ããã§ãããã®ããã¿ã確èªããæãç°¡åãªæ¹æ³ã¯ã¯ãŒã¯ãã³ãã䜿çšããããšã§ãã
- [Setup (èšå®)] (
) ã¡ãã¥ãŒããã[Developer Console (éçºè ã³ã³ãœãŒã«)] ãéžæããŠéçºè ã³ã³ãœãŒã«ãéããŸãã
- äžéšãã€ã³ã® [Query Editor (ã¯ãšãªãšãã£ã¿ãŒ)] ã¿ããã¯ãªãã¯ããŸãã
- 次ã®ã¯ãšãªãå
¥åããŸãã
SELECT AccountId, Email, Id, LastName FROM Contact
-
[Execute (å®è¡)] ãã¯ãªãã¯ããŸãã
ãã®ã¯ãšãªã¯ãçµç¹ã®ãã¹ãŠã®ååŒè²¬ä»»è ãè¿ããŸããéçºçµç¹ã§ã¯ãªã¹ãã¯çããããããŸããããå®éã®çµç¹ã§ã¯ãè¿ãããååŒå 責任è ã®æ°ã¯æ°åã«ããªãå Žåããããããåžžã« WHERE å¥ã䜿çšã㊠SOQL ã¯ãšãªãçµã蟌ãããšãæ€èšããå¿ èŠããããŸããApex ã³ãŒãå ã§äœ¿çšããå Žåã¯ç¹ã«ãããéèŠã§ãã
- Where å¥ã远å ããŠãã¯ãšãªãæŽæ°ããŸãã
SELECT AccountId, Email, Id, LastName FROM Contact WHERE Email LIKE '%.net%'
-
[Execute (å®è¡)] ãã¯ãªãã¯ããŸãã
ã©ããªã£ããããããŸãã? äœæãããã¯ãšãªã«ã¯ contains
ãšããèšèããããŸããããã®ä»£ããã« LIKE
ã䜿çšãããŠããŸããããã«ãå¿
èŠãªäžéåŒçšç¬Š (Email ã¯ããã¹ãé
ç®ã§ãããã)ãããã³ã¯ã€ã«ãã«ãŒãæ€çŽ¢ã§ããããšã瀺ãå
é ãšæ«å°Ÿã®ããŒã»ã³ãèšå·ãå«ãŸããŠããŸãã
ã¡ã¢: ã¯ã€ã«ãã«ãŒãã䜿çšããããšãç¹ã«äŸã®ãããªå é ãšæ«å°Ÿã®ã¯ã€ã«ãã«ãŒãã䜿çšããããšã¯ãå§ãããŸãããå¹ççãªã¯ãšãªãäœæããæ¹æ³ã«ã€ããŠã¯åŸã®åå ã§èª¬æããŸãããä»ã®ãšããã¯ãå¯èœãªéããã®ãããªã¯ã€ã«ãã«ãŒãæ€çŽ¢ãé¿ãããšããããšãèŠããŠãããŠãã ããã
- ã€ãã§ã«ã
ORDR BY
å¥ã䜿çšããŠçµæã LastName ã§äžŠã¹æ¿ããŸããã¯ãšãªã¯æ¬¡ã®ããã«ãªããŸããSELECT AccountId, Email, Id, LastName FROM Contact
WHERE Email LIKE '%.net%' ORDER BY LastName ASC NULLS FIRST
-
[Execute (å®è¡)] ãã¯ãªãã¯ãããšãçµæã®é åºä»ããªã¹ãã衚瀺ãããŸãã
çµæã§ç¹ã«æ³šç®ããŠæ¬²ããã®ã¯ãAccountId ãš Id ã® 2 ã€ã®é ç®ã§ãããããã®é ç®ã«ã¯ãååŒå ã¬ã³ãŒããšååŒå 責任è ã¬ã³ãŒããäœæããããšãã«ãã©ãããã©ãŒã ã«ãã£ãŠå²ãåœãŠãããäžæã® 18 æåã®æååãå«ãŸããŠããŸããAccountId é ç®ã¯ããã®ç¹å®ã®ååŒå 責任è ãå²ãåœãŠãããŠããååŒå ã¬ã³ãŒãã«é¢é£ä»ããããŠããŸããSQL çšèªã§ã¯ãããã¯å€éšããŒãªã¬ãŒã·ã§ã³ã§ããId é ç®ã¯ååŒå 責任è ã«é¢é£ä»ããããŠããŸããSQL çšèªã§ã¯ãããã¯äž»ããŒã衚ããŸãã
å€éšããŒã®è©±ãåºããšããã§ãSOQL ã§ããŒãã«ãçµåããã«ã¯ã©ãããã®ã ãããšãæããããããŸãããäžèšã§çãããšãçµåããŸãããSOQL ã«ã¯ JOIN å¥ã«çžåœãããã®ããããŸãããããããå¿é ã¯ãããŸãããããã¯è¯ãããšãªã®ã§ãã
å¥ã®çš®é¡ã®çµå
ãããèããŠãé©ããªããšæããŸãããSalesforce ããªããžã§ã¯ããããŒãã«ãçµåããæ¹æ³ã¯åŸæ¥ã®ãã®ãšã¯å°ãç°ãªããŸããJOIN å¥ã䜿çšããŠããŒãã«ãçµåããã®ã§ã¯ãªãããªã¬ãŒã·ã§ã³ã¯ãšãªãšãããã®ãèšè¿°ããŸãã
ãããã§ãäžäœããã¯äœã§ãã?ããšãã質åãèãããŠãããã§ããããããèããŠãããŸããã
Salesforce ã§ã¯ã芪åãªã¬ãŒã·ã§ã³ã䜿çšã㊠2 ã€ã®ãªããžã§ã¯ããçµåããŸããSQL ãšåæ§ã« SOQL ã§ãå€éšããŒã䜿çšããŠãããã® 2 ã€ã®ãªããžã§ã¯ããé¢é£ä»ããŸãããSOQL ã§ã¯ã¯ãšãªæ§æãç°ãªããŸãããã®æ°ããæ§æã§ã¯è¡ã§ã¯ãªããªããžã§ã¯ãã䜿ããããåãã¯éåæãèŠããããç¥ããŸãããããã©ããäžæŠåºæ¬ã«æ £ããã°ãSQL ã§çµåãèšè¿°ãããããããªã¬ãŒã·ã§ã³ã¯ãšãªãèšè¿°ããã»ããã¯ããã«ç°¡åã ãšæããã§ãããã
SOQL ã§ã¯ã2 ã€ã®åºæ¬çãªãªã¬ãŒã·ã§ã³ã¯ãšãªçš®å¥ãèŠããŠããå¿ èŠããããŸãã
- å-芪
- 芪-å
ããããæ©èœãç°ãªããŸãããã§ã«ååŒå ãªããžã§ã¯ããšååŒå 責任è ãªããžã§ã¯ãã®ããã€ãã®é ç®ã«ã€ããŠåãäžããŸãããããããã¯äžè¬çã«çµåããããã®ãªã®ã§ãããããå§ããŸããããããã§ç¥ã£ãŠããã¹ãéèŠãªããšã¯ãååŒå ã芪ã§ååŒå 責任è ãåã§ãããšããããšã§ãã
å-芪ã¯ãšãªã®èšè¿°
ååŒå ãšååŒå 責任è ã®æ å ±ãè¿ãã¯ãšãªãèšè¿°ãããšããŸãã1 ã€ç®ã®ãªãã·ã§ã³ã¯å-芪ã¯ãšãªãèšè¿°ããããšã§ãããªã¬ãŒã·ã§ã³ã¯ãšãªã¯ããããã衚èšãã䜿çšããŠèŠªããã®ããŒã¿ã«ã¢ã¯ã»ã¹ããŸããã€ãŸããããªãªãã«ãã£ãŠãªã¬ãŒã·ã§ã³åãã¯ãšãªå¯Ÿè±¡ã®é ç®åãšåºå¥ããŸãã
ãã®ããã¿ãçè§£ããããã«ãé¢é£ä»ããããååŒå ã®ååãå«ãååŒå 責任è ã®ãªã¹ããè¿ããªã¬ãŒã·ã§ã³ã¯ãšãªã®èšè¿°ãé ã远ã£ãŠèª¬æããŸãããã ããä»åã¯ãã¯ãŒã¯ãã³ãã§ã¯ãªãéçºè ã³ã³ãœãŒã«ã® [Query Editor (ã¯ãšãªãšãã£ã¿ãŒ)] ã¿ãã䜿çšããŸãã
- éçºè
ã³ã³ãœãŒã«ã§ãäžéšãã€ã³ã® [Query Editor (ã¯ãšãªãšãã£ã¿ãŒ)] ã¿ããã¯ãªãã¯ããŸãã
- 次ã®ã¯ãšãªãå
¥åããŸãã
SELECT FirstName, LastName, Account.Name FROM Contact
-
[Execute (å®è¡)] ãã¯ãªãã¯ããŸãã
ããããçããã¯ãŸã SQL çšèªã§èããŠããã§ããããããã§ããã¡ãã®ã·ããªãªãæ³åããŠãã ãããAccount ãš Contact ãšãã 2 ã€ã® SQL ããŒãã«ããããããŒãã«éã«äžå¯Ÿå€ãªã¬ãŒã·ã§ã³ãããå Žåãåãæ å ±ãè¿ã SQL ã¯ãšãªã¯ã©ã®ããã«èšè¿°ããŸãã?
ãã¡ããçµåã䜿çšããŸãããã ãããã®å Žåã¯ãååŒå ã«é¢é£ä»ããããŠããªããã®ãå«ããã¹ãŠã®ååŒå 責任è ãè¿ãäžèšãšåçã®ã¯ãšãªãèšè¿°ãããããå³å€éšçµåãå¿ èŠã§ããã¯ãšãªã¯æ¬¡ã®ããã«ãªããŸãã
SELECT c.FirstName, c.LastName, a.Name FROM Account a RIGHT JOIN Contact c ON (c.AccountId = a.Id)
ããã§åã«æ»ã£ãŠåçã® SOQL ãèŠãŠã¿ãŸãããã
SELECT FirstName, LastName, Account.Name FROM Contact
SOQL ã¯ãšãªã®ã»ãããã£ãšç°¡åã ãšæããŸããã?
æ£çŽãªãšããããªã¬ãŒã·ã§ã³ã¯ãšãªã¯æ±ããé£ããå ŽåããããŸããç¹ã«ãã«ã¹ã¿ã ãªããžã§ã¯ãã«å¯Ÿãããªã¬ãŒã·ã§ã³ã·ããåãäœã§ããããææ¡ãã«ããå ŽåããããŸããSQL ã¯ãšãªãã SOQL ã¯ãšãªãžã®å€æã«ã€ããŠã®è©³çްã¯ããã¡ãã®ãã³ãºãªã³ãã¬ãŒãã³ã°åç»ãåç §ããŠãã ããã
ãããçš®å¥ã®ã¯ãšãªã«ã€ããŠç¥ã£ãŠããã¹ãéåžžã«éèŠãªç¹ã¯ãããã衚èšã䜿çšããã° 5 ã€ã®ã¬ãã«ããã©ããŒã¹ã§ããããšã§ãããã®ãããåãã芪ã芪ã®èŠªã芪ã®èŠªã®èŠªããšããããã«ãã©ãããšãã§ããŸãã
芪-åã¯ãšãªã®èšè¿°
芪-åã¯ãšãªã§ããªã¬ãŒã·ã§ã³åã䜿çšãããŸããããã¹ããããéžæã¯ãšãªãšãããã®ã®äžã§äœ¿çšãããŸããåã®ã¯ãšãªçš®å¥ãšåæ§ã«ãããã§ãäŸã瀺ããŠèª¬æããŸãã
ä»åã¯ãAccount ãªããžã§ã¯ãããã¯ãšãªãèšè¿°ããé¢é£ä»ããããåååŒå 責任è ã®æ å ±ãè¿ããã¹ããããã¯ãšãªãããã«å«ããŸãã
- éçºè
ã³ã³ãœãŒã«ã§ãäžéšãã€ã³ã® [Query Editor (ã¯ãšãªãšãã£ã¿ãŒ)] ã¿ããã¯ãªãã¯ããŸãã
- 次ã®ã¯ãšãªãå
¥åããŸãã
SELECT Name, (Select FirstName, LastName FROM Contacts) FROM Account
ãã¹ããããã¯ãšãªå ã®ãªã¬ãŒã·ã§ã³åã§ã¯ãContact ã§ã¯ãªãè€æ°åœ¢ã®Contacts
ã䜿çšãããŠããŸããããã¯ãã€ãŸãããããç¹ã§ãã®ã§çè§£ããŠããããšãéèŠã§ãããªã¬ãŒã·ã§ã³ã¯ãšãªã䜿çšããå Žåã¯ã芪-åãªã¬ãŒã·ã§ã³åã¯è€æ°åœ¢ã®ååã«ããå¿ èŠããããŸãã
ã«ã¹ã¿ã ãªããžã§ã¯ãã䜿çšããå Žåã¯ããªã¬ãŒã·ã§ã³åã¯è€æ°åœ¢ã«ããã ãã§ã¯ãªããæ«å°Ÿã« 2 ã€ã®ã¢ã³ããŒã¹ã³ã¢ãš r ã远å ããŸããããšãã°ãã«ã¹ã¿ã ãªããžã§ã¯ã My_Object__c ã®ãªã¬ãŒã·ã§ã³å㯠My_Objects__r ã§ãã
-
[Execute (å®è¡)] ãã¯ãªãã¯ããŸãã
ã¯ãšãªçµæã«ã¯ 2 ã€ã®åãå«ãŸããŸããæ¬¡ã®å³ã§ãContacts åã®äžã®çµæãã©ã®ããã«è¡šç€ºãããŠãããã«æ³šç®ããŠãã ãããéåžžãåååŒå ã¯è€æ°ã®ååŒå 責任è ãšé¢é£ä»ããããŠãããããå§ãšå㯠JSON 圢åŒã§è¡šç€ºãããŸãã
ããã§ãããã®åãã¯ãšãªã SQL ã§ã¯ã©ã®ããã«èšè¿°ãããããèŠãŠã¿ãŸãããããã®ã¯ãšãªã§ã¯ãåçã® SQL ã¯æ¬¡ã®ãããªå·Šå€éšçµåã«ãªããŸãã
SELECT a.Name, c.FirstName, c.LastName FROM Account a LEFT JOIN Contact c ON (a.Id = c.AccountId)
ãã® SQL ã¯ãšãªã¯ããã¹ãŠã®ååŒå ã¬ã³ãŒããšãããã®ååŒå ã¬ã³ãŒãã«é¢é£ä»ãããããã¹ãŠã®ååŒå 責任è ãååŸããŸããSQL ã§è¿ãããåºå㯠JSON 圢åŒã§ã¯ãããŸããããã以å€ã¯ãSQL ã¯ãšãªãš SOQL ã¯ãšãªã®çµæã¯åãã«ãªããŸãã
ãã®æç¹ã§ãSOQL ã¯å¥åæå®ããµããŒãããŠããã ããããšçåã«æããããããããŸããããµããŒãããŠããŸãããSQL ã§äœ¿çšããŠãããã®ãšã¯ç°ãªããŸããSOQL ã«ã¯ AS ããŒã¯ãŒãããããŸãããSOQL ã§ã¯ãå¥åã䜿çšããŠãªããžã§ã¯ãåã衚ãããšã¯ã§ããŸãããé ç®åã®å¥åæå®ã¯éèšã¯ãšãªã§ã®ã¿äœ¿çšã§ããŸããããã«ã€ããŠã¯ã次ã«èª¬æããŸãã
ãã®ãããã¯ã«ã€ããŠããã«è©³ããåŠç¿ããã«ã¯ãããªãœãŒã¹ãã»ã¯ã·ã§ã³ã®ãã³ãºãªã³ãã¬ãŒãã³ã°åç»ã®ãªã³ã¯ãåç §ããŠãã ããã
éèšã«ã€ããŠ
SOQL ã«ã¯éèšæ©èœããããã»ãŒäºæ³ãããŠãããšããã«åäœããŸããããã§ã ãããæ£è§£ã§ããéèšã«ã€ããŠæ³šæãã¹ãç¹ã¯ãã»ãšãã©ã®é¢æ°ã§ã¯çµæã AggregateResult åã§è¿ããããšããããšã§ãã
SOQL ã§äœ¿çšã§ãã颿°ã«ã€ããŠã¯ãäžã®è¡šã«ç€ºããŸãå颿°ã®è©³çްã«ã€ããŠã¯å ¬åŒããã¥ã¡ã³ããåç §ããŠãã ããã
SOQL éèšé¢æ°
颿° |
説æ |
---|---|
AVG() |
æ°å€é ç®ã®å¹³åå€ãè¿ããŸãã |
COUNT()ãCOUNT(fieldName)ãããã³ COUNT_DISTINCT() |
ã¯ãšãªæ¡ä»¶ã«äžèŽããè¡æ°ãè¿ããŸãã |
MIN() |
é ç®ã®æå°å€ãè¿ããŸãã |
MAX() |
é ç®ã®æå€§å€ãè¿ããŸãã |
SUM() |
æ°å€é ç®ã®åèšãè¿ããŸãã |
SQL ã§ Account ãšããååã®ç¹å®ã®ããŒãã«ã®ã¬ã³ãŒãä»¶æ°ãååŸããã¯ãšãªã¯æ¬¡ã®ããã«ãªããŸãã
SELECT COUNT(*) FROM Account
SOQL ã§ã¯åãã¯ãšãªã¯æ¬¡ã®ããã«ãªããŸãã
SELECT COUNT() FROM Account
ããªã䌌ãŠããŸããã
éãã¯ãã©ã®ããŒãžã§ã³ã® COUNT 颿°ã䜿çšãããã«ãããã®ã§ããããŒãžã§ã³ã«ãã£ãŠæ»ãå€ãç°ãªããŸããé
ç®åããªã COUNT 颿°ã¯ãã»ãã®éèšé¢æ°ããåããããå€ãããŒãžã§ã³ã§ããæŽæ°ãè¿ããSQL ã® count(*)
颿°ã«æã䌌ãŠããŸãã
Count(fieldName) ã¯ãããæ°ããããŒãžã§ã³ã§ãfieldName ã null 以å€ã®å€ãæã€è¡ã®æ°ãè¿ããŸããç°ãªãç¹ã¯ãçµæã 1 ã€ã®å€ã§ã¯ãªã AggregateResults ã®ãªã¹ããšããŠè¿ããšããããšã§ãã
å®éã«ç¢ºèªããŠã¿ãŸãããã
- éçºè
ã³ã³ãœãŒã«ã§ [Query Editor (ã¯ãšãªãšãã£ã¿ãŒ)] ãã¯ãªãã¯ããŸãã
- 次ã®ã¯ãšãªãå
¥åããŸãã
SELECT COUNT() FROM Account
-
[Execute (å®è¡)] ãã¯ãªãã¯ããŸãã
ã¯ãšãªçµæã«ã¯ãåèšè¡æ°ãšãã®æšªã«æ°åã衚瀺ãããŸãã
- [Query Editor (ã¯ãšãªãšãã£ã¿ãŒ)] ã¿ãã«æ»ããã¯ãšãªã次ã®ããã«å€æŽããŸãã
SELECT COUNT(Id) FROM Account
-
[å®è¡] ãã¯ãªãã¯ããŸãã
ä»åºŠã¯ãã¯ãšãªçµæã«ã¯ 1 è¡ã®ã¿ãè¿ãããã¬ã³ãŒãã®åèšæ°ã瀺ãåã衚瀺ãããŸãã
ãããŸã§ SOQL ã¯ãšãªã§è¿ãããããŒã¿ã®åŠçã«ã€ããŠã¯åãäžããŠããŸããã§ããããé¿ããŠéãããã«ã¯ãããŸãããå°ã é¢åã§ãããéèšããŒã¿ã®åŠçã«åãããããŸãããã
Apex ãå°ã䜿çšããŸãããå®å šã«çè§£ã§ããªããŠãå¿é ã¯ãããŸãããåŸã»ã©è©³ãã説æããŸãã
- éçºè
ã³ã³ãœãŒã«ã§ã[Debug (ãããã°)] ãéžæãã[Open Execute Anonymous Window (å®è¡å¿åãŠã£ã³ããŠãéã)] ãéžæããŸãã
- æ¢åã®ã³ãŒããåé€ããæ¬¡ã®ã¹ãããããæ¿å
¥ããŸããGROUP BY å¥ãšå ±ã«åèšã衚ãå¥åã䜿çšãããŠããããšã«æ³šç®ããŠãã ãããSOQL ã§ã¯ãGROUP BY å¥ã䜿çšããéèšã¯ãšãªå ã®é ç®ã®ã¿å¥åãæå®ã§ããŸãã
List<AggregateResult> results = [ SELECT Industry, count(Id) total FROM Account GROUP BY Industry ]; for(AggregateResult ar : results) { System.debug('Industry: ' + ar.get('Industry')); System.debug('Total Accounts: ' + ar.get('total')); }
- [Open Log (ãã°ãéã)] ãéžæãããŠããããšã確èªãã[Execute (å®è¡)] ãã¯ãªãã¯ããŸãã
ã¿ããèªã¿èŸŒãŸããå®è¡ãã°ã衚瀺ãããŸãã
- ãã°å
ã®ãããã°ã¹ããŒãã¡ã³ãã®ã¿ã衚瀺ãããããã«ã[Debug Only (ãããã°ã®ã¿)] ãéžæããŸãã
ããã²ãšããš...
GROUP BY å¥ä»¥å€ã«ããSOQL ã«ã¯ GROUP BY ROLLUPãGROUP BY CUBEãGROUPING ãªã©ã®ã°ã«ãŒãåå¥ããããŸãããããã®å¥ã¯ãã¯ãšãªãããã€ãã®é¢é£ããŒãã«ããå€ãè¿ãå Žåã«ãçµæã調ã¹ãã®ã«äŸ¿å©ã§ããGROUP BY CUBE ã¯ãã¯ãšãªçµæã®ã°ã«ãŒãåé ç®ã®ãã¹ãŠã®çµã¿åããã«ã€ããŠå°èšã远å ãã䟿å©ãªå¥ã§ãããããã®å¥ã«ã€ããŠã®è©³çްã¯ãããªãœãŒã¹ãã® GROUP BY ã«ã€ããŠã®ããã¥ã¡ã³ããåç §ããŠãã ããã
éèšé¢æ°ã¯çç¥å¯èœãª HAVING å¥ããµããŒãããŠããŸãããã㯠SQL Server ã® HAVING å¥ã«äŒŒãŠãããããåé¡ãªã䜿çšã§ããã§ããããåºæ¬çã«ãéèšé¢æ°ã§è¿ãããçµæãçµã蟌ãããã®ãã®ã§ãã詳现ã¯ãããªãœãŒã¹ããåç §ããŠãã ããã
ãªãœãŒã¹
- SOQL ããã³ SOSL ãªãã¡ã¬ã³ã¹: SOQL SELECT ã®æ§æ
- SOQL ããã³ SOSL ãªãã¡ã¬ã³ã¹: Count ããã³ Count(fieldName)
- Apex éçºè ã¬ã€ã: SOQL ã¯ãšãªãš SOSL ã¯ãšãª
- SOQL ããã³ SOSL ãªãã¡ã¬ã³ã¹: GROUP BY