Magento join tables

Question

i need make a join costum tables in my query. I need to do a cycle of all products ordered by categories of "entity_id" and join this select if is valid

SELECT *, pps_product_id, MIN(pps_last_unit_price) AS pps_last_unit_price, pps_quantity_product FROM ".Mage::getSingleton('core/resource')->getTableName('purchase_product_supplier')." WHERE pps_last_unit_price > '0' AND pps_last_unit_price != '' AND pps_product_id = ".$data['entity_id']." GROUP BY pps_product_id

and join with SELECT *, COUNT(entity_id) AS conta FROM ".Mage::getSingleton('core/resource')->getTableName('catalog_category_entity_varchar')." WHERE attribute_id = 192 AND entity_id = ".$IDProduto." AND value != '' AND value != '0' if is valid. Anybody can help me? Thanks

edorian · Accepted Answer · 2012-09-24 02:24:42Z

why not:

$orders->getSelect()->join( array('p' => $orders->getResource()->getTable('sales/order_payment')), 'p.parent_id = main_table.entity_id', array('cc_last4' => 'p.cc_last4', 'cc_type' => 'p.cc_type', 'additional_information' => 'p.additional_information', ) );

Max · Accepted Answer · 2011-07-25 10:30:29Z

First of all, generally it is preferred within Magento to use the Magento Models for querying the database. That way your queries will also work on other Databases if you decide to switch (currently unsupported as far as I know, but still its worth it).

I like to use this link or this one for figuring out joins.

Your question is very vague. Can you provide the schema of the custom table? What is being joined with what? On what fields?

To start, I'd say it'll look something like this:

$pps = Mage::getModel('purchase_product_supplier')->getCollection() ->addAttributeToFilter('pps_last_unit_price', array('gt' => 0)) ->addAttributeToFilter('pps_last_unit_price', true) ->addAttributeToFilter('pps_product_id', $data['entity_id']); $pps->getSelect()->join( );

Collectives™ on Stack Overflow

Magento join tables

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related