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

Low level sql query
 
Sponsorewiczowiczew
Jr. Member
 
Avatar
Total Posts:  5
Joined:  2007-09-06
 

How can I make low level query - base on magento db system, where I select products where for example some attribute have value more than 4? Can You give me example?

 
Magento Community Magento Community
Magento Community
Magento Community
 
jsperri
Sr. Member
 
Total Posts:  126
Joined:  2007-08-31
Fistufle
 

1. First find your attribute_id, check eav_attribute table
This column will give you the attribute_id to look for in your next select.
backend_type column will tell you which is the value type for this attribute, so it will give you the eav_entity column to look for.
It could be eav_entity_int in your case if your attribute contains only integer values.

2. Then select in column catalog_product_entity_XXX for the column “value” corresponding to the values you want to filter, on the attribute_id (XXX being the right data type corresponding to your attribute)
In this result set, you’ll find the id for your products in the entity_id column.

Please note, that these are findings I have done by trial and error, they will need to be confirmed by Moshe and other Magento tech guys smile
Also, this is valid for current Magento release, and there is no warranty this will stay valid for the future.

 
Magento Community Magento Community
Magento Community
Magento Community
 
__pete®
Jr. Member
 
Total Posts:  11
Joined:  2007-11-22
 

in php you can create some model and then get your infos e.g. this one retrieves all assigned categories for a product:

class Mage_Catalog_Model_Category_Product extends Mage_Core_Model_Abstract {
    
/**
     * 
     *
     * @param integer $productId
     * @return array
     */
    
public function getCategoryIdsForProduct($productId{
        $storeId 
Mage::app()->getStore();
        
        
$resource Mage::getSingleton('core/resource');
        
$read $resource->getConnection('catalog_read');
        
$categoryProductTable $resource->getTableName('catalog/category_product');
        
        
$select $read->select()->from($categoryProductTable)->where('product_id = '.$productId);
        
$categoryRows $read->fetchAll($select);
        
        
$categories = array();
        
        foreach (
$categoryRows as $row{
            $categories[] 
$row['category_id'];
        
}
        
        
return $categories;
    
}
}

e.g. this one helped me to get oen category if no category in url and mage throws exceptions because no category found wink

$categoryProductModel Mage::getModel('catalog/category_product');
            
$cats $categoryProductModel->getCategoryIdsForProduct($productId);
            if(
is_array($cats)) {
                $category 
Mage::getModel('catalog/category')->load(array_pop($cats));
                
Mage::register('current_category'$category);
            
}

_pete®

 
Magento Community Magento Community
Magento Community
Magento Community
 
spider
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2007-10-12
 

I have question to 2 lines of code inserted above:

First line its:

$read $resource->getConnection('catalog_read');
What exactly mean catalog_read ?

Second one is:

$categoryProductTable $resource->getTableName('catalog/category_product');
catalog/category_product <- This is the path to something or what ?
 
Magento Community Magento Community
Magento Community
Magento Community
 
Moshe
Magento Team
 
Avatar
Total Posts:  1770
Joined:  2007-08-07
Los Angeles
 

@spider: ‘calalog_read’ is a name of connection for catalog’s database for reading. the declaration can be found in app/code/core/Mage/Catalog/etc/config.xml:

<config>
  <global>
...
    <
resources>
...
      <
catalog_read>
        <
connection>
          <use>
core_read</use>
Which means it should be using the same connection as core_read, which is defined in app/etc/config.xml (default information) and app/etc/local.xml (custom local db information)
$read will return an instance of Zend_Db_Adapter_Abstract.

‘catalog/category_product’ is built from 2 components: model name (catalog) and entity name (category_product). Both are defined in same app/code/core/Mage/Catalog/etc/config.xml:

<config>
  <global>
...
    <
models>
      <
catalog>
        <
resourceModel>catalog_entity</resourceModel>
      </
catalog>
      <
catalog_entity>
        <
entities>
...
          <
category_product>
            <
table>catalog_category_product</table>
          </
category_product>
As you can see ‘catalog_entity’ model is declared as a resource model for ‘catalog’ model, and that’s where entity configuration is taken from.
Catalog module uses EAV resource model, and that’s why it’s called ‘catalog_entity’. The ‘core’ model for example has ‘core_mysql4’ as resource model.

Consult this diagram for a concept: http://www.magentocommerce.com/wiki/_media/general/doc/page-request-flow.png?cache=cache

 
Magento Community Magento Community
Magento Community
Magento Community
 
spider
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2007-10-12
 

Ok It works fine. I needed to retrive all products so I replace this line:

$categoryProductTable $resource->getTableName('catalog/category_product');
to this line:

$categoryProductTable = $resource->getTableName(’catalog/product’);

and I get all products and if I vardump my results of query I see many arrays represents all products in database (code below)

[0]=>
  array(
10{
    [
"entity_id"]=>
    
string(1"6"
    
["entity_type_id"]=>
    
string(2"10"
    
["attribute_set_id"]=>
    
string(2"38"
    
["parent_id"]=>
    
string(1"0"
    
["store_id"]=>
    
string(1"0"
    
["type_id"]=>
    
string(1"1"
    
["sku"]=>
    
string(4"1234"
    
["created_at"]=>
    
string(19"2007-08-22 17:42:08"
    
["updated_at"]=>
    
string(19"2007-08-30 16:51:33"
    
["is_active"]=>
    
string(1"1"
  
}
...
and my question is: How I can getting all atrributes for each products with values of this attributes ?
I know I must put my resut of query to the foreach() like this:

$categoryRows $read->fetchAll($select);
        
$categories = array();
        
foreach (
$categoryRows as $row{
    
// probably here I must retrieve attributes and put product and attributes into array and this array I will shown on view
}

I will be fully satisfied when I can join products with attributes in query to database.

any help ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
spider
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2007-10-12
 

any help? this is very important for me right now.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Moshe
Magento Team
 
Avatar
Total Posts:  1770
Joined:  2007-08-07
Los Angeles
 

@spider: could you explain your final goal? is this to learn how the system works or you wish to retrieve all products data from db? If it is latter, here’s how you do it:

$collection Mage::getResourceModel('catalog/product_collection')
    ->
addAttributeToSelect('*')
    ->
setStore('base')
    ->
load();

$data $collection->exportToArray();

Now in $data you have 2-dimentional grid of all products with all attributes with data for store identified by code ‘base’ .

 
Magento Community Magento Community
Magento Community
Magento Community
 
spider
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2007-10-12
 

Fatal errorCall to undefined method Mage_Catalog_Model_Entity_Product_Collection::setStore() in /usr/home/ftpuser/magento/www/app/code/core/Mage/Page/Block/Html/Mainpage.php on line 14

Witch class i need to extend to my class ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Moshe
Magento Team
 
Avatar
Total Posts:  1770
Joined:  2007-08-07
Los Angeles
 

What version of Magento you have?

in 0.6.13700 there’s function setStore on line 45 in
app/code/core/Mage/Catalog/Model/Entity/Product/Collection.php

If you do not have it, add this:

public function setStore($store)
{
    $this
->getEntity()->setStore($store);
    return 
$this;
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
spider
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2007-10-12
 

if I throw this line from my code

->setStore('base')

works very well. In what this line I need ?
This is the needed flag witch tells the script what type of store is chhosen ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
spider
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2007-10-12
 

This works fine but I need in first way to example: getting all products with attribute “promotion” = “yes” Do You know what I mean ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Moshe
Magento Team
 
Avatar
Total Posts:  1770
Joined:  2007-08-07
Los Angeles
 

$collection Mage::getResourceModel('catalog/product_collection')
    ->
addAttributeToSelect('*')
    ->
addAttributeToFilter('promotion''yes')
    ->
setStore('base')
    ->
load();
 
Magento Community Magento Community
Magento Community
Magento Community
 
spider
Jr. Member
 
Avatar
Total Posts:  19
Joined:  2007-10-12
 

ok very fine but now I have problem with values of attributes, I created 3 attributes and add them into ‘Default’ attribute sets. I add product with ‘Default’ set of attributes and when I choose Yes or No(of my attribute) in dropdownlist in configurating my product in database this value save like a number 1 always. What the problem is ?

Another question is:
In this code I need to put something like ‘OR’ I show this in example below:

$collection Mage::getResourceModel('catalog/product_collection')
    ->
addAttributeToSelect('*')
    ->
addAttributeToFilter('promotion''yes')
    
// here OR ->addAttributeToFilter('second_attribute', 'yes')
    // here OR ->addAttributeToFilter('third_attribute', 'yes')
    
->setStore('base')
    ->
load();
 
Magento Community Magento Community
Magento Community
Magento Community
 
Moshe
Magento Team
 
Avatar
Total Posts:  1770
Joined:  2007-08-07
Los Angeles
 

When you create a dropdown attribute, it stores the value as integer increment. See this thread: http://www.magentocommerce.com/boards/viewreply/7231/

As of 2nd question, right now it works like this:

$collection Mage::getResourceModel('catalog/product_collection')
    ->
addAttributeToSelect('*')
    ->
addAttributeToFilter(array(
        array(
'attribute'=>'promotion''in'=>array(1))
        array(
'attribute'=>'second_attribute',  'in'=>array('yes'))
        array(
'attribute'=>'third_attribute',  'in'=>array('yes'))
    ))
    ->
setStore('base')
    ->
load();
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top