How to show data from another table based on foreign key?

[tahiryasin]

I have a custom dropdown attribute (vendor_id) for products.

The dropdown is populated from a custom table vendors (entity_id, name). Now with this extension I am able to add Vendor column in products grid that shows vendor_id but I want to display name. And the search should also work on name rather than entity_id.

My gridcontrol.xml

<?xml version="1.0"?>
<gridcontrol>
    <grids>
        <!-- grid id -->
        <productGrid>
            <vendor_id>
                <add>
                    <header>Vendor</header>
                    <type>text</type>
                    <index>vendor_id</index>
                </add>
                <after>name</after>
            </vendor_id>
        </productGrid>
    </grids>
</gridcontrol>

Can you please tell if this already possible or need custom coding?

[Schrank]

This should be possible. You just need to join the table and use the name column

[tahiryasin]

Schrank,
Thanks for your response.

Can you please tell me how to create a join in above described scenario?

[Schrank]

https://github.com/magento-hackathon/GridControl/blob/master/src/app/code/community/FireGento/GridControl/etc/gridcontrol.xml.dist#L79-L86

[tahiryasin]

Here is my updated xml

<?xml version="1.0"?>
<gridcontrol>
    <grids>
        <!-- grid id -->
        <productGrid>      
            <vendor_id>
                <add>
                    <header>Vendor Name</header>
                    <type>text</type>
                    <index>vendor_id</index>
                    <join table="vendormanager_vendor" condition="main_table.entity_id={{table}}.entity_id" field="name"/>
                </add>
                <after>name</after>
            </vendor_id>        
        </productGrid>
    </grids>
</gridcontrol>

Where vendormanager is module name and vendor is entity name defined in module's config.xml
I am not sure if I am doing right.

It shows me below fatal error
Fatal error: Call to undefined method Mage_Catalog_Model_Resource_Product_Collection::join() in /public_html/app/code/community/FireGento/GridControl/Model/Observer.php on line 97

and if I replace join with joinField the errors goes but grid still showing id instead of name.

[Schrank]

Try with joinTable and http://blog.fabian-blechschmidt.de/joining-a-flat-table-on-eav/ may help

[tahiryasin]

Thanks but can you please tell me how to write joinTable in xml ?

I need to show name column from vendors table based on vendor_id that is a custom product attribute and primary key of vendors table.

[Schrank]

No, not out of the mind, need to dig the code for it and don't have time. Sorry.

But we need to document this.

[tahiryasin]

Yes, please document the XML join syntax so that people who use this extension know how to use it.

Looking forward for an update. Thank you!