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

Accessing Database
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Why is it that half of my database selections work like this

$CatalogProductEntity $resource->getTableName('catalog/product_entity');
        
        
$select $read->select()->from(array('cpe'=>$CatalogProductEntity))
            ->
where('cpe.entity_id=?'$productId);

and the other half I have to do like this?

$rows $readonce->fetchAll('SELECT * FROM catalog_product_entity_int where attribute_id ='.$attribId);
 
Magento Community Magento Community
Magento Community
Magento Community
 
ckosny
Guru
 
Total Posts:  349
Joined:  2009-02-28
Luxembourg
 

Afaik this is because in the first example you try to access an entity (that might consist of multiple (eav) attributes and thus is a resource for Magento), whereas the second example tries to access a single attribute which in an ideal world should not be accessed on its own, but only as part of the entity it belongs to.

But apart from the getTableName which most likely will not work for the table in the second example (might be worth to try though...), I don’t see anything that stops you from using the Zend DB adapter style for the second query as well. Or have you encountered any problems with that?

Claudia

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

With certain tables I get a config error with the first example and I have to go to the second example to get it to work.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ckosny
Guru
 
Total Posts:  349
Joined:  2009-02-28
Luxembourg
 

Can you give examples for which tables the first example fails? And where do you get the config error - at the line with the getTableName or at the creation of the select?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

I just got the error when I was working on a module yesterday. I was on the tablename

$CatalogProductSuperAttribute $resource->getTableName('catalog/product_super_attribute');

This table didn’t, so I setup my method in a different way

$rows $readonce->fetchRow('SELECT value FROM catalog_product_entity_varchar where entity_id='.$productId.' and attribute_id ='.$attribId.'');

The error come on the pageload and I don’t remember exactly what it says, I am sure I will get it again.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ckosny
Guru
 
Total Posts:  349
Joined:  2009-02-28
Luxembourg
 

Well how does your config.xml look like?
Afaik if you want to use getTableName you have to have the mapping from the resource/entity to the table name in your config.xml, meaning something like this:

<global>
  <
models>
    <
catalog_resource_eav_mysql4>
      <
entities>
        <
product_super_attribute>
          <
table>catalog_product_super_attribute</table>
        </
product_super_attribute>
      </
entities>
    </
catalog_resource_eav_mysql4>
  </
models>
</global>

But no matter where you get the table name from you still should be able to rewrite your select statement to a Zend select (the code is not tested though):
Your statement:

SELECT value FROM catalog_product_entity_varchar where entity_id='.$productId.' and attribute_id ='.$attribId.'

I think you could write that like this:

$select $read->select()->from(array('cpe'=> 'catalog_product_entity_varchar'))
                            ->
where('cpe.entity_id=?'$productId)
                            ->
where('cpe.attribute_id=?'$attribId);

Good luck

Claudia

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Hi Claudia,

Thank you for your feedback, I have been writing my statements the 2nd way when they work, but I resolve to the first way when I get an error

Here is my latest error after writing a method to get the lowest price

Can’t retrieve entity config: catalog/product_entity_decimal
Trace:
#0 /home/midwestsupplies/public_html/app/code/core/Mage/Core/Model/Resource.php(142): Mage::throwException(’Can’t retrieve ...’)
#1 /home/midwestsupplies/public_html/app/code/local/Wdc/Catalog/Model/Product.php(258): Mage_Core_Model_Resource->getTableName(’catalog/product...’)

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Here is my function that I get an error on.

public function _getPrice($productId$attributeId=99)
    
{
        $select 
null;
        
$resource Mage::getSingleton('core/resource');
        
$read $resource->getConnection('catalog_read');
        
        
$CatalogProductEntityDecimal $resource->getTableName('catalog/product_entity_decimal');
        
        
$select $read->select()->from(array('cped'=>$CatalogProductEntityDecimal))
            ->
where('cped.entity_id=?'$productId)
            ->
where('cped.attribute_id=?'$attributeId);
        
        
$opVal $read->fetchRow($select);        
        
        return 
$opVal['value'];
        
    
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
ckosny
Guru
 
Total Posts:  349
Joined:  2009-02-28
Luxembourg
 

You get this error because this table does not have a mapping from entity to table name in the config.xml of catalog.
If you add the mapping there manually your code works.

Initially I thought that the lack of the mapping is because Magento did not plan that someone might want to retrieve parts of entities directly from the database (meaning without retrieving the entity). Checking the config.xml of catalog this assumption does not seem to hold up - there is a table mapping for e.g. catalog/product_option_type_title which for me would be part of the entity product_option (or maybe product_option_type). Ah well - they certainly had some reason to do it like this.

Be that as it may - from the pure technical point of view it seems that you can use getTableName for all instances where the respective entity is mentioned in some config.xml.

Hth

Claudia

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Claudia,

Thank you very much for your help!
Is there a performance/ programmability advantage using one or the other?

Also, do add the mapping, I need to add it to my module?

 
Magento Community Magento Community
Magento Community
Magento Community
 
ckosny
Guru
 
Total Posts:  349
Joined:  2009-02-28
Luxembourg
 

Regarding performance - no idea. I would say it does not matter.

Regarding programmability - I think that using getTableName is more flexible if you use table prefixes because it adds them automatically. Thus your code would have a better chance of working on different setups of Magento. On the other hand - if you only work on your own installations and you know that you don’t use a prefix it does not really matter.

If you decide to add some table mappings I would suggest adding it to your module because otherwise they might get lost during the next update.

Good luck

Claudia

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