Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We've moved the forum!

Please use for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

E-Commerce Modules /

Discuss about the various e-commerce modules available:
Ecommerce, SS Shop, SilverCart and SwipeStripe
Alternatively, have a look the shared mailinglist.

Moderators: martimiz, Nicolaas, Sean, Ed, frankmullenger, biapar, Willr, Ingo, Jedateach, swaiba

counting items sold (database query question)

Go to End

2 Posts   1951 Views

Bruce B

Community Member, 164 Posts

3 March 2015 at 4:45pm

I want to use SS Shop to sell places at workshops. Its important that we don't oversell the workshops so I need to know how many places have been sold. Thats where I'm having problems.

The table OrderItem has the quantity,
Product_OrderItem extends OrderItem and contains the ProductID which identifies the workshop
Order contains the Status which tells me if the order has been paid
Order has a has-many relation with OrderItem
I'm sitting on the workshop page when I make the query so $this->ID gives me the ProductID.

Given all these elements, how do I construct a query that tells me how many of the current workshop 'products' exist in orders with a status of 'Paid'?


Bruce B

Community Member, 164 Posts

16 March 2015 at 5:34pm

Edited: 16/03/2015 5:34pm

I have been able to answer my own question for this one, courtesy of the very clear SilverStripe lessons. The following function gives me the number of sold items in paid invoices:

public function Paid() {
$StockItems = Product_OrderItem::get()->filter(array('ProductID' => $this->ID));
foreach ($StockItems as $Stockitem) {
	if ($Stockitem->Order()->Status=='Paid') {
	$StockTotal += $Stockitem->Quantity;
return $StockTotal;