Magento joining two tables with different columns

Question

i have to modify an adminhtml section for stock alerts. normally for a single product there's a retrieve of registered customer which have subscribed to alerts by this function:

protected function _prepareCollection()
{
    $productId = $this->getRequest()->getParam('id');
    $websiteId = 0;
    if ($store = $this->getRequest()->getParam('store')) {
        $websiteId = Mage::app()->getStore($store)->getWebsiteId();
    }
    $collection = Mage::getModel('productalert/stock')
        ->getCustomerCollection()
        ->join($productId, $websiteId);
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

this (for what i have understand) make a query on the table product_alert_stock which has the following columns:

alert_stock_id, customer_id, product_id, website_id, add_date, send_date, send_count, status

and then join it with the customer table to retriev customers names.

Well we have created a similar table for guest users (not registered), product_alert_stock_guest, with the following colums:

alert_stock_id, guest_email, product_id, website_id, add_date, send_date, send_count, status

as you can see the difference is the column guest_email which replace customer_id. is it possible to join (or do something similar) these two tables and then display it on the same grid?

thanx

Luca

@JakeTheFish at the end I didn't do a join, but i retrieved guest stock alert objects in a similar of the regular stock alert, i set email field by setData('email', $item->getData('email')), and then i added them to $collection by addItem. I have an error: Mage::exception('Mage_Eav', 'Attempt to add add an invalid object') when i do a log on regular stock alert items, i have a lot of information: http://pastebin.com/z7NpNJrH instead with a custom guest stock alert items, i have this: http://pastebin.com/KKcTeQEH so the problem is that i need a Mage_Customer_Model_Customer instance for the add. i could create one but:

1) i can fill attributes of that instance with normal methods as setField (i'm looking at the API right now and i dont see anything like that)

2) even if i can fill attributes, what happens if i set blank fields?

Jevgeni Smirnov Jevgeni Smirnov · Accepted Answer · 2011-11-30T11:37:39

Magento provides such methods as:

joinAttribute
joinField
joinTable

But joined tables should have some similar id.

Based on these 2 links 1 and 2

Try something like this:

$selectFoo = Mage::getResourceModel('model/foo')->getSelect();
$selectBar= Mage::getResourceModel('model/bar')->getSelect();
$mergedFooBar = $selectFoo->union(array($selectBar));

OR you can add element to collection:

$collection->addItem($item);

BUT, You have 2 different classes and I don't want how they will merge in a single collection, I suppose if you want to unite 2 different entities, you should make those 2 subclass some third class, which negotiates difference between e-mail and customer_id.

Magento joining two tables with different columns

1 Answers