How to access custom table/attributes using the ORM?

Question

I've been struggling with this for a while - I'm new to Magento and haven't programmed in PHP for a while, so please, bear with me.

Doing some changes on a Shipping module to magento I've come to need to save and query an specific extra attribute, which I've managed to add through a simple update script:

<?php

$installer->startSetup();
$table = new Varien_Db_Ddl_Table(); $table->setName('shipping_token');

$table
->addColumn('token_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, 
    array(
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
        'identity'  => true,
    ), 'Entity Id')
->addColumn('rate_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null,
    array(
        'unsigned'  => true,
        'nullable'  => false,
    ), 'Quote Id')
->addColumn('token', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, 
    array(
        'unique'    => true,
    ), 'Quote Token')
->addIndex(
    'ID_QUOTE_TOKEN',
    'token_id')
->addForeignKey(
    'FK_TOKEN',
    'rate_id',
    'sales_flat_quote_shipping_rate', 
    'rate_id', 
    Varien_Db_Ddl_Table::ACTION_CASCADE,
    Varien_Db_Ddl_Table::ACTION_CASCADE);

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

After that, with some ups and downs, I've been following this tutorial trying to access the models with:

 $shipping = Mage::getModel('shipping/token');
 $shipping->setToken("whatevercomes");

or

 $shipping = Mage::getModel('shipping/token');
 $shipping->setData('token', 'whatevercomes');

but since I'm doing this when I receive the shipping rates, hitting

 $shipping->save();

not only doesn't work but doesn't throw any exceptions either. So, am I doing anything blatantly wrong, or is there any way to dig deeper on the debugging of the matter? I'm lost here.

Answer 1

Let's take it a step at a time.

First, ensure that your table has been created by checking the database.

Once that's been verified, start logging your queries to see if there are any problems. You can do this by opening up the file in lib/Varien/Db/Adapter/Pdo/Mysql.php. In that class, you will find a number of protected variables that allow you to log queries. In this example, we will set $_logAllQueries to true. You can now go into var/debug/pdo_mysql.log and check against all queries that are executed.

Also make sure you have logging enabled on your site. In the Magento admin, go to System->Configuration->Advanced->Developer->Log Settings. Set Enabled to "Yes".

As you work with the ORM, you can always debug your output. Try:

Mage::log($shipping->debug()); // for logging your data
Zend_Debug::dump($shipping->debug()); // for printing it on the screen

Once that's done, make sure you are setting all values required by your table. Does the foreign key you've set exist yet? How are you assigning it to the table you've created when you save your values?

Should your method look more like this?

$shipping->setToken('whatevercomes')->setRateId('shippingRateIdHere')->save();

Once you have some logging or debug output, that can help you better understand what's going on with your error.

I hope this helps!

Answer 2

1. check if the table has got created in Db, clear magento cache
2. insert record in that table from any database manager.

3. try loading the collecion, fot that you can use below code

   $collection = Mage::getModel('shipping/token')->getCollection;
   echo "<pre>"; print_r($collection->getData());
   

4. if you see your inserted record in that loaded collection that means everything is fine.

5. to insert data in that table by using model, follow below steps

   $token = Mage::getModel('shipping/token');
   //lets assume "token_id" is one of the column of that table than to insert data to that column you can use any of the below code.
   $token->setData("token_id", "abc");
   //OR
   $token->setTokenId("abc");
   //And than save the model
   $token->save();
   

Answer 3

For querying a custom data table you can define following methods in your model file which are locate under your module_namespace/module_name/Model/ModelName.php

For insert

   public function insert($fieldArray,$tableName)
   {
    $query_append = null;
    $query_append .= "INSERT INTO `".Mage::getConfig()->getTablePrefix()."".$tableName."` ";
    foreach($fieldArray as $key => $val)
    {
            $query_append_field .= "`$key`".',';
            $query_append_values .= "'$val'".',';
    }
    $query_append .= '('.rtrim($query_append_field,",").') VALUES ('.rtrim($query_append_values,",").')';

    $this->executeSql($query_append);
}

here we have to pass the fieldsArray as a parameter which will going to inserted

$fieldArray[field1] = value1 like this.

For Update

public function update($updateArray, $tableName, $keyName, $editId)
{
    $query_append = null;
    $query_append .= "UPDATE `".Mage::getConfig()->getTablePrefix()."".$tableName."` SET ";
    $sizeof = sizeof($updateArray); 
    $i = 1;
    foreach($updateArray as $key => $val)
    {
        $separator = ($i < $sizeof) ? ',' : '';
            $query_append_field .= "`$key`".'='."'$val'".$separator;
        $i++;
    }
    $query_append .= $query_append_field." WHERE ".$keyName." = '".$editId."'";

    $this->executeSql($query_append);
}

Here we have to pass the updateArray as a parameter which will going to updated

$updateArray['fieldname1'] = value1;

$updateArray['fieldname2'] = value2;

all fields which are contains in array will be updated.

For Delete

public function deleteRecord($tableName, $keyName, $delete_id)
{
    $resource = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');
    $query = "DELETE FROM ".Mage::getConfig()->getTablePrefix()."".$tableName." WHERE ".$keyName." = ".$delete_id." ";

    $this->executeSql($query);
}

For execute all query

 public function executeSql($query)

{
    // fetch write database connection that is used in Mage_Core module
    $write = Mage::getSingleton('core/resource')->getConnection('core_write');

    // now $write is an instance of Zend_Db_Adapter_Abstract
    $write->query("$query");
}

For Particular Records

 public function getRecordsById($tableName,$keyName, $value)
{

    $resource = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');
    $query = "SELECT * FROM ".Mage::getConfig()->getTablePrefix()."".$tableName." WHERE ".$keyName." = ".$value." ";

    $results = $readConnection->fetchAll($query);

    return $results;
}

These all methods are accessible from everywhere inside magento directory using following syntax

Example:

$Model = Mage::getModel('ModelName/ModelName');

$Model->deleteRecord('tablename','fieldname','fieldvalue');

Hope these will help you.