CODEWITHSUNDEEP
magentojoincollections
ooblek6000
ooblek6000

Reputation: 163

Magento Collections - Resulting SQL Queries and How To Specify INNER/OUTER Joins

As a relatively new user of Magento and an even newer user of SQL, this question could be a dumb one.

I'm simply having a very hard time formulating an SQL query with the Magento API. The query I'm attempting to translate into the API is right below.

SELECT b.product_id, e.item_nr
FROM mag1catalog_product_flat_1 AS 'e' 
RIGHT OUTER JOIN 
mag1personal_order AS 'b'
ON b.product_id = e.item_nr
WHERE e.item_nr is null or e.erp_item_nr = '';

What I have so far in terms of a Magento collection query and the resulting SQL is below.

public function importCatalog() {


    $catalogCollect = Mage::getModel('catalog/product')->getCollection();
    $catalogCollect->addAttributeToFilter('item_nr');
    $orderflyCollect->addAttributeToFilter('b.product_id');
    $catalogCollect->getSelect()->joinRight(array('b'=>'mag1personal_order'), 'b.product_id = e.item_nr', 'e.item_nr');

    echo $catalogCollect->getSelect();

SQL Query:

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`item_nr`, `e`.`item_nr` FROM `mag1catalog_product_flat_1` AS `e` RIGHT JOIN `mag1personal_order` AS `b` ON b.product_id = e.item_nr WHERE (e.item_nr = '')

I have a few questions below.

1) Where is "1 AS" at the beginning of the SQL query coming from and how do I get it to leave?

2) Where did status, e.entity_id, e.type_id, e.attribute_set_id, e.item_nr, and e.item_nr come from? I thought I tried to filter just e.item_nr and 'b.product_id'? 'b.product_id' isn't even there.

3) How does one specify a RIGHT/LEFT OUTER/INNER JOIN and not just a RIGHT/LEFT JOIN?

Any help with these would be greatly appreciated.

Upvotes: 1

Views: 2289

Answers (1)

Alana Storm
Alana Storm

Reputation: 166086

Your question is a complicated one and would take more than a Stack Overflow post to answer. Instead, here's some pointers and background information to get you headed in the right direction.

Magento uses classes from the Zend Framework to perform its database queries. When you use the getSelect method, you're returning a Varien_Db_Select object which inherits from Zend_Db_Select.

The good news is, that means anything that works with Zend Framework also works with Magento's selects. Searching around for RIGHT/LEFT OUTER/INNER JOIN information Zend Framework will yield more results that searching for the same with Magento.

Complicating things though are Magento's EAV objects. Products are an EAV model, which means some base field information is stored in the catalog_product_entity table, and additional attribute information is stored in the catalog_product_entity_* tables. Categories are also an EAV model, with the added twist that categories implement a recursive nested hierarchy in a SQL table.

Further complications come from Magento's "index" tables. Magento started life with a relatively normalized database — which meant it was slow as molasses. Over the years many models (most heavily products), implemented non-normalized index table to allow for fewer selects/joins during normal store operation. (real database people will cringe at my use of the word normalized here — usual warnings about over simplification apply)

All this means when you call addAttributeToFilter, or other attribute based filtering methods on one of these collections, there's a large amount of custom, per model, SQL code being run. Even without calling these methods there's a lot of custom logic for reading to indexes tables, refreshing indexes, or other feature based code that worked its way into the data model layer.

The 1 AS status, as well as status, e.entity_id, e.type_id, e.attribute_set_id, e.item_nr, and e.item_nr fields are all part of the extra fields needed to make EAV and product features work. If you're interested in where these get added, start with the product collection class

app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php

and then work your way down the class hierarchy. 

app/code/core/Mage/Catalog/Model/Resource/Collection/Abstract.php
app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php    
lib/Varien/Data/Collection/Db.php

Welcome to Magento, and Good luck!

Upvotes: 1

Related Questions