如何在Magento中使用joinTable或joinField选择具有别名的特定字段 [英] How to select specific fields with aliases using joinTable or joinField in Magento

问题描述

我想在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.

因此，我正在修改Mage_Adminhtml_Block_Sales_Invoice_Grid :: _ prepareCollection方法，以便它首先获取已登录管理员所推荐的客户.然后它将从这些客户那里获取订单-理想情况下仅是订单ID-然后将此集合加入 sales/order_invoice_grid ，以获取要为此管理员列出的发票.

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.

根据最后一个答案，并使用这些文档，以下是我的三种方法尝试加入此信息:(代码示例1)

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()

如果我删除对joinTable的第二次调用，则上面的代码有效，但这不是我想要的.

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('*'));

此处错误出现在第二行，我实际上是在尝试对字段order.entity_id进行别名化，以使其与发票表Entity_id不冲突.但是，这会产生如下错误:

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:

项目(Mage_Customer_Model_Customer) 相同ID"1"已经存在

Item (Mage_Customer_Model_Customer) with the same id "1" already exist

我只需要订单ID，这样我就可以获取相关的发票，这表明我也可以使用joinField函数，我尝试如下操作:

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');

但这给了我以下错误:

具有相同ID"1"的项目(Mage_Customer_Model_Customer)已经存在

Item (Mage_Customer_Model_Customer) with the same id "1" already exist

我正在寻找一种将客户->发票合并在一起的解决方案.

I am looking for a solution that joins customer->invoices.

通过预过滤器，我的意思是甚至在网格中没有显示任何内容之前，都会对网格中列出的数据进行过滤.

好，现在我的代码如下:

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'));

我得到的错误是:

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'

---

推荐答案

这是我的测试脚本的总数.要将其放在Magento根目录下的文件中并直接在浏览器中键入URL，Magento的控制器不会处理它.这是一种实验的好方法，因为它不受其他模块，页面布局等的影响.

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.

这篇关于如何在Magento中使用joinTable或joinField选择具有别名的特定字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！