function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Compass 1Compass 1 

Can we use Select query with where condition inside Apex:Page

Can we use Select query with where condition inside Apex:Page instead of using Apex:Class.
I would like to fetch data from Opportunity for the selected columns using where condition.
Best Answer chosen by Compass 1
Brenda S FinnBrenda S Finn
Ok. I think your code should serve you well to fetch the opportunites with the filter applied. however, your unit test doees not create Opportunities with that field set. So where are the two Opportunities that have Top_Prospects__c set to true? If they are in your org, you will not see them as you do not have SeeAllData=true in your unit test and I dont think you want to set that to true. You would be better served by creating test Opportunities that have that field explicitly set so just add the bolded line to your test code:
  
// Load the test opportunities
List<Opportunity> opps = new List<Opportunity>();
for(Integer i=0;i<200;i++){
Opportunity opp = new Opportunity();
opp.AccountId = acc.Id;
opp.StageName = 'Prospecting';
opp.CloseDate = Date.today();
opp.Top_Prospects__c = true;
opp.Name = 'Test opp ' + i;
opps.add(opp);

}
No, I was not suggesting you call generateReport from your VF page, although I assume you will want to, otherwise what purpose does it serve? I was suggesting that you add a call to it from your test code to ensure you have full code coverage. Something like the line in bold. 
// Test start here
Test.startTest();
topprospectsqry testcls = new topprospectsqry();
List<Opportunity> returned = testcls.getOpportunityList();
System.assertEquals(0,returned.size());
PageReference pgRef = testcls.generateReport();
// Do some assert here to verify pgRef is what you expect. You could just verify it is non-null.
Test.stopTest();
}
}

Finally, what line(s) of code are not being executed in your controller? That would help me to determine where the problem is.

All Answers

goabhigogoabhigo
Hi,
Not exactly where condition, but you can achieve this within VF page using <apex:repeat> tag.. 
<apex:page standardController="Account">
    <!-- Your code/logic here -->
    <apex:repeat value="{!Account.Opportunities}" var="opp">
            <apex:outputField value="{!opp.Amount}" rendered="{!opp.StageName=='Closed Won'}" />
    <!-- Your code/logic here -->
     </apex:repeat>
</apex:page>
Does this help? Let me know.
 
goabhigogoabhigo
Please note that "value" in <apex:repeat> is the Child Relationship Name. To identify this, click on the lookup/master-detail field from the child object, note the name and use it here.

Also above code snippet works only for one Account at a time (and its child Opportunities). If you want to query all opportunities, then I am afraid there is no other way in VF page. You should write query in Apex controller.
Compass 1Compass 1
I have tried as you suggested but now records are populating. The output is coming as blank page.

My requirement is to get the all opportunity by providing the where condition. I have tried through controller but when i running the test I am getting only 60% coverage. Could you advise what more needs to add in my test unit to get more than 75% so I can deploy the changes in Production.

====================================

public class topprospectsqry{

    public Opportunity[] getOpportunityList() {
    Opportunity[] opps = [SELECT
    name,
    account.name,
    Facility__c,
    Scope_Of_Work__c,
    Pricing_Type__c,
    BiddingStatus__c,
    Estimated_Revenue_MM__c,
    Contract_Award_Date__c,
    Project_Go__c,
    Project_Get__c
    FROM
    Opportunity
    where
    Top_Prospect__c='Yes'
    order by Estimated_Revenue_MM__c desc
    LIMIT 20
    ];
    return opps;
    }
     
    public pageReference generateReport() {
    return Page.TopProspectsRpt;
    }
    }

-------------------------------

@isTest

private class topprospecttest{

static testMethod void TestTopProspects(){
 // Load the test data
Account acc = new Account();
acc.Name='Test of the Account1';
acc.type='Home';
insert acc;

// Load the test opportunities
List<Opportunity> opps = new List<Opportunity>();
for(Integer i=0;i<200;i++){
Opportunity opp = new Opportunity();
opp.AccountId = acc.Id;
opp.StageName = 'Prospecting';
opp.CloseDate = Date.today();
opp.Name = 'Test opp ' + i;
opps.add(opp);

}

insert opps;

// Test start here
Test.startTest();
topprospectsqry testcls = new topprospectsqry();
List<Opportunity> returned = testcls.getOpportunityList();
System.assertEquals(0,returned.size());
Test.stopTest();
}
}

=====================================



 
Brenda S FinnBrenda S Finn
What lines of code are not being executed? It appears as if your  test Opportunities do not set the field 
Top_Prospect__c=true so your query will return 0 Opportunities (unless there is a workflow rule or trigger that is setting that field behind the scenes?).

Also you should call generateReport on your StandardController. Then you can do some assertions on what you would expect to be in the returned PageReference object. It is not clear what to expect based on the code you have shared.

 
Compass 1Compass 1
Brenda, Thanks for the response, I have two opportunities where Top_Prospects__c =true
If am i am not misunderstood you meant I should add generateReport on my StandardController in my Apex Page?

Actually I am looking to create one Apex Page to display the selected fields from Opportunity after providing some filters. I just need to do a select query no need to insert and update any record.

Is there a way to fetch records directly while designing the Apex Page instead of using any extension.

As I am new in Apex development due to which I am not having clear idea on available functions.

Hope you got my requirements.
 
Brenda S FinnBrenda S Finn
Ok. I think your code should serve you well to fetch the opportunites with the filter applied. however, your unit test doees not create Opportunities with that field set. So where are the two Opportunities that have Top_Prospects__c set to true? If they are in your org, you will not see them as you do not have SeeAllData=true in your unit test and I dont think you want to set that to true. You would be better served by creating test Opportunities that have that field explicitly set so just add the bolded line to your test code:
  
// Load the test opportunities
List<Opportunity> opps = new List<Opportunity>();
for(Integer i=0;i<200;i++){
Opportunity opp = new Opportunity();
opp.AccountId = acc.Id;
opp.StageName = 'Prospecting';
opp.CloseDate = Date.today();
opp.Top_Prospects__c = true;
opp.Name = 'Test opp ' + i;
opps.add(opp);

}
No, I was not suggesting you call generateReport from your VF page, although I assume you will want to, otherwise what purpose does it serve? I was suggesting that you add a call to it from your test code to ensure you have full code coverage. Something like the line in bold. 
// Test start here
Test.startTest();
topprospectsqry testcls = new topprospectsqry();
List<Opportunity> returned = testcls.getOpportunityList();
System.assertEquals(0,returned.size());
PageReference pgRef = testcls.generateReport();
// Do some assert here to verify pgRef is what you expect. You could just verify it is non-null.
Test.stopTest();
}
}

Finally, what line(s) of code are not being executed in your controller? That would help me to determine where the problem is.
This was selected as the best answer
Compass 1Compass 1
Brenda thanks for your quick response.
As i said I am new Apex Development due to which i am not able to check wich line is not executed in my controller. 
Find below the Apex Code which i have written,

-------------------------------------
public class topprospectsqry{

    public Opportunity[] getOpportunityList() {
    Opportunity[] opps = [SELECT
    name,
    account.name,
    Facility__c,
    Scope_Of_Work__c,
    Pricing_Type__c,
    BiddingStatus__c,
    Estimated_Revenue_MM__c,
    Contract_Award_Date__c,
    Project_Go__c,
    Project_Get__c
    FROM
    Opportunity
    where
    Top_Prospect__c='Yes'
    order by Estimated_Revenue_MM__c desc
    LIMIT 20
    ];
    return opps;
    }
     
    public pageReference generateReport() {
    return Page.TopProspectsRpt;
    }
    }

------------------------------------------------------

To achieve my this page i had written the given class and test unit.

But I am not able to get more than 75% of coverage and cannot deploy my this page in production.

I have created one customized field called "Top_Prospect__c" having drop value "Yes" or "No"

 
Brenda S FinnBrenda S Finn
What IDE are you using? I can help you figure out what line(s) of code are not covered if I know that. Are you using eclipse, sublime/mavensmate or Force.com developer console?

Is the code shown above the entire class being tested and entire unit test? or is there some code that is not displayed?
Compass 1Compass 1
Thanks Brenda,
With your last code reference for my test unit after I have updated I am able to get now 100% coverage and I am able to deploy the page into production. Once again thanks for your great help.
 
Brenda S FinnBrenda S Finn
Great - glad I was able to be of help!