1

i had made a backend module before only select a single table which will be used in a collection, here is the code

    <models>
        <adminlog>
            <class>Rayman_Adminlog_Model</class>            
            <resourceModel>Adminlog_mysql4</resourceModel>
        </adminlog>
        <adminlog_mysql4>
            <class>Rayman_Adminlog_Model_Mysql4</class>
    <entities>
                <adminlog>
                    <table>admin_log</table>
                </adminlog>
            </entities>
        </adminlog_mysql4>
    </models>

but i'm going to made another backend module which will use two tables or more, so my question how can i adjust above code so the collection can use two tables?

  • you are going to use two table in your module? – MeenakshiSundaram R May 30 '14 at 6:43
  • yes....... ...... – hkguile May 30 '14 at 6:46
  • It will depend on what condition or scenario are using the join in. – TBI Infotech May 30 '14 at 7:00
4

The entities child nodes act primarily as alias names for database table names. They can be used within the context of the various database resource models, which take care of adding any database prefix configured in app/etc/local.xml.

Using your example, we'll assume we have an admin_log_note table which has two columns: admin_log_id and note

<models>
    <adminlog>
        <class>Rayman_Adminlog_Model</class>            
        <resourceModel>Adminlog_mysql4</resourceModel>
    </adminlog>
    <adminlog_mysql4>
        <class>Rayman_Adminlog_Model_Mysql4</class>
        <entities>
            <adminlog>
                <table>admin_log</table>
            </adminlog>
            <adminlog_note>
                <table>admin_log_note</table>
            </adminlog_note>
        </entities>
    </adminlog_mysql4>
</models>

If you needed to join the "note" in a collection (e.g. to render a grid) you would do the following:

$collection = Mage::getModel('adminlog/log')->getCollection();
$collection->join(array('log_notes' => 'adminlog/adminlog_note'), 'main_table.id = log_notes.log_id', array('log_notes.note'));

This will create an inner join in the SQL statement generated. It's possible to do different types of joins (e.g. left) by working with the query select object:

$collection->getSelect()->joinLeft(array('log_notes'=> $collection->getTable('adminlog/adminlog_note')), 'main_table.id = log_notes.log_id', array('log_notes.note'));

When working with the select object it's necessary to explicitly get the table name from a magento resource model (in this case the collection model). The Varien_Db_Select class is at a different abstraction level than the Mage_Core_Model_Resource_Db_Collection_Abstract class.

A warning about left joins and collections: It's possible to generate a query which produces multiple rows from the main_table. The abstract collection classes do not support this and will throw an exception. Be sure you understand the results of the query in this context. Many times the query is written with very little data and will appear to function as inteded. Once more data has been populated this issue crops up and catches developers by surprise in production.

  • to beeplogic: if the table is a magento product table which is already defined by magento, so still i need to define like <adminlog_test> <table>catalog_product_entity</table> </adminlog_test> – hkguile May 30 '14 at 8:14
  • @hkinterview - It is not necessary (or recommended) to redefine core tables, you can use their appropriate aliases: array('products' => 'catalog/product'). – beeplogic May 30 '14 at 15:24
3

If you want two join two custom tables in xml :

You would need to supply a second entity entry in your XML to create that second table.

<entities>
    <ness>
        <table>awesome_ness</table>
    </ness>
    <foo>
        <table>awesome_foo<table>
    </foo>
</entities>

Then in your install script, for the second table, you call

$installer->getTable('awesome_ness/foo')

Instead of

$installer->getTable('awesome_ness/ness')

Or If you want to join two custom tables using magento commands:

$collection = Mage::getModel('module/model_name')->getCollection();

//Sytnax 

$collection->getSelect()->join(Mage::getConfig()->getTablePrefix().'table_name_for_join', 'main_table.your_table_field ='.Mage::getConfig()->getTablePrefix().'table_name_for_join.join_table_field',array('field_name_you_want_to_fetch_from_db'));

//Working Query Example

$collection = Mage::getModel('module/model_name')->getCollection();
$collection->getSelect()->join(Mage::getConfig()->getTablePrefix().'catalog_product_entity_varchar', 'main_table.products_id ='.Mage::getConfig()->getTablePrefix().'catalog_product_entity_varchar.entity_id',array('value'));

Hope this will help you

1
<models>
    <adminlog>
        <class>Rayman_Adminlog_Model</class>            
        <resourceModel>Adminlog_mysql4</resourceModel>
    </adminlog>
    <adminlog_mysql4>
        <class>Rayman_Adminlog_Model_Mysql4</class>
        <entities>
            <adminlog>
                <table>admin_log</table>
            </adminlog>
            <newtable> <!-- unique id for entities-->
                <table>your_table</table> <!-- your tabel name-->
            </newtable>
        </entities>
    </adminlog_mysql4>
</models>

then create following files for collection

codepool/yournamespace/yourmodule/Model/Newtable.php codepool/yournamespace/yourmodule/Model/Mysql4/Newtable.php codepool/yournamespace/yourmodule/Model/Mysql4/Newtable/collection.php

you can add more entities(table) like this

  • Mysql4 is deprecated, you should use Resource instead, since Version 1.6 (if your module needs to be backwards compatible, you are right of course :-)) – Fabian Blechschmidt May 30 '14 at 10:28
  • @FabianBlechschmidt thanks for your suggestion. May i know more detail about Mysql4 and Resource advantages and disadvantages – MeenakshiSundaram R May 30 '14 at 10:32
1

Take a look at Mage_Cms_Model_Resource_Page. This Model uses 2 Tables ( cms_page, cms_page_store ).

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.