Filter Orders based on product_id or user_id(Vendor)

Question

I have followed How to create a custom grid from scratch to create custom Sales Orders. Admin is creating vendors and vendors will be uploading product. I want to restrict vendors such that they can see orders placed on their own product only. There is one new model jbmarketplace/jbmarketplaceproducts in which vendors user_id and product_id is being stored when vendor creates product. But when I'm filtering it gives SQLSTATE[42S22]: Column not found: 1054 Unknown column 'product_id' in 'where clause'. But product_id is available in sales_flat_order_item table.

This problem is Fixed. Updated Code

protected function _prepareCollection()
{
    // Get current logged in user
    $current_user = Mage::getSingleton( 'admin/session' )->getUser();

    // Limit only for vendors
   if ( $current_user->getRole()->getRoleId() == Mage::getStoreConfig( 'jbmarketplace/jbmarketplace/vendors_role' ) ) {
      // echo( $current_user->getUserId());
       $my_products = Mage::getModel( 'jbmarketplace/jbmarketplaceproducts' )
           ->getCollection()
           ->addFieldToSelect( 'product_id' )
           ->addFieldToFilter( 'user_id', $current_user->getUserId() )
           ->load();
       $my_product_array = array();
       foreach ( $my_products as $product ) {
           $my_product_array[] = $product->getProductId();
           $entity = Mage::getModel('sales/order_item')
               ->getCollection()
               ->addFieldToSelect('order_id')
               ->addFieldToFilter('product_id',$my_product_array)
               ->load();
          // echo $entity->getSelect();// will print sql query

       }
       $d=$entity->getData();

       if($d){

           $collection = Mage::getResourceModel('sales/order_collection')
           // My code
             ->addFieldToFilter('entity_id', $d)
        ->join(array('a' => 'sales/order_address'), 'main_table.entity_id = a.parent_id AND a.address_type != \'billing\'', array(
            'city'       => 'city',
            'country_id' => 'country_id'
        ))

         //  ->join(Mage::getConfig()->getTablePrefix().'catalog_product_entity_varchar', 'main_table.products_id ='.Mage::getConfig()->getTablePrefix().'catalog_product_entity_varchar.entity_id',array('value'))
        ->join(array('c' => 'customer/customer_group'), 'main_table.customer_group_id = c.customer_group_id', array(
            'customer_group_code' => 'customer_group_code'
        ))


            ->addExpressionFieldToSelect(
            'fullname',
            'CONCAT({{customer_firstname}}, \' \', {{customer_lastname}})',
            array('customer_firstname' => 'main_table.customer_firstname', 'customer_lastname' => 'main_table.customer_lastname'))
        ->addExpressionFieldToSelect(
            'products',
            '(SELECT GROUP_CONCAT(\' \', x.name)
                FROM sales_flat_order_item x
                WHERE {{entity_id}} = x.order_id
                    AND x.product_type != \'configurable\')',
            array('entity_id' => 'main_table.entity_id')
        )

       ;
         parent::_prepareCollection();
       $this->setCollection($collection);
    return $this;

   }
       else
       {
           echo("Current there are no purchases on your product. Thank you");
       }
   }
   else{
       echo("Please Login as Vendor and you will see orders on your products.<br>");
      // $current_user = Mage::getSingleton( 'admin/session' )->getUser()->getUserId();
      // echo($current_user);
    }

}

Answer 1

Here is the code which worked for me.

protected function _prepareCollection()
{
    // Get current logged in user
    $current_user = Mage::getSingleton( 'admin/session' )->getUser();

    // Limit only for vendors
   if ( $current_user->getRole()->getRoleId() == Mage::getStoreConfig( 'jbmarketplace/jbmarketplace/vendors_role' ) ) {
      // echo( $current_user->getUserId());
       $my_products = Mage::getModel( 'jbmarketplace/jbmarketplaceproducts' )
           ->getCollection()
           ->addFieldToSelect( 'product_id' )
           ->addFieldToFilter( 'user_id', $current_user->getUserId() )
           ->load();
       $my_product_array = array();
       foreach ( $my_products as $product ) {
           $my_product_array[] = $product->getProductId();
           $entity = Mage::getModel('sales/order_item')
               ->getCollection()
               ->addFieldToSelect('order_id')
               ->addFieldToFilter('product_id',$my_product_array)
               ->load();
          // echo $entity->getSelect();// will print sql query

       }
       $d=$entity->getData();

       if($d){

           $collection = Mage::getResourceModel('sales/order_collection')
           // My code
             ->addFieldToFilter('entity_id', $d)
        ->join(array('a' => 'sales/order_address'), 'main_table.entity_id = a.parent_id AND a.address_type != \'billing\'', array(
            'city'       => 'city',
            'country_id' => 'country_id'
        ))

         //  ->join(Mage::getConfig()->getTablePrefix().'catalog_product_entity_varchar', 'main_table.products_id ='.Mage::getConfig()->getTablePrefix().'catalog_product_entity_varchar.entity_id',array('value'))
        ->join(array('c' => 'customer/customer_group'), 'main_table.customer_group_id = c.customer_group_id', array(
            'customer_group_code' => 'customer_group_code'
        ))


            ->addExpressionFieldToSelect(
            'fullname',
            'CONCAT({{customer_firstname}}, \' \', {{customer_lastname}})',
            array('customer_firstname' => 'main_table.customer_firstname', 'customer_lastname' => 'main_table.customer_lastname'))
        ->addExpressionFieldToSelect(
            'products',
            '(SELECT GROUP_CONCAT(\' \', x.name)
                FROM sales_flat_order_item x
                WHERE {{entity_id}} = x.order_id
                    AND x.product_type != \'configurable\')',
            array('entity_id' => 'main_table.entity_id')
        )

       ;
         parent::_prepareCollection();
       $this->setCollection($collection);
    return $this;

   }
       else
       {
           echo("Current there are no purchases on your product. Thank you");
 }
   }
   else{
       echo("Please Login as Vendor and you will see orders on your products.<br>");
      // $current_user = Mage::getSingleton( 'admin/session' )->getUser()->getUserId();
      // echo($current_user);
    }
}