if joining two tables, is it need to define models with two table tab in module\etc\config.xml?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

<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

Answer 4

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