Database Archives | Bravehartk2's Blog

Query Magento database object oriented

20 May 201627 May 2016 SheldonNo Comments

Naturally you can query Magento database directly as described in my earlier post about querying Magento database. But a much smarter way is it, to use exiting Magento objects and constructs like that:

<?php
/**
 * Sets position (sort order) values for the given attribute for a given option value. Therefore the given store specific value is
 * used to lookup the necessary option id to identify the correct option
 * @param sting $sAttributeCode Magento attribute code for that option value position should be set
 * @param sting $sValue  option value in the given store
 * @param int $iPosition position (sort order) to set for given option value
 * @param int $iReferenceStoreId store to search for a option label
 * @throws Zend_Db_Adapter_Exception
 */
public function setAttributeOptionSorting($sAttributeCode, $sValue, $iPosition, $iReferenceStoreId)
{
    /* @var Varien_Db_Adapter_Pdo_Mysql $oWriteConnection */
    $oWriteConnection = Mage::getSingleton('core/resource')->getConnection('core_write');

    $sOptionTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option');
    $sOptionLableTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option_value');

    $iAttributeId = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $sAttributeCode)->getId();


    $oSelect = new Varien_Db_Select($oWriteConnection);
    $oSelect->from(
        ['eao' => $sOptionTable],
        ['option_id']
    )->join(
        ['eaov' => $sOptionLableTable],
        "eao.option_id = eaov.option_id AND eaov.store_id = " . $iReferenceStoreId,
        ''
    )->where(
        'eao.attribute_id = ?',
        $iAttributeId
    )->where(
        'eaov.value = ?',
        $sValue
    );

    $aResult = $oWriteConnection->fetchCol($oSelect);

    foreach ($aResult as $iOptionId) {
        $oWriteConnection->update(
            $sOptionTable,
            ['sort_order' => $iPosition],
            $oWriteConnection->quoteInto('option_id = ?', $iOptionId)
        );
    }
}

<?php

/**

* Sets position (sort order) values for the given attribute for a given option value. Therefore the given store specific value is

* used to lookup the necessary option id to identify the correct option

* @param sting $sAttributeCode Magento attribute code for that option value position should be set

* @param sting $sValue option value in the given store

* @param int $iPosition position (sort order) to set for given option value

* @param int $iReferenceStoreId store to search for a option label

* @throws Zend_Db_Adapter_Exception

public function setAttributeOptionSorting($sAttributeCode, $sValue, $iPosition, $iReferenceStoreId)

{

/* @var Varien_Db_Adapter_Pdo_Mysql $oWriteConnection */

$oWriteConnection = Mage::getSingleton('core/resource')->getConnection('core_write');

$sOptionTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option');

$sOptionLableTable = Mage::getSingleton('core/resource')->getTableName('eav/attribute_option_value');

$iAttributeId = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $sAttributeCode)->getId();

$oSelect = new Varien_Db_Select($oWriteConnection);

$oSelect->from(

['eao' => $sOptionTable],

['option_id']

)->join(

['eaov' => $sOptionLableTable],

"eao.option_id = eaov.option_id AND eaov.store_id = " . $iReferenceStoreId,

)->where(

'eao.attribute_id = ?',

$iAttributeId

)->where(

'eaov.value = ?',

$sValue

);

$aResult = $oWriteConnection->fetchCol($oSelect);

foreach ($aResult as $iOptionId) {

$oWriteConnection->update(

$sOptionTable,

['sort_order' => $iPosition],

$oWriteConnection->quoteInto('option_id = ?', $iOptionId)

);

}

This is a simplified version of a function (better to say shell script class) that sets attribute value positions for a certain […]

Create database tables in setup scripts

11 May 201611 May 2016 SheldonNo Comments

The following snippet shows, how to create database tables in a setup script. It uses no direct queries against the database but the an object oriented way Magento does such things. (This snipped assumes that your resource setup model is derived from Mage_Core_Model_Resource_Setup)

<?php
/**
 *
 * Magento
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * It is  available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 *
 *
 * @category   AskSheldon
 * @package    AskSheldon_FeaturedProducts
 * @copyright  Copyright (c) 2016 Marcel Lange (https://www.ask-sheldon.com)
 * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 * @author     Marcel Lange <info@ask-sheldon.com>
 *
 * Date:       10.05.16
 * Time:       14:53
 *
 * Creates table structure for category -> featured product relation
 *
 * Mage_Core_Model_Resource_Setup
 *
 *  *
 * Created by IntelliJ IDEA.
 *
 */
/* @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;

$installer->startSetup();

/**
 * Create table 'asksheldon_featuredproducts/category_featuredproducts'
 */
$table = $installer->getConnection()
    ->newTable($installer->getTable('asksheldon_featuredproducts/category_product')) # returns instance of Varien_Db_Ddl_Table
    ->addColumn('category_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
        'default' => '0',
    ), 'Category ID')
    ->addColumn('product_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
        'default' => '0',
    ), 'Product ID')
    ->addColumn('position', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'nullable' => false,
        'default' => '0',
    ), 'Position')
    ->addIndex(
        $installer->getIdxName(
            'asksheldon_featuredproducts/category_product',
            array('product_id')
        ),
        array('product_id'))
    ->addForeignKey(
        $installer->getFkName(
            'asksheldon_featuredproducts/category_product',
            'category_id', 'catalog/category',
            'entity_id'
        ),
        'category_id',
        $installer->getTable('catalog/category'),
        'entity_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE,
        Varien_Db_Ddl_Table::ACTION_CASCADE
    )
    ->addForeignKey(
        $installer->getFkName(
            'asksheldon_featuredproducts/category_product',
            'product_id',
            'catalog/product',
            'entity_id'
        ),
        'product_id',
        $installer->getTable('catalog/product'),
        'entity_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE,
        Varien_Db_Ddl_Table::ACTION_CASCADE
    )
    ->setComment('AskSheldon Features Product To Category Linkage Table');
$installer->getConnection()->createTable($table);

$installer->endSetup();

<?php

/**

* Magento

* NOTICE OF LICENSE

* This source file is subject to the Open Software License (OSL 3.0)

* It is available through the world-wide-web at this URL:

* http://opensource.org/licenses/osl-3.0.php

* @category AskSheldon

* @package AskSheldon_FeaturedProducts

* @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0)

* @author Marcel Lange <info@ask-sheldon.com>

* Date: 10.05.16

* Time: 14:53

* Creates table structure for category -> featured product relation

* Mage_Core_Model_Resource_Setup

* *

* Created by IntelliJ IDEA.

/* @var $installer Mage_Core_Model_Resource_Setup */

$installer = $this;

$installer->startSetup();

/**

* Create table 'asksheldon_featuredproducts/category_featuredproducts'

$table = $installer->getConnection()

->newTable($installer->getTable('asksheldon_featuredproducts/category_product')) # returns instance of Varien_Db_Ddl_Table

->addColumn('category_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(

'unsigned' => true,

'nullable' => false,

'primary' => true,

'default' => '0',

), 'Category ID')

->addColumn('product_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(

'unsigned' => true,

'nullable' => false,

'primary' => true,

'default' => '0',

), 'Product ID')

->addColumn('position', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(

'nullable' => false,

'default' => '0',

), 'Position')

->addIndex(

$installer->getIdxName(

'asksheldon_featuredproducts/category_product',

array('product_id')

array('product_id'))

->addForeignKey(

$installer->getFkName(

'asksheldon_featuredproducts/category_product',

'category_id', 'catalog/category',

'entity_id'

'category_id',

$installer->getTable('catalog/category'),

'entity_id',

Varien_Db_Ddl_Table::ACTION_CASCADE,

Varien_Db_Ddl_Table::ACTION_CASCADE

)

->addForeignKey(

$installer->getFkName(

'asksheldon_featuredproducts/category_product',

'product_id',

'catalog/product',

'entity_id'

'product_id',

$installer->getTable('catalog/product'),

'entity_id',

Varien_Db_Ddl_Table::ACTION_CASCADE,

Varien_Db_Ddl_Table::ACTION_CASCADE

)

->setComment('AskSheldon Features Product To Category Linkage Table');

$installer->getConnection()->createTable($table);

$installer->endSetup();

As you can see, it is also possible to add indexes and […]

Create UTF-8 Database

11 July 201512 January 2017 SheldonNo Comments

To crate a UTF-8 database:

CREATE DATABASE sns_typo3   DEFAULT CHARACTER SET utf8   DEFAULT COLLATE utf8_general_ci;

1	CREATE DATABASE sns_typo3 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Debug database queries with parameters

17 April 201520 May 2016 Sheldon1 Comment

Sometimes you have to query the database directly in your module. In these situations you can use the placeholder feature of the Zend_Db_Statement component. :placeholder will be replaced by the respective value when calling fetch on a Zend_Db_Adapter_Mysqli instance with an array of replacements for example. If you need to debug the query with all parameters in it, […]

M	T	W	T	F	S	S
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

Bravehartk2's Blog

- next level braining -

Tag: Database

Query Magento database object oriented

Create database tables in setup scripts

Create UTF-8 Database

Debug database queries with parameters

Share this:

Share this:

Share this:

Share this: