How to select specific fields with aliases using joinTable or joinField in Magento
I want to pre-filter* data in the invoice grid visible in Magento's admin panel.
Here is a question that I asked earlier, and this one is related to the solution presented for that, hence it might act as a good explanation.
So, I am modifying the Mage_Adminhtml_Block_Sales_Invoice_Grid::_prepareCollection method so that it first fetches customer referred by the logged in admin. Then it will fetch orders from these customer(s) - ideally only the order id's - Then join this collection to sales/order_invoice_grid, to get invoices to be listed for this admin.
Based on the last answer and using these docs, following are 3 ways I have tried joining this information: (Code Sample 1)
$collection = Mage::getResourceModel('customer/customer_collection');
$collection->joinTable('sales/order_grid', 'customer_id=entity_id', array('*'));
$collection->joinTable('sales/invoice_grid', 'order_id=main_table.entity_id', array('*'));
When I do the above, I see the following error:
A joint field with this alias (0) is already declared.
#0 /var/www/magento/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(706): Mage::exception('Mage_Eav', 'A joint field w...')
#1 /var/www/magento/app/code/local/Myproject/Adminhtml/Block/Sales/Invoice/Grid.php(41): Mage_Eav_Model_Entity_Collection_Abstract->joinTable('sales/invoice_g...', 'order_id=main_t...', Array)
#2 /var/www/magento/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(576): Myproject_Adminhtml_Block_Sales_Invoice_Grid->_prepareCollection()
#3 /var/www/magento/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(582): Mage_Adminhtml_Block_Widget_Grid->_prepareGrid()
If I remove the second call to joinTable, the above code works, but it is not what I want.
The other method I tried is with this code:
$collection = Mage::getResourceModel('customer/customer_collection');
$collection->joinTable('sales/order_grid', 'customer_id=entity_id', array('entity_id as order_entity_id'));
$collection->joinTable('sales/invoice_grid', 'order_id=main_table.entity_id', array('*'));
Here the error appears in the second line, where I am actually trying to alias the field order.entity_id so that it does not conflict with invoice tables entity_id. However that produces an error like:
Item (Mage_Customer_Model_Customer) with the same id "1" already exist
I only need order id's so that I can get related invoices, which suggests that I can also use joinField function, which I tried as follows:
$collection = Mage::getResourceModel('customer/customer_collection');
$collection->joinField('order_entity_id', 'sales/order_grid', 'entity_id', 'customer_id=entity_id' , null, 'left');
But it gives me the following error:
Item (Mage_Customer_Model_Customer) with the same id "1" already exist
I am looking for a solution that joins customer->invoices.
By pre-filter I mean that data listed in the grid is filtered even before anything is presented in the grid.
Ok, now my code looks like:
$collection =
Mage::getResourceModel('customer/customer_collection');
$collection->joinTable('sales/order_grid', 'customer_id=entity_id', array('entity_id' => 'order_entity_id'));
And the error that I get is:
SELECT `e`.*, `sales_flat_order_grid`.`order_entity_id` AS `entity_id` FROM `customer_entity` AS `e`
INNER JOIN `sales_flat_order_grid` ON (sales_flat_order_grid.customer_id=e.entity_id) WHERE (e.entity_type_id = '1') ORDER BY `e`.`created_at` desc, `e`.`created_at` desc LIMIT 20
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_flat_order_grid.order_entity_id' in 'field list'
Answer
Here is the total of my test script. To use put it in a file at the Magento root and type it's URL directly in your browser, it is not handled by Magento's controllers. This is a good way of experimenting as it is not as influenced by other modules, page layouts, etc.
<pre><?php
require 'app/Mage.php';
Mage::app();
$collection = Mage::getResourceModel('customer/customer_collection');
$collection->getSelect()->reset('columns');
$collection->joinTable('sales/order_grid', 'customer_id=entity_id', array('order_entity_id' => 'entity_id'));
$collection->joinTable('sales/invoice_grid', 'order_id=order_entity_id', array('*'));
foreach ($collection as $invoice)
print_r($invoice->debug());
?></pre>
As with your previous question I choose to reset the initial columns because I don't believe in giving the database more work than necessary. However it's not essential, the test still succeeds without it.
If this doesn't work in your installation then we need to consider what that outside influence could be.