0

I have the following collection which helps implement a custom report.

<?php


class VMR_Customreports_Model_Resource_Productsordered_Collection
    extends Mage_Reports_Model_Resource_Order_Collection
{
    public function __construct()
    {
        parent::__construct();
        $this->_init('sales/order_item');
    }

    public function setDateRange($from, $to)
    {
        $this->_reset();
        $this->getSelect()
            ->joinInner(
                array('i' => $this->getTable('sales/order_item')),
                'i.`order_id` = main_table.`entity_id`'
            )
            ->where("main_table.created_at BETWEEN '" . $from . "' AND '" . $to . "'")
            ->where('main_table.status != \'canceled\'')
            ->where('main_table.customer_email NOT LIKE \'%@v2cigs.%\'')
            ->where('i.parent_item_id IS NULL')
            ->columns(array(
                'myname' => 'i.name',
                'ordered_qty' => 'sum(i.qty_ordered)',
                'ordered_money' => 'sum(i.`price`*i.qty_ordered)'))
            ->group('i.name');

        return $this;
    }

    public function setStoreIds($storeIds)
    {
        if ($storeIds)
            $this->addAttributeToFilter('i.store_id', array('in' => (array)$storeIds));

        return $this;
    }

}

When I login as full admin, it selects the right data but when I login as a more restricted user I get an error about, "Integrity constraint violation: 1052 Column 'store_id' in where clause is ambiguous".
I can understand that it's talking about store_id being present both in the order_item table and in the order table, but I am specifying the table to pull from when using store_id.

Furthermore, the error happens right at the join. If I comment out the store_id code I still get the error. Why?

1

If you are using the Enterprise Edition, the AdminGws module is applying store scope filters so admins are only able to access data they are authorized to see.

Also, you probably want to remove your __construct() method. See below for some additional information.

Background:

You are extending from Mage_Reports_Model_Resource_Order_Collection. This class uses the sales/order table (that is, sales_flat_order as the main table.

 $this->getSelect()->from(array('main_table' => $this->getMainTable()));

The main table is actually fetched from the associated resource model:

public function getMainTable()
{
    if ($this->_mainTable === null) {
        $this->setMainTable($this->getResource()->getMainTable());
    }

    return $this->_mainTable;
}

So the resource model is responsible to supply the main table name.
Mage::getResourceModel($this->getResourceModelName()) is used to collect the resource model.
So what does getResourceModelName() return?
It returns the value that was set by the _init() call.

protected function _init($model, $resourceModel = null)
{
    $this->setModel($model);
    if (is_null($resourceModel)) {
        $resourceModel = $model;
    }
    $this->setResourceModel($resourceModel);
    return $this;
}

In your class, you are overriding the _init() call of the parent class:

$this->_init('sales/order_item');

In effect, you are specifying that your collection should use the order item resource model, which will return the table sales/order_item as the main table.
Because of your _init() call you are joining the sales_flat_order_item table with itself.
Fixing that by removing the _init() call, or in fact the whole __construct() override, will probably fix that issue for you.

  • I totally agree with you about the _init() not making sense but it seemed to work with it and I probably got it from some guide somewhere. But more importantly, even when I comment out the constructor entirely, I still get the store_id error. And yes I am using the enterprise edition. – user2045 Jun 19 '13 at 14:06
  • As I've said briefly at the beginning of the post, check the Enterprise_AdminGws module so see which additional filters are added for restricted admin users. You can also turn on logging in lib/Varien/Db/Pdo/Mysql.php (set $_debug and $_logAllQueries to true) to see the resulting queries. – Vinai Jun 19 '13 at 14:16
  • I'm assuming you mean lib/Varien/Db/Adapter/Pdo/Mysql.php since I dont have lib/Varien/Db/Pdo/Mysql.php. Anyway, when I do that and tail -f my log I dont see anything probably because it recognizes the statement error before it even tries to run it. I will play with that module to see what I can do. Thanks. – user2045 Jun 19 '13 at 16:31
  • Ok, sure enough I see it adding store_id in a where clause. How do I make that module use table prefixes? – user2045 Jun 19 '13 at 16:41
  • I have further confirmed that it is admingws because when I comment out the contents of it's Collection.php's addStoreAttributeToFilter() I see my query without that storeid where clause. The only problem is, it also fails to load when i do that. :( – user2045 Jun 19 '13 at 16:55
0

If I do this:

public function addStoreAttributeToFilter($collection)
{
    if($collection->getModelName() == 'sales/order' || $collection->getModelName() == 'sales/order_item')
    {
        $collection->addFieldToFilter('main_table.store_id', array('in' => $this->_role->getStoreIds()));
    }
    else
    {
        $collection->addAttributeToFilter('store_id', array('in' => $this->_role->getStoreIds()));
    }
}

in AdminGws/Model/Collections.php

and in my collection create a constructor which does $this->_init('sales/order_item') it works.

The constructor part makes no sense to me but I will be asking about that separately.

0

Today I faced a similar issue. Turning on $_logAllQueries as @Vinai suggested showed that Enterprise_AdminGws was filtering my collection by store_id. I fixed it by adding:

$collection->addFilterToMap('store_id', 'main_table.store_id');

just before the $this->setCollection($collection);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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