You do a sub-select, e.g:
Select Amount,CloseDate,Name, (Select PricebookEntry.Product2Id, TotalPrice, UnitPrice, ListPrice From OpportunityLineItems) From Opportunity WHERE StageName = 'Closed' ORDER BY Amount DESC NULLS LAST
Opportunity Products (OpportunityLineItem) is a Detail to the Master Opportunity, i.e. 1:n between Opportunity and Product lines. Products belong to a Pricebook, thus you have to traverse the PricebookEntry relationship to retrieve the Product ID (if you need it).
You can also query having OpportunityLineItem as the Primary object and referencing Opportuinty via the Opportunity relationship:
Select Select PricebookEntry.Product2Id, TotalPrice, Opportunity.Amount, Opportunity.CloseDate, Opportunity.Name From OpportunityLineItem WHERE Opportunity.StageName = 'Closed' ORDER BY Opportunity.Amount
You can't go directly from Product2 to Opportunity because there is no direct relationship between the two, i.e. the relationship is via PricebookEntry.
A useful tool in discovering relationships between objects is to use the Force.com Eclipse plugin. You can double click on the salesforce.schema item and it will open a GUI for building queries:
Absolutely there is. Use the following syntax:
// write your query string exactly as in a SOQL statement minus the square-brackets
string queryString = 'SELECT id,Name,OpportunityId FROM Account WHERE OpportunityId!=null LIMIT 5';
// then use database.query() method.
// Note its return value is a list<sObject> though you can cast it appropriately
list<sObject> sAccountList=database.query(queryString);
list<Account> accountList=(list<Account>)database.query(queryString);
You can also reference instance variables, but not any variable of an instance
set<id> myIdSet;
string queryString = 'SELECT id,Name FROM Account WHERE id IN:myIdSet';
list<Account> accountList = (list<Account>)database.query(queryString);
// XXXXXXXX The following WONT work X_X
map<id,Account> accountMap = new map<id,Account>(accountList);
string anotherQueryString = 'SELECT id,Name FROM Account WHERE id IN:accountMap.keyset()'; // X_X WILL NOT WORK
list<Account> emptyAccountList = (list<Account>)database.query(anotherQueryString);
system.assert(emptyAccountList.isEmpty());
And since you mentioned there being no equivalant to SELECT * FROM ... in SOQL, here's some code I've written to get around that. You'll need to dig into the code to see how it all works. It's very handy when starting a project.
//================= QUERY UTIL METHODS =================================
//################# Format Fields For Query ###########################################
public static string FormatFieldsForQuery(sObject M, string prefix){
if(string.isNotBlank(prefix)){
if(prefix.endsWith('__c'))
prefix=prefix.replace('__c','__r.');
if(!prefix.endsWith('.'))
prefix+='.';
}
string fields = '';
for(string f : M.getSObjectType().getDescribe().fields.getMap().keySet())
fields+=prefix+f+',';
return fields.removeEnd(',');
}//END FormatFieldsForQuery(sObject M, string prefix)
public static string FormatFieldsForQuery(sObject M){
return FormatFieldsForQuery(M,'');
}//END FormatFieldsForQuery(sObject M)
public static string FormatFieldsForQuery(string objectName){
return FormatFieldsForQuery(objectName,'');
}//END FormatFieldsForQuery(string objectName)
public static string FormatFieldsForQuery(string objectName,string prefix){
if(string.isNotBlank(prefix)){
if(prefix.endsWith('__c'))
prefix=prefix.replace('__c','__r.');
if(!prefix.endsWith('.'))
prefix+='.';
}
string fields = '';
sObjectType objectType=Schema.getGlobalDescribe().get(objectName);
if(objectType==null)
return fields;
for(string f :objectType.getDescribe().fields.getMap().keySet())
fields+=prefix+f+',';
return fields.removeEnd(',');
}//END FormatFieldsForQuery(string objectName,string prefix)
public static string FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix){
if(string.isNotBlank(prefix)){
if(prefix.endsWith('__c'))
prefix=prefix.replace('__c','__r.');
if(!prefix.endsWith('.'))
prefix+='.';
}
string fields = '';
for(string f : m.keySet())
fields+=prefix+f+',';
return fields.removeEnd(',');
}//END FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix)
public static string AllFieldsQuery(string objectName){
return 'SELECT '+FormatFieldsForQuery(objectName)+' FROM '+objectName+' ';
}//END AllFieldsQuery
public static string AllFieldsQuery(sObject s){
return 'SELECT '+FormatFieldsForQuery(s)+' FROM '+s.getSObjectType().getDescribe().getName()+' ';
}//END AllFieldsQuery
Test Code:
//################# Format Fields For Query ###########################################
@isTest private static void testFormatFieldsForQuery(){
// Test order (depending upon input:
//
// #1 (sObject M, string prefix)
// #2 (sObject M)
// #3 (string objectName)
// #4 (string objectName,string prefix)
// #5 (map<String,Schema.SObjectField> m,string prefix)
// #6 AllFieldsQuery(string objectName) <---- different method name ----
///////////////////////////////////////////////////////////////////////////
test.startTest();
//test Account Standard Fields
Account a=new Account();
assertFFFQ(Portal.FormatFieldsForQuery(a,''),fffqTestObj.Account);
assertFFFQ(Portal.FormatFieldsForQuery(a),fffqTestObj.Account);
assertFFFQ(Portal.FormatFieldsForQuery('Account'),fffqTestObj.Account);
assertFFFQ(Portal.FormatFieldsForQuery('Account',''),fffqTestObj.Account);
assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Account.fields.getMap(),''),fffqTestObj.Account);
assertFFFQ(Portal.AllFieldsQuery('Account'),fffqTestObj.Account);
//test Contact Standard Fields
Contact c=new contact();
assertFFFQ(Portal.FormatFieldsForQuery(c,''),fffqTestObj.Contact);
assertFFFQ(Portal.FormatFieldsForQuery(c),fffqTestObj.Contact);
assertFFFQ(Portal.FormatFieldsForQuery('Contact'),fffqTestObj.Contact);
assertFFFQ(Portal.FormatFieldsForQuery('Contact',''),fffqTestObj.Contact);
assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Contact.fields.getMap(),''),fffqTestObj.Contact);
assertFFFQ(Portal.AllFieldsQuery('Contact'),fffqTestObj.Contact);
// bad objectName for
// #3 (string objectName)
// #4 (string objectName,string prefix)
system.assert(string.isBlank(Portal.FormatFieldsForQuery('This shouldn\'t work!!!')));
}//END testFormatFieldsForQuery2
public enum fffqTestObj {Account,Contact}
public static void assertFFFQ(string fieldString,fffqTestObj objName){
set<string> fields=new set<string>(fieldString.removeStart('SELECT ').substringBefore(' FROM').split(','));
if(objName==fffqTestObj.Account){
system.assert(fields.contains('name'));
system.assert(fields.contains('accountnumber'));
system.assert(fields.contains('ownerid'));
system.assert(fields.contains('site'));
system.assert(fields.contains('accountsource'));
system.assert(fields.contains('annualrevenue'));
system.assert(fields.contains('createdbyid'));
system.assert(fields.contains('jigsaw'));
system.assert(fields.contains('description'));
system.assert(fields.contains('numberofemployees'));
system.assert(fields.contains('fax'));
system.assert(fields.contains('industry'));
system.assert(fields.contains('lastmodifiedbyid'));
system.assert(fields.contains('ownership'));
system.assert(fields.contains('parentid'));
system.assert(fields.contains('phone'));
system.assert(fields.contains('rating'));
system.assert(fields.contains('sic'));
system.assert(fields.contains('sicdesc'));
system.assert(fields.contains('tickersymbol'));
system.assert(fields.contains('type'));
system.assert(fields.contains('website'));
}
else if(objName==fffqTestObj.Contact){
system.assert(fields.contains('accountid'));
system.assert(fields.contains('assistantname'));
system.assert(fields.contains('assistantphone'));
system.assert(fields.contains('birthdate'));
system.assert(fields.contains('ownerid'));
system.assert(fields.contains('createdbyid'));
system.assert(fields.contains('jigsaw'));
system.assert(fields.contains('department'));
system.assert(fields.contains('description'));
system.assert(fields.contains('donotcall'));
system.assert(fields.contains('email'));
system.assert(fields.contains('hasoptedoutofemail'));
system.assert(fields.contains('fax'));
system.assert(fields.contains('hasoptedoutoffax'));
system.assert(fields.contains('homephone'));
system.assert(fields.contains('lastmodifiedbyid'));
system.assert(fields.contains('lastcurequestdate'));
system.assert(fields.contains('lastcuupdatedate'));
system.assert(fields.contains('leadsource'));
system.assert(fields.contains('mobilephone'));
system.assert(fields.contains('otherphone'));
system.assert(fields.contains('phone'));
system.assert(fields.contains('reportstoid'));
system.assert(fields.contains('title'));
}
}//END assertFFFQ
Best Answer
You need to first fetch Ids of the Characteristics for Color and Material type. Then you need to fetch ids of the Characteristic values for Black and Solid. Then query the Product characteristic which has these Ids.