Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

How can I Join two tables? 
 
centerax
Sr. Member
 
Avatar
Total Posts:  90
Joined:  2008-09-10
Montevideo, Uruguay
 

Hi,

I’ve been trying to do a JOIN between two tables and couldn’t so far.

My table has a foreign_key, “product_id” and I’d like to do a JOIN with the Magento producst table.

Anyone knows?

Thank you!

 
Magento Community Magento Community
Magento Community
Magento Community
 
nikefido
Guru
 
Avatar
Total Posts:  481
Joined:  2008-07-11
New Haven, CT
 

You’re own custom table?

 
Magento Community Magento Community
Magento Community
Magento Community
 
centerax
Sr. Member
 
Avatar
Total Posts:  90
Joined:  2008-09-10
Montevideo, Uruguay
 

I’d like to fetch a collection from my table and do a join with magento products table and get the product for every row of my table, my table is from a new module.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brandlive_br
Guru
 
Avatar
Total Posts:  653
Joined:  2008-05-19
Buenos Aires, Argentina
 

Hi, this is a really simple example.
I hope that you can understand how to apply.

<?php
protected function _prepareCollection()
{
    $collection 
Mage::getModel('yourmodule/yourmodel')->getCollection();
    
$collection->getSelect()
                ->
join(array('your_table_alias' => 'your_table_name'),
                    
'main_table.reference_id = your_table_alias.reference_id',
                    array(
'your_table_field_1','your_table_field_2','your_table_field_N'))
                ->
where('your_table_alias.filter_field = 1')
    ;
    
$this->setCollection($collection);
    return 
parent::_prepareCollection();
}
?>
 
Magento Community Magento Community
Magento Community
Magento Community
 
centerax
Sr. Member
 
Avatar
Total Posts:  90
Joined:  2008-09-10
Montevideo, Uruguay
 

Thanks Damián,
I’m sorry but I couldn’t get to work the piece of code you provide me. :(

I have a table (entity) with a foreign-key that is the “product_id”, so when I fetch a collection of my table I want to do a Join so I get the product identified by the product_id on my table.

Can you help me?

Thanks in advance!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brandlive_br
Guru
 
Avatar
Total Posts:  653
Joined:  2008-05-19
Buenos Aires, Argentina
 

The tables that you’re trying to join, are entities inside Magento or just tables inside your database?

 
Magento Community Magento Community
Magento Community
Magento Community
 
centerax
Sr. Member
 
Avatar
Total Posts:  90
Joined:  2008-09-10
Montevideo, Uruguay
 

entities inside magento

 
Magento Community Magento Community
Magento Community
Magento Community
 
centerax
Sr. Member
 
Avatar
Total Posts:  90
Joined:  2008-09-10
Montevideo, Uruguay
 

I’m getting an error that says that it cant find the foreign key in my table :s

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brandlive_br
Guru
 
Avatar
Total Posts:  653
Joined:  2008-05-19
Buenos Aires, Argentina
 

Can you post your code?

 
Magento Community Magento Community
Magento Community
Magento Community
 
LeeSaferite
Guru
 
Avatar
Total Posts:  322
Joined:  2007-08-31
Lake City, FL
 

Not sure if this helps you any

$collection Mage::getModel('catalog/product')->getCollection();
    
$collection->joinTable('yourmodule/yourmodel''product_id=entity_id', array('col1'=>'col1''col2'=>'col2')) ;
 
Magento Community Magento Community
Magento Community
Magento Community
 
Rinku
Member
 
Total Posts:  41
Joined:  2008-09-17
New Delhi
 

Hi,

I have this same problem to join two tables. these are simple table and not have entity in magento database. I have a order_master table that has all order information and second is order_address table that has address information corresponding to the order. Now I want to show address with order listing. the common field name is order_no. I used this code

$collection = Mage::getModel(’buyminute/ordermaster’)->getCollection();
$collection->getSelect()
->join(array(’address’ => ‘order_address’),
‘order_master.order_no = address.order_no’,
array(’address’,’order_no’))
;

$this->setCollection($collection);
return parent::_prepareCollection();

It is giving me error that: Column not found: 1054 Unknown column ‘order_master.order_no’ in ‘on clause’
Which order_no column is exist in order_master table and it is a primary key.

Could you please correct it.

Thanks
Sandeep Jain

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top