3

I have been trying to create a simple module to enable order comments at checkout. I know there are a number of free extensions that do this but I need to add additional functionality so decided to write it from scratch.

At the moment when I save the comment using an Observer, I get a Javascript alert and looking in the exception.log file I can see the following error...

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`musicstore`.`checkout_comments`, CONSTRAINT `FK_CHECKOUT_COMMENTS_ORDER_ID_SALES_FLAT_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_flat_order` (`entity_id`) ON DELETE CASCADE O)'

Here is my install-0.0.1 script

<?php

/* @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;

$installer->startSetup();

$table = $installer->getConnection()
    ->newTable($installer->getTable('checkoutcomments/comments_table'))
    ->addColumn('comment_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity' => true,
        'unsigned' => true,
        'nullable' => false,
        'primary'  => true,
    ),  'Comment ID')
    ->addColumn('order_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned' => true,
        'nullable' => false,
    ),  'Real Order ID')
    ->addColumn('comment', Varien_Db_Ddl_Table::TYPE_TEXT, '64k', array(
    ), 'Comment')
    ->addForeignKey(
        $installer->getFkName(
        'checkoutcomments/comments_table',
        'order_id',
        'sales/order',
        'entity_id'
    ),
        'order_id', $installer->getTable('sales/order'), 'entity_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE)
        ->setComment('Checkout Comments');
$installer->getConnection()->createTable($table);
$installer->endSetup();

checkout_comments table:

mysql> describe checkout_comments;                                  
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| comment_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| order_id   | int(10) unsigned | NO   | MUL | NULL    |                |
| comment    | text             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.07 sec)

config.xml

<events>
    <controller_action_predispatch_checkout_onepage_saveOrder>
        <observers>
            <save_checkout_comments>
                <class>checkoutcomments/observer</class>
                <method>prepareCheckoutComment</method>
            </save_checkout_comments>
        </observers>
    </controller_action_predispatch_checkout_onepage_saveOrder>

    <sales_order_place_after>
        <observers>
            <save_checkout_comments>
                <class>checkoutcomments/observer</class>
                <method>saveCheckoutComment</method>
            </save_checkout_comments>
        </observers>
    </sales_order_place_after>
</events>

Observer.php

public function prepareCheckoutComment($observer){

    $message = $observer->getControllerAction()
                        ->getRequest()
                        ->getPost('checkoutcomments');
    Mage::getSingleton('core/session')->setCheckoutComment($message);
}

public function saveCheckoutComment($observer){

    $order = $observer->getOrder();
    $message = Mage::getSingleton('core/session')->getCheckoutComment(true);

    if (!$order || empty ($message)) {
        return;
    }

    $commentModel = Mage::getModel('checkoutcomments/comment');
    $commentModel->setOdrderId($order->getId())
                 ->setComment($message)
                 ->save();
}

I've tried with different events

  <sales_model_service_quote_submit_after>
        <observers>
            <save_checkout_comments>
                <class>checkoutcomments/observer</class>
                <method>saveCheckoutComment</method>
            </save_checkout_comments>
        </observers>
    </sales_model_service_quote_submit_after>

    <sales_model_service_quote_submit_success>
        <observers>
            <save_checkout_comments>
                <class>checkoutcomments/observer</class>
                <method>saveCheckoutComment</method>
            </save_checkout_comments>
        </observers>
    </sales_model_service_quote_submit_success>

    <checkout_submit_all_after>
        <observers>
            <save_checkout_comments>
                <class>checkoutcomments/observer</class>
                <method>saveCheckoutComment</method>
            </save_checkout_comments>
        </observers>
    </checkout_submit_all_after>

Debug array

2013-11-20T19:28:08+00:00 DEBUG (7): Array
(
    [increment_id] => 100000043
    [store_id] => 1
    [quote_id] => 55
    [quote (Mage_Sales_Model_Quote)] => Array
        (
            [entity_id] => 55
            [store_id] => 1
            [created_at] => 2013-11-20 19:21:20
            [updated_at] => 2013-11-20 19:28:08
            [is_active] => 1
            [is_virtual] => 0
            [is_multi_shipping] => 0
            [items_count] => 1
            [items_qty] => 1
            [orig_order_id] => 0
            [store_to_base_rate] => 1
            [store_to_quote_rate] => 1
            [base_to_global_rate] => 1
            [base_to_quote_rate] => 1
            [global_currency_code] => GBP
            [base_currency_code] => GBP
            [store_currency_code] => GBP
            [quote_currency_code] => GBP
            [grand_total] => 20
            [base_grand_total] => 20
            [customer_id] => 2
            [customer_tax_class_id] => 3
            [customer_group_id] => 1
            [customer_email] => *************
            [customer_firstname] => *************
            [customer_lastname] => ************
            [customer_note_notify] => 1
            [customer_is_guest] => 0
            [remote_ip] => ::1
            [applied_rule_ids] => 
            [reserved_order_id] => 100000043
            [subtotal] => 15
            [base_subtotal] => 15
            [subtotal_with_discount] => 15
            [base_subtotal_with_discount] => 15
            [is_changed] => 1
            [trigger_recollect] => 0
            [is_persistent] => 0
            [virtual_items_qty] => 0
            [taxes_for_items] => Array
                (
                )

            [can_apply_msrp] => 
            [totals_collected_flag] => 1
            [inventory_processed] => 1
        )

    [customer (Mage_Customer_Model_Customer)] => Array
        (
            [website_id] => 1
            [entity_id] => 2
            [entity_type_id] => 1
            [attribute_set_id] => 0
            [email] => ***************
            [group_id] => 1
            [store_id] => 1
            [created_at] => 2013-11-17 10:40:11
            [updated_at] => 2013-11-20 19:28:08
            [is_active] => 1
            [disable_auto_group_change] => 0
            [firstname] => ******
            [lastname] => *******
            [password_hash] => 456731650daade64aa54e0d4ea97c69a:OrJLah1fNeeX0lNEUmyoU9n1DAQ8sGyC
            [created_in] => English
            [mcd_meeting12_avatar] => logo.png
            [default_billing] => 1
            [default_shipping] => 1
            [tax_class_id] => 3
            [parent_id] => 0
            [_profile_picture_dir_cache] => /Applications/MAMP/htdocs/Developer/media/mcd/images/customer/2
        )

    [remote_ip] => ::1
    [customer_id] => 2
    [customer_email] => *********
    [customer_firstname] => **********
    [customer_lastname] => *************
    [customer_group_id] => 1
    [customer_tax_class_id] => 3
    [customer_note_notify] => 1
    [customer_is_guest] => 0
    [quote_base_grand_total] => 20
    [global_currency_code] => GBP
    [base_currency_code] => GBP
    [store_currency_code] => GBP
    [order_currency_code] => GBP
    [store_to_base_rate] => 1.0000
    [store_to_order_rate] => 1.0000
    [base_to_global_rate] => 1.0000
    [base_to_order_rate] => 1.0000
    [is_virtual] => 0
    [applied_rule_ids] => 
    [total_qty_ordered] => 1
    [weight] => 0.5
    [shipping_method] => flatrate_flatrate
    [shipping_description] => Flat Rate - Fixed
    [subtotal] => 15
    [tax_amount] => 0
    [discount_amount] => 0
    [shipping_amount] => 5
    [shipping_incl_tax] => 5
    [shipping_tax_amount] => 0
    [grand_total] => 20
    [base_subtotal] => 15
    [base_tax_amount] => 0
    [base_discount_amount] => 0
    [base_shipping_amount] => 5
    [base_shipping_incl_tax] => 5
    [base_shipping_tax_amount] => 0
    [base_grand_total] => 20
    [hidden_tax_amount] => 0
    [base_hidden_tax_amount] => 0
    [shipping_hidden_tax_amount] => 0
    [base_shipping_hidden_tax_amount] => 0
    [base_shipping_hidden_tax_amnt] => 0
    [discount_description] => 
    [shipping_discount_amount] => 0
    [base_shipping_discount_amount] => 0
    [subtotal_incl_tax] => 15
    [base_subtotal_incl_tax] => 15
    [payment_surcharge_amount] => 0
    [base_payment_surcharge_amount] => 0
    [applied_taxes] => Array
        (
        )

    [converting_from_quote] => 1
    [store_name] => Main Website
Main Store
English
    [total_item_count] => 1
    [protect_code] => b599b9
    [created_at] => 2013-11-20 19:28:08
    [updated_at] => 2013-11-20 19:28:08
    [entity_id] => 80
    [billing_address_id] => 71
    [shipping_address_id] => 72
    [applied_tax_is_saved] => 1
    [state] => pending
    [status] => 
)

I have no idea how to solve this or why it is even happening and would like to ask MO for some guidance please?

  • seems your foreign key is already added, so you can't add it again. check in your mysql table checkout_comments. – Kalpesh Nov 19 '13 at 11:22
  • @KalpeshMehta I've dropped my table removed install script from core_resource and refreshed but I still get the error in my log looking at the table I don't notice anything fishy... See Edit^ – user1704524 Nov 19 '13 at 12:17
  • Most probably you are trying to save a comment with an order_id that does not exist or is null. How/When do you save the comments? – Marius Nov 19 '13 at 12:32
  • 1
    This is what I'm trying to say... maybe you are trying to save the comment before the order is saved. And everything is in a transaction so it gets rollbacked. Log the data for the comment before saving it and see how it looks. Or post some code, or the event name you are using. – Marius Nov 19 '13 at 15:57
  • 1
    this currently only includes the increment_id which is not the same as the order id. It needs to have an entity_id for the order (your comment only shows it for the quote) which is what is returned by $order->getId() – Kristof at Fooman Nov 21 '13 at 1:06
5

First remove the typo:

$commentModel->setOrderId($order->getId())
                 ->setComment($message)
                 ->save();

Additionally I would suggest to use

$commentModel = Mage::getModel('checkoutcomments/comment')->load($order->getId(), 'order_id');

which would make sure that you are updating the same comment in case your code gets executed twice.

  • I'd be damned, ONE typo... – user1704524 Nov 22 '13 at 9:58
2

The problem (like suspected in the comments) is the event you are using. sales_order_place_after. That event is dispatched after the payment is placed. The order is not saved yet at that point.
You can try with sales_model_service_quote_submit_success or sales_model_service_quote_submit_after. I recommend the first one.

  • I still get the constraint in my log, I've attempted to use different events also but the error still occurs... been scratching my head since yesterday! – user1704524 Nov 19 '13 at 16:35
  • app/code/core/Mage/Sales/Model/Service.php on line 191, debug it with an IDE and verify the $order variable. – Niloct Nov 21 '13 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.