014_SOQL 和 SOSL

1.SOQL: 和Sql 相似，但是更多的是限制，像Join 就不能够直接使用的，但在SOQL中融合了更多的对象（父子）间的关系，这个关系就能够代替join

 1）按照Lastname升序排，Null 在最前面

SELECT AccountId,Email,Id,LastName FROM Contact
  WHERE Email LIKE '%.net%' ORDER BY LastName ASC NULLS FIRST

　2）现在看两个例子，来具体分析下join在sql 和 soql 之间的使用方法

SQL：SELECT c.FirstName, c.LastName, a.Name FROM Account a
RIGHT JOIN Contact c ON (c.AccountId = a.Id)

　　因为在soql中没有办法使用join，但可以明了的确定Account 和 Contact 之间的数据关系，即父子关系，是先有的Account，最后有Contact，每条contact都要指定一个Account。

SELECT FirstName, LastName, Account.Name FROM Contact

　　特别提醒:这里的Account.Name 的Account 指的是 Account的API Name, 如果是自定的Object，就应该使用 Custome__r.Name

接收值的写法：

Account[] acctsWithContacts = [SELECT Name, (SELECT FirstName,LastName FROM Contacts)
                               FROM Account 
                               WHERE Name = 'SFDC Computing'];
// Get child records
Contact[] cts = acctsWithContacts[0].Contacts;
(类似于java的写法)System.debug('Name of first associated contact: ' + cts[0].FirstName + ', ' + cts[0].LastName);

Contact[] cts = [SELECT Account.Name FROM Contact WHERE FirstName = 'Carol' AND LastName='Ruiz'];

Contact carol = cts[0];

String acctName = carol.Account.Name;

System.debug('Carol\'s account name is ' + acctName);

　　接下来说下SOSL接收数据的写法：先创建几条测试数据

// Add account and related contact
Account acct = new Account(
    Name='SFDC Computing',
    Phone='(415)555-1212',
    NumberOfEmployees=50,
    BillingCity='San Francisco');
insert acct;

// Once the account is inserted, the sObject will be 
// populated with an ID.
// Get this ID.
ID acctID = acct.ID;

// Add a contact to this account.
Contact con = new Contact(
    FirstName='SFDC',
    LastName='Ruiz',
    Phone='(415)555-1212',
    Department='Wingo',
    AccountId=acctID);
insert con;

// Add account with no contact
Account acct2 = new Account(
    Name='The SFDC Query Man',
    Phone='(310)555-1213',
    NumberOfEmployees=50,
    BillingCity='Los Angeles',
    Description='Expert in wing technologies.');
insert acct2;

　　创建 account和contact的关系数据，

FIND {SFDC} IN ALL FIELDS RETURNING Account(Name,Phone), Contact(FirstName,LastName,Department)

　　

个人理解SOSL，返回的结果是多个Object，并且是模糊的查询，不同于上文中soql 中的child  to parent And Parent to child

Sosl 查询结果接收方式：

List<List<sObject>> searchList = [FIND 'SFDC' IN ALL FIELDS 
                   RETURNING Account(Name,Phone),Contact(FirstName,LastName,Department)];
Account[] searchAccounts = (Account[])searchList[0];
Contact[] searchContacts = (Contact[])searchList[1];

System.debug('Found the following accounts.');
for (Account a : searchAccounts) {
    System.debug('Account:::'+a.Name);
}

System.debug('Found the following contacts.');
for (Contact c : searchContacts) {
    System.debug('Contact:::'+c.LastName + ', ' + c.FirstName);
}

　　

Note:

• External objects don’t support the ORDER BY clause in relationship queries.
• 如果数据量 太大 就不能使用 Like，然后用for 处理，容易导致 Timeout 
• 大忌：不能将 查询语句写到for 里面

SELECT c.Id, c.firstname, " +
"c.lastname, c.leadsource, a.Id, a.name, a.industry, c.accountId " +
"FROM Contact c, c.account a ORDER BY leadsource LIMIT 10

 

 

 参考：https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm