1

Below is some PSEUDO code I am trying to implement for my preparecollection for my Grid.php. Can anyone help me along with this SQL to put in the actual Magento tables? The desired outp

This_Product_Sold_This_Many_Times_In_This_BUNDELD_Product = 10 BundledProduct = 'TheBundledProducts'

This_Product_Sold_This_Many_Times_In_This_GROUPED_Product = 7 GroupedProductName = 'TheGroupedProducts'

This_Product_Sold_This_Many_Times_BY_ITSELF = 25

Select 
    count(ProductID) as This_Product_Sold_This_Many_Times_In_This_BUNDELD_Product,
    Parent.Product_Name as BundledProductName
from 
    Orders o
    join Orders_products op on ( o.orderid = op.orderid )
    join products p on ( p.productid = op.productid )
    join products Parent on (p.parent_product_id = Parent.parent_product_id)
where
    o.CreationDate between Date1 and Date2
    and
    p.productid = $prodcutID
    and 
    Parent.product_type = "Bundled"
Group 
    Parent.'ProductID';


Select 
    count(ProductID) as This_Product_Sold_This_Many_Times_In_This_GROUPED_Product,
    Parent.Product_Name as GroupedProductName
from 
    Orders o
    join Orders_products op on ( o.orderid = op.orderid )
    join products p on ( p.productid = op.productid )
    join products Parent on (p.parent_product_id = Parent.parent_product_id)
where
    o.CreationDate between Date1 and Date2
    and
    p.productid = $prodcutID
    and 
    Parent.product_type = "Grouped"
Group 
    Parent.'ProductID';


Select 
    count(productid) as This_Product_Sold_This_Many_Times_BY_ITSELF 
from 
    Orders o
    join Orders_products op on ( o.orderid = op.orderid )
    join products p on ( p.productid = op.productid )
where
    o.CreationDate between Date1 and Date2
    and
    p.productid = $prodcutID
and 
    not exist (
                Select 
                    p.ProductID
                from 
                    Orders o
                    join Orders_products op on ( o.orderid = op.orderid )
                    join products p on ( p.productid = op.productid )
                    join products Parent on (p.parent_product_id = Parent.parent_product_id)
                where
                    o.CreationDate between Date1 and Date2
                    and
                    p.productid = $prodcutID
                    and 
                    Parent.product_type = "Bundled"
                    p.productid = product.id
                Group 
                    Parent.'ProductID' )

and 
    not exist (
                Select 
                    p.ProductID
                from 
                    Orders o
                    join Orders_products op on ( o.orderid = op.orderid )
                    join products p on ( p.productid = op.productid )
                    join products Parent on (p.parent_product_id = Parent.parent_product_id)
                where
                    o.CreationDate between Date1 and Date2
                    and
                    p.productid = $prodcutID
                    and 
                    Parent.product_type = "Grouped"
                    p.productid = product.id
                Group 
                    Parent.'ProductID' );

Thank you for ANY help towards accomplishing this!

1 Answer 1

2

You might want to start by having a look at: Mage_Reports_Model_Resource_Product_Collection - Not only will this give you better insight into using Magento's methods for working with the database (vs using plain SQL in your code as you seem to be doing), it gives you a generic starting point for getting sales totals for products.

Giving this class a once-over, I'm not noticing anything that distinguishes product sales by product type (whether is was an individual sale vs. part of a bundle, etc), though it looks like you should be able to make your own changes to the addOrderedQty() method to take product type into account by adding a filter that checks if the product has a parent or not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.