dynamic SOQL With group by clause(very urgent)

hi,

i wanna a use group by clause in my Soql but we need to do in dynamically. is it possible

i am getting fields and object and condition are dynamically.

now i need to add group by in that query which is selected by the user.

is it possible with dynamic soql.

Regards

Ashok chandra

April 18, 2012
·
Answer
·
Like
0
·
Follow
0

TheIntegrator
yes, its possible, cosider the following example

String condQueryO='SELECT SUM(Amount) FROM Opportunity WHERE Probability >0'; if(true){ condQueryO=condQueryO+' Group By Type'; }else{ condQueryO=condQueryO+' Group By Account'; } List<AggregateResult> amtList=new List<AggregateResult>(); system.debug('@@@ condQueryO '+condQueryO); try{ amtList = Database.query(condQueryO); }catch(Exception e){ system.debug(e); }
Hope it helps.

April 18, 2012
·
Like
0
·
Dislike
0

Navatar_DbSup
HI,

Yes you can do this. Try the below code as reference:
string groupBy='Account.Name';
String con='select Account.Name AccountName,Count(id) from contact group by ' +groupBy;
system.debug('#############' +con);
list<contact> co = database.query(con);

April 18, 2012
·
Like
0
·
Dislike
0

ashchan
These two senarios are happenig when we knew the fileds and we can do group by with those fields.

my senario is

i am displaying all the objects in picklist, when we select one object from picklist i am dispalying all the fields respect to selected object.
Then will select the fields from that picklist as a fields for the soql query...................

Now we need to group by the some other fields in that object

Regards
Ashok

April 18, 2012
·
Like
0
·
Dislike
0

TheIntegrator
you can easily do that with the same code, just change the group by field to a string variable and from the selection, put the values in this variable. If there are more than 1 field to group by, you can make this string a comma separated one.

String groupBy='Type'; String condQueryO='SELECT SUM(Amount) FROM Opportunity WHERE Probability >0'; condQueryO=condQueryO+' Group By '+groupBy;

April 18, 2012
·
Like
0
·
Dislike
0

ashchan
this is my page

<apex:page Controller="Describer">
<apex:form id="Describe">
<apex:pageBlock id="block2" >
<apex:pageblockbuttons location="top" >
<apex:commandButton value="Get Describe Object Fields" action="{!showFields}" />
</apex:pageblockbuttons>
<apex:pageblocksection id="text1" >
<apex:selectList value="{!selectedObject}" size="1">
<apex:selectOptions value="{!objectNames}"/>
</apex:selectList>
</apex:pageblocksection>
<apex:pageblocksection id="fieldList" rendered="{!not(isnull(selectedObject))}">

<apex:selectList value="{!selectedfield}" size="1" multiselect="true">
<apex:selectOptions value="{!filedNames}"/>
</apex:selectList>
</apex:pageblocksection>
<apex:commandButton value=">>" action="{!addfieldsforquery}" rerender="text3"/>

<apex:pageBlockTable value="{!selectedfield}" var="sfield" id="text3">
<apex:column value="{!sfield}"/>

</apex:pageBlockTable>

<apex:commandButton value="runquery" action="{!runquery}"/>
<apex:pageBlockSection >
<apex:pageBlockTable value="{!queryresult}" var="que">

<apex:column value="{!que}"/>

</apex:pageBlockTable>
</apex:pageBlockSection>

</apex:pageBlock>
</apex:form>
</apex:page>

this is my class

public class Describer
{
public List<SelectOption> getFunction() {
selectoption stringcondition1=new selectoption('SUM','SUM');
selectoption stringcondition2=new selectoption('AVG','AVG');
selectoption stringcondition3=new selectoption('MIN','MIN');
selectoption stringcondition4=new selectoption('MAX','MAX');

if(conditions.isEmpty()){
conditions.add(stringcondition1);
conditions.add(stringcondition2);
conditions.add(stringcondition3);
conditions.add(stringcondition4);

}

return conditions;

}

public list<Sobject> queryresult{get;set;}
//public list<AggregateResult> queryresult{get;set;}
public PageReference runquery() {

string soql='select'+' '+ selectedobjectsforquery + ' from ' + selectedObject +' limit 10 ';

try {

system.debug('aftersoqladd'+soql);
queryresult= Database.query(soql);

} catch (Exception e) {
ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
}

return null;
}

public PageReference addfieldsforquery() {

selectedobjectsforquery='';
if(!selectedfield.isEmpty() ){
for(string s:selectedfield)
{
if(s!='')
selectedobjectsforquery+=s+',';

}
}

integer sizes=selectedobjectsforquery.length();
selectedobjectsforquery=selectedobjectsforquery.substring(0,sizes-1);
system.debug('items'+selectedobjectsforquery);
return null;
}
public Map <String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
public List<Pair> lstfieldname{get;set;}
public List <Pair> fields {get{return lstfieldname;} set{lstfieldname =value;}}
public List <SelectOption> objectNames{public get; private set;}
public String selectedObject {get; set;}
public list<String> selectedfield{get;set;}

public List<SelectOption> conditions= new List<SelectOption>();
public String selectedfieldforgroupby{get;set{selectedfieldforgroupby=value;}}
public list<String> selectedfieldsforgroupby{get;set;}
public String selectedobjectsforquery{get;set;}
public List <SelectOption> filedNames{public get; private set;}

//public list<pair1> filedwithfunction{get;set;}

// Intialize objectNames and fields
public Describer() {
objectNames = initObjNames();
fields = new List<Pair>();

//filedwithfunction=new list<pair1>();
selectedfieldsforgroupby=new list<string>();

}

// Populate SelectOption list -
// find all sObjects available in the organization
private List<SelectOption> initObjNames() {
List<SelectOption> objNames = new List<SelectOption>();

List<String> entities = new List<String>(schemaMap.keySet());
entities.sort();
system.debug('valuesofentities'+entities);
for(String name : entities)

objNames.add(new SelectOption(name,name));
return objNames;
}

// Find the fields for the selected object
public void showFields() {
filedNames=new list<SelectOption>();
system.debug('$$$$$' + selectedObject);
Map <String, Schema.SObjectField> fieldMap = schemaMap.get(selectedObject).getDescribe().fields.getMap();
for(Schema.SObjectField sfield : fieldMap.Values())
{
if (sfield.getDescribe().isAccessible() && !sfield.getDescribe().isDefaultedOnCreate() && sfield.getDescribe().isUpdateable()&& sfield.getDescribe().getLabel()!='Languages' && sfield.getDescribe().getLabel()!='Level' && sfield.getDescribe().getLabel()!='Account ID' && sfield.getDescribe().getLabel()!='id' && sfield.getDescribe().getLabel()!='Reports To ID' && sfield.getDescribe().getLabel()!='Email Bounced Date' && sfield.getDescribe().getLabel()!='Email Bounced Reason'){
schema.describefieldresult dfield = sfield.getDescribe();
Pair field = new Pair();
field.key = dfield.getname();
field.val = dfield.getType () + ' : ' + dfield.getLabel ();
lstfieldname.add(field);
filedNames.add(new SelectOption(field.key,field.key));
}
}
}

public class Pair {
public String key {get; set;}
public String val {get; set;}
}

}

here i am getting objects and fields dynamically now i want to add group by in soql......................

April 19, 2012
·
Like
0
·
Dislike
0

TheIntegrator
I don't see the part in the code where you are selecting the field to group with, do you intend to group by the selectedobjectsforquery as well?

April 20, 2012
·
Like
0
·
Dislike
0

ashchan
yes
i want those fields for group by also. we can add 'group by selectedobjectsforquery' to soql string. then we will get aggregate results.......

one more problem what i am facing
Here my problem is how to show the result in page.....

u may suggest wrapper class but we don't know the fields exactly because it may vary (user is selecting the fields from picklist)

This is my problem

sorry if i am asking Non-sence question

April 20, 2012
·
Like
0
·
Dislike
0

TheIntegrator
I'm a bit confised now, so you original issue of group by is now solved right? Now you have a different issue to dynamically display data of various fields that were selected? I think you should post that question under the Visualforce thread and people with better visualforce skills might be able to help you out.