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
emuelasemuelas 

SOQL query comilation error

Hi,


I am trying to display products from a custom pricebook Purchase in a custom controller.

I tried the following three queries and all of them are throwing compilation errors.
The three queries are : 

public  List<product2> pls=new List<product2>();
public price(){

 

//Queries to get the products and their prices from the purchase pricebook
//pls=[select Pricebookentry.unitprice,product2__r.name from pricebook2 where name='Purchase' ];
//pls=[select a.name,b.UnitPrice from product2 a,pricebookentry b,pricebook2 c where a.id=b.Product2Id and b.id=c.id and c.name='Purchase' ];
//pls=[select name,[select unitprice from pricebookentry where pricebook2id in[select id from pricebook2 where name='Purchase']] from product2 ];

 

Please help!

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

 

[select id,unitprice,product2.id,product2.name from pricebookentry where pricebook2.name='Purchase']

PricebookEntry is a many-to-many relationship between Product2 and Pricebook2. Thus, this would be the easiest way to acquire the data you're looking for. However, if you wanted a list of product2, you'd have the slightly more complex query:

 

 

[select id,name,(select id,unitprice from pricebookentries where pricebook2.name='Purchase') from product2
where id in (select product2id from pricebookentry where pricebook2.name='Purchase')]

Use square brackets only for the entire query, not including each subquery. Also, you would have to use the plural name, pricebookentries, since it is a child object.

 

 

In the former method, you reference each variable as:

 

Unit price   = record.UnitPrice
Product Name = record.Product2.Name
Product Id   = record.Product2.Id

 

 

In the latter method, you reference each variable as:

 

Unitprice = record.Pricebookentries[0].UnitPrice
Product Name = record.Name
Product Id = record.Id

 

 

 

All Answers

sfdcfoxsfdcfox

 

[select id,unitprice,product2.id,product2.name from pricebookentry where pricebook2.name='Purchase']

PricebookEntry is a many-to-many relationship between Product2 and Pricebook2. Thus, this would be the easiest way to acquire the data you're looking for. However, if you wanted a list of product2, you'd have the slightly more complex query:

 

 

[select id,name,(select id,unitprice from pricebookentries where pricebook2.name='Purchase') from product2
where id in (select product2id from pricebookentry where pricebook2.name='Purchase')]

Use square brackets only for the entire query, not including each subquery. Also, you would have to use the plural name, pricebookentries, since it is a child object.

 

 

In the former method, you reference each variable as:

 

Unit price   = record.UnitPrice
Product Name = record.Product2.Name
Product Id   = record.Product2.Id

 

 

In the latter method, you reference each variable as:

 

Unitprice = record.Pricebookentries[0].UnitPrice
Product Name = record.Name
Product Id = record.Id

 

 

 

This was selected as the best answer
emuelasemuelas

Thank you so much! This was perfect! Really helped me a lot to understand the Product-Pricebook-Pricebookentry relationships! Thanks a zillion!
emuelasemuelas

Hi,

 

I used the second query:

 

[select id,name,select__c,(select unitprice from pricebookentries where pricebook2.name='Purchase') d  from product2
where id in (select product2id from pricebookentry where pricebook2.name='Purchase')];

 

but iam having an issue in my vf page

 

how do i access unit price?

<apex:pageblocktable var="a" value="{!Recs}" >
<apex:column >
<b><apex:inputCheckbox value="{!a.Select__c}"/> </b>
</apex:column>
<apex:column value="{!a.name}"/>
<apex:column value="{!a.pricebookentries.UnitPrice}"/> This is not working.

</apex:pageblocktable>

 
how do i access this?
sfdcfoxsfdcfox

I would recommend a custom wrapper class, personally, because of the way that VF handles arrays (which is interesting at best). Such a class might look like this:

 

 

public class ItemWrapper {
  private Product2 prod;
  // Constructor
  public ItemWrapper(Product2 p) { prod = p; }

  // Setter methods
  public void setselect(boolean value) { prod.select__c = value; }

  // Getter methods
  public boolean getselect() { return prod.select__c; }
  public string getname() { return prod.name; }
  public string getunitprice() { return prod.pricebookentries[0].unitprice; }
}

 

 

In your page, you can then access them like before:

 

<apex:pageblocktable var="a" value="{!Recs}">
<apex:column><b><apex:inputCheckbox value="{!a.select}"/></b></apex:column>
<apex:column value="{!a.name}"/>
<apex:column value="{!a.unitprice}"/>
</apex:pageblocktable>

The disadvantage is that you have to loop through your result set and "wrap" them into the new list. The upside is that you don't have to access the array members, which would otherwise require you to modify your page like this:

 

 

<apex:pageblocktable var="a" value="{!Recs}" >
<apex:column >
<b><apex:inputCheckbox value="{!a.Select__c}"/> </b>
</apex:column>
<apex:column value="{!a.name}"/>
<apex:column>
<apex:repeat value="{!a.pricebookentries}" var="pricebookentry">
{!pricebookentry.unitprice}
</apex:repeat>
</apex:column>
</apex:pageblocktable>

Since this is an array, you are forced to loop through the results, even though you know only one entry will ever exist in this list. This is the design of Visualforce, so to avoid that problem, a small wrapper class can help alleviate the issue.

 

Edit: I should also add that you can keep the query results in your viewstate. Since the constructor is taking a reference to a variable, you're not duplicating memory, and when Select__c is modified on the page, this will propagate into your query result list.
emuelasemuelas
Thank You so much
Harting HarrisHarting Harris

Thanks a lot.

It help me a lot.